Passing Column Name Parameter to Query

[alex44]

Greetings!

I am an Access newb and this is my first post to this forum.

I've spent some time searching the forum and google, but I have not been able to find an answer to my question.

SUMMARY:
In Access 2007, can I pass a Column Name as a parameter from a Combo Box in a form?

DETAILS:
I would like to use a String value from a Combo Box as a parameter in a Inner Join query:
...
WHERE (((AAA.HSC) Like Forms!My_Form!My_TextBox)
AND((CCC.Forms!My_Form!My_ComboBox)="X"))

Where "CCC is a Table from my Join Query

I'm able to run the code above without generating any errors. However, instead of the query accepting the value from the Combo Box, it opens a pop up asking for the value of "CCC.Forms!My_Form!My_ComboBox", not once, but twice! After it finally runs, the result set is empty when it should not be. I also find it strange that it is asking for the combo box value with the Table name appended to the front.

Thanks to all in advance for any advice you can offer,

Alex

 

[pbaldy]

The parameter prompt is Access telling you it can't find something, so it's not strange that it asks for what you typed, which is the table name appended to a form reference (which is invalid by the way). The short answer is that you can't do what you're trying to do. To have a dynamic field, you'd need to build the query in VBA.

 

[alex44]

Paul,

Thank you very much for your reply! You have confirmed my worst fears I'll begin transfering the query to VBA.

One question- what is the best way to output the results of the query from VBA? For example, one of my co-workers does this by creating a table in the DB that is simply re-populated each time that from is run. I'm new to this however, that doesn't seem like a 'clean' approach. Is there perhaps a way to pass this to a new query? Or maybe another best practice?

Thanks again for your help!

Alex

 

[pr2-eugin]

If you create the Query on the fly.. Then you can simply assign that Query as the RecordSource to the Form in the Form_Open method..

Does not have to be Created as a table.. As this will result in bloat of the DB..

 

[pbaldy]

Depends on the ultimate goal, but for presenting the data to the user I might set the record source of a form/subform to the SQL, or change the SQL of a saved query.