Dynamic Query Fields

**dmhfflwjl** · Jun 8, 2004 18:23

Hello!

I am using MS Access 2000

Is there any way to update a query field based on a combo box selection?

The combo box row source is the field list for "Table1"

Table1 is a storage table with >50 fields, basically:
"WorkerInitials" and then their aptitude in a variety of skills, such as "Task1" "Task2", etc.

I would like to be able to have a user select a skill on which to base the query such that if I selected Task33, the query (and subsequent report) would spit out

Worker Task33
ABC not trained
BCD trained
CDE in training
DEF trained
... ...

is there some SQL code or vb script that can do this?

**Dr John** · Jun 9, 2004 04:38

It's built into Access. Look at the help under "query by form". It's a while since I used it so I can't remember the steps, but it is very straightforward indeed.

**dmhfflwjl** · Jun 9, 2004 20:00

I think the QBF would give me select records for whichever fields I chose to base my query (presuming multiple text boxes, each representative of the different fields), but all the other fields would show up as well, and/or I would have to set up >50 text boxes for the fields.

I am trying to have only two columns displayed in the Query design view (and ultimately a report): the WorkerInitials field, and then the other field, which would be based on the field selected in a combo box populated by a field list, and show all records

**Dr John** · Jun 10, 2004 08:33

Now I see the problem. My mistake.

This probably isn't what you want, but you could write all the required scripts and use the drop down list to chose which one run via a switch case in vba. Which of course is a lot of work and not a nice compact answer.

An alternative would be to pass the name of the field selected to a vba script which then used concatenation to construct the desired sql from existing bits and then ran it.