Using a variable from a form in the field section of a query

[St_man]

Sorry to try this again but I posted this a few days ago and got no real
response. Is there someone that can help!

I need to set up a form with a list box that reads from a table. then after
highlighting my choice, run a query that takes the highlighted item that is a
table field name, and uses it to search in a different table for all the
records that meet a "true" criteria under that field name.
There may already be a question like this on this forum. If so, please
point it out to me or give me some direction.

Explaining it another way:
I'm just trying to figure out how to reference in a variable, the "headings"
of a table. In this way I can query
just that column of the table. I want to use a list box on a form to change
which column I am querying at my whim.
I work at a plant that has 30 years of training records on paper. It is
time we start to track training electronically. The table in question is one
in which training names are the field names, except for one. That one is
various job titles at the facility.
The records are true/false noting whether or not the job requires a specific
training subject.
Is doing this extremely easy (and I'm not seeing it) or is it extremely
hard? Any help is appreciated.

Thanks
Stan

 

[Klatuu]

Queries don't see variables at any level. You have a couple of options and a
few issues. You don't see whether this is a single or multi select list box.
If it is a multi select, you can't use it's value in a query. If it is a
single select, I suggest you change it to a combo box. It has a couple of
advantages. First, you can create a row source for a combo based on the
field names of a table. This may make your life a litte easier. And, values
in controls on a form (with the multi select list box being the exception)
are available to queries. Then you can reference the control in a query.

Another method is to use a Static Function. Before you run the query,
initiate the return value of the funtion, then use the function in your query.

Now on to the real problem. Your database design needs some work. If you
have a Yes/No field for each type of training, you are way off in the wrong
direction. What you should have is 3 tables. An Employee table, a Training
table, and an Employee/Training table. Since Employee to training is many to
many, you use the Employee/Traning table to record which employees have had
which training.

If you use this technique, things will be a lot easier. If you continue on
your current path, you are only setting up an environment that will be
problematic. You will find creating reports, forms, etc. much harder than it
has to be.

Please seriously consider this change.