Check which Access table is open

[owen4512]

Hi all,

I currently use a Excel userform to show data from a access table. The data is displayed in a Listbox in the userform. I use the below VBA to allow the user to double click the entry and the data is then displayed in textBoxes/ComboBoxes.

If i run "Macro1" it will show the data in "Table1" from access and display this in "Listbox1". I then have "Macro2" that will show the data in "Table2" from access and display this also in "Listbox1" (I dont mean at the same time). The issue i'm having is the fields in "Table1" and different to "Table2" so the below double click feature doesnt work for "Table2".

What i'm looking for is, IF "Table1" is active then use "Code1" Elseif "Table2" is active then use "code2" - i hope i've explained this well enough and would greatly appreciate your help on this.

The "txt......" refers to the textboxes in the excel userform.

Code 1

Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim i As Integer
    
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            txtID = ListBox1.Column(0, i)
            txtBrand = ListBox1.Column(1, i)
            txtName = ListBox1.Column(2, i)
            txtContact = ListBox1.Column(3, i)
            txtEmail = ListBox1.Column(4, i)

        End If
    Next i

End Sub

Code 2

Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim i As Integer
    
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            txtID = ListBox1.Column(0, i)
            txtDecision = ListBox1.Column(1, i)
            txtStatus = ListBox1.Column(2, i)

        End If
    Next i

End Sub

 

[welshgasman]

I would have thought you would need to decide that yourself in someway, option button, combo etc.?

 

[xenou]

Yes, if you have an optio button or something that shows the choice that's great.

You also might use a hidden field or non-hidden cell, an excel Name, a global variable (hiss! boo!), even set a value in your database ... i.e., when you run Macro1 you need to mark some where that Table1 is in use, and when you run Macro2 mark somewhere that table2 is in use.

Another option is if there is a way to tell from the listbox contents itself which table is in use then you can just use an "inspection" approach - if table1 shows 5 columns and table2 shows 6 columns, for instance, or if they have different data types, or if you have access to the columns names, or even some connection that describes the data source ...

a kind of hacky solution would be assume one table source and if it fails then try the other...

 

[owen4512]

Yes, if you have an optio button or something that shows the choice that's great.

You also might use a hidden field or non-hidden cell, an excel Name, a global variable (hiss! boo!), even set a value in your database ... i.e., when you run Macro1 you need to mark some where that Table1 is in use, and when you run Macro2 mark somewhere that table2 is in use.

Another option is if there is a way to tell from the listbox contents itself which table is in use then you can just use an "inspection" approach - if table1 shows 5 columns and table2 shows 6 columns, for instance, or if they have different data types, or if you have access to the columns names, or even some connection that describes the data source ...

a kind of hacky solution would be assume one table source and if it fails then try the other...

Great suggestions! I'll give it a go! Thank you for your help

 

[Micron]

I'm going to assume that by macro, you don't mean code.
You know which table is needed based on the macro, so if the macro can call a procedure (code) then you could set the required listbox properties such as column count, widths, bound column etc. You'd pass a certain parameter to the procedure (based on which table you want to use) as part of the process.

Perhaps I'm not understanding what it is you want to do because I don't see how just knowing which table is involved or which option button is chosen will help in a case where one data source has n fields and another one has x fields - especially since it's not likely that the fields even have the same names between the tables.