Access, VB and coma seperated lists

hey all

#im trying to create a program in vb that reads from a relational database. i would like to have three drop down boxes were the user chooses from the first, which tells the second which data to show then when they choose from the second they can choose again from the third. that said VB seams to want a coma seperated list, alas i have a quite mighty database(around 40,000 products) so is there some kinda automated way of doing this. thnx evo

I've done this before when I wrote a frontend for my Access tables, if I'm understanding you correctly. Your friend in this situation would be custom SQL statements. For instance, let's take this hypothetical situation, assuming VB.Net 2005:

I have a table with 40,000 different products named tblProducts. I open my OleDbConnection and make myself an OleDbCommand object so I can use SQL statements on the database table, as well as a DataTable object for me to dump the results into, and finally a OleDbDataAdapter to fill the datatable using the SQL Command.

I have three comboboxes. One lists the product types, one lists the specific product names, and one lists the sizes those products come in. Similarly, my table has columns for product type, product name, and product size (and probably product price, although your user won't get to choose that).

All of the comboboxes are initialized as blank when the program loads. The program fills the first combobox, on load, with the sizes (get creative when eliminating duplicates) from the table column that has the sizes. When the user picks a size, the ComboBox_SelectedIndexChanged event that we'll use will populate the second combobox with product names for that size by doing the following:

1. Use the Command object from before to make our SQL statement. For instance, if the user chose "Large" from the combobox, we'd use "SELECT * from tblProducts WHERE size='Large'".
2. Use the DataAdapter object to fill the DataTable using that SQL statement.
3. Loop from 0 to tblProducts.Rows.Count - 1 (remember, 0-based indexes), putting each value from the productname column into the second combobox.

You can use a similar technique for linking your second and third comboboxes… you'll just be looking for two matches with your SQL statement. For anyone trying to use any programming language with any database, you have to learn SQL. There's really no getting around it; once you do, though, there's really not much you can't do.

Hope that helps some.