I'm back  

I've been using an SQL statement to populate three list boxes on a simple form. I'm not sure exactly how to write the VBA code to populate the list boxes. The most important thing is that the code does not rely on a paticular table name. They need to be able to take whatever table that is in the database and pull form that.

Thanks ahead of time.

What office app are you using? (Access? Is the SQL you're referring to the RowSource of the Listbox?)

____________________________
Eggheads unite! You have nothing to lose but your yolks.

I am using Access 97 and this is the Row Source..... Here is the SQL I'm using right now to populate the list boxes:

SELECT DISTINCT Title FROM Build ORDER BY TITLE;

Which is fine for looking under one table. But I need this form to be able to switch to a different table (6 to be exact) in which to pull it's data from. So I thought the best route to go is to have a pull down menu that has each of the table names listed in it. When the user selects a new table name it would update the RowSource SQL in each of the three list boxes and display the new data.

I think that made sence.....

makes sense I think...

Put something like this in your Combo box [Edit: Or Menu]  change event...

Private Sub Combo0 [Edit: Or Menu] _Change()    List1.RowSource = "SELECT DISTINCT * FROM [" & Combo0.Value & "]"    'List2.RowSource = ?    'List3.RowSource = ?    'etc. End Sub

[Edited by ~Bean~ on 02-06-2003 at 06:21 PM GMT]

____________________________
Eggheads unite! You have nothing to lose but your yolks.

You'll have to excuse me being dense this morning but I'm rather amature when it comes to VBA and programming and the like.

This is what I have so far....

Private Sub tableList() [Edit: or Menu] Change() Build.RowSource = "SELECT DISTINCT * FROM [" & tableList.Value & "]" Test1.RowSource = "SELECT DISTINCT * FROM [" & tableList.Value & "]" Test2.RowSource = "SELECT DISTINCT * FROM [" & tableList.Value & "]" Test2.RowSource = "SELECT DISTINCT * FROM [" & tableList.Value & "]" End Sub

Now as you can see I haven't utilized the [Edit: or Menu] Change() yet. For two reasons... I don't really understand exactly how that code snippet needs to work. I'm geussing that is the reason why nothing is working as of yet. Also, Once I get that piece of code straightned out will I need to adjust the rowsource for the three list boxes to take the data from this combo box?

Thanks Again.

Sry for the misunderstanding...plz remove the [Edit: or Menu] Change() from your code...thats just my note where I went back to my post and [Editted] it to reflect that this snippet can be used either after a Menu change event or a Combo box change event.


Here's how to go about it...

- Decide how the user will make the Table choice. Will you have your tables listed in a Combobox or a Listbox or in a menu? Each control has a set of Events that fire when a certain condition is met - in this case, I was coding my snippet to fire when the Combo_Change (combobox) event fires (this means the user has changed the selection in the combobox). When that event happens, the List1,2,3,etc. boxes RowSources are changed to reflect the users choice in the Combobox (looks like you have coded this correctly). You need to either place your code in a Change event, or Call your Sub tableList from a change event.

____________________________
Eggheads unite! You have nothing to lose but your yolks.