Access 2007: Looking up data in one table based on selection in a combo box

Question

I'm currently working on a project where I'd like to display all the data from a given table based on a selection made in a Combo Box.

What I have currently is the following:

Four tables (T1, T2, T3, T4) each with two columns of data (Values1, Values2).

One combo box which contains the names of these look up tables. The user selects one of these options to display the data from the specific table.

Two other combo boxes, where I'd like to place the data from the chosen table into.

If I were writing C# I could do something like:

String query = "SELECT (Values1, Values2) FROM " + TableName;
var rows = doQueryAndGetRows(query);
displayMyrows(rows);

How can I do something similar in Access? I've never really used it before, and I don't have much experience with VB for applications either.

Could you elaborate more? Are your project VBA? Acess-plugin? — SOUser
I could use either, since I'm working on the project alone. Which approach would be easier and/or more flexible? — Mike Bailey
@Mike: The development environment for VBA is less attractive than Visual Studio, which one could use for developing plugins. Anyhow, if you go for VBA, create the combo box in VBA and capture the selection in the event handler; if you go for plugin, you basically do the same but using powerful Access Object Library for .NET. I am sorry but I most likely don't understand what I should focus on to answer your question. Perhaps you could ask for more specific details so that I can add later. — SOUser
@Xichen Li, I've updated my question to have the specific details of what I'm trying to accomplish. — Mike Bailey
@Mike: Thanks for the additional details. I need to ask another question, do you plan to use Access as a front end? Or do you plan to use Access file as database? — SOUser

Fionnuala Fionnuala · Accepted Answer · 2011-03-11T21:37:52

In Access you can have cascading combo boxes, which would seem to be what you want for your additional combos, subforms, which would seem to be where you wish to display your data, and a number of other easy ways to do what you wish.

For example, in the After Update event of your combo box, you could simply set the Record Source of a subform to the table name:

Me.MySubformControl.Form.recordSource = Me.MyCombo

Assuming that the bound column of the combo contains the table name, or using the column property, if some other column contains the name.

As for cascading combo boxes: Is there a simple way of populating dropdown in this Access Database schema? or http://support.microsoft.com/kb/289670

Access 2007: Looking up data in one table based on selection in a combo box

1 Answers