Query criteria, multiple tables

[Vicker]

Hi

The problem i have is pretty simple actually.



I have DB, with 2 tables in it. Main tables are Profession and Salary (which inlcude various information on Profession and Salary respectively).
Now i have built a form that includes 2 comboboxes.

In the first one, the user chooses his own name from the list and in the other box he chooses the table he wishes to examine.

So if Mr. Smith would like to receive he's info on in the Salary table, he would choose "Mr. Smith" from the first combobox and "Salary" in the second combobox. After hitting a command button the query would show all the records Mr Smiths records from the "Salary" table.

The problem is that i really have no idea how to do this.

Ideally the SQL would look something like this:

SELECT *
FROM Forms!my_form!choose_table
WHERE Forms!my_form!choose_table.Name = Forms!my_form!choose_name

But i know that you cannot actually do that.

Should i try to number the table names by using slave tables which identifies which table i want to be examening?

Any help would greatly be appreciated.

[jwreding]

I don't know exactly how to do it with SQL, but in my DB I have several queries that use the value of a combobox as criteria. In the query builder, I just use the expression builder to use the combobox value as the criteria.

For example, I have the CustomerID field criteria set to the value of a cboShipperName combobox. I went to SQL view and the part of the WHERE clause that had this criteria looked like this (I couldn't paste all the code because it's a long, complex query):

((Loads.CustomerID)=[Forms]![mnuDailyOps]![cboShipperName])

[Vicker]

Now that i have thought about this more, i think that this is impossible to accomplish without some VBA