Use a form to tailor query results

[thekruser]

I would like to use a form to tailor query results. I can do this by entering [Enter some criteria] in the conditions box of the query, but I would like the resulting message box to be a combo box. Can you help me with this?

Thank you in advance for your time and help. Have a great day!

[pbaldy]

Sounds like you already know the answer. You have to use a form if you want a combo box. There's no way with the [Enter...] method. Use a form and your criteria becomes:

Forms!FormName.ComboName

[thekruser]

@pbaldy

Thank you for your reply. I don't know where I would be without your help. however, I am not sure what you mean. Where would I put that code?

What I have:
qryDateMax
-EmployeeName
-Date
-CourseName
-CourseStatus
-FinalScore

This pulls the max date for all records in tblTraining. I would like to have a form that will filter based on one or any combination of the above criteria. The end result will be a "Print Report" command button that will do just that, but one step at a time. First I need to get the right data, then I will worry about the report.

Thanks again for your help!!

[pbaldy]

You replace

[Enter some criteria]

with

Forms!FormName.ComboName

adjusting for the actual names of course. The form must already be open when the query runs. Typically you'd have a form where the user enters the criteria, with a button to open the report.

[thekruser]

So the combo boxes would be bound to tblTraining, I assume? Or would it be to qryDateMax?

[pbaldy]

It would probably not be bound to anything (no control source). It would have a row source that got the selections you wanted it to present to the user.

[thekruser]

Ok...I feel like an idiot. Long day, sorry. In this scenario, if a user left a cbo blank, will that return all records for that criteria, or exclude them all? Just wondering how much control I can give to the user without causing myself headache with a million "It is not showing any results" emails.

[pbaldy]

You can use this technique:

http://www.mvps.org/access/queries/qry0001.htm

Personally I tend to use that when there are only a couple of options. I typically create dynamic SQL or a wherecondition when there are numerous optional criteria. Can't say why, that's just my preference.

[thekruser]

Ok...I tried. Didn't work. Copied the code exactly (replacing the form/cbo names of course) and still only works if all cbos are filled. Where can I go to learn how to create a dynamic SQL?

[pbaldy]

The sample db here demonstrates modifying the SQL of a subform, and the tutorial isn't bad (I didn't write it):

http://www.baldyweb.com/BuildSQL.htm

To open a report, I'd do something similar but only build a string to use in the wherecondition argument of OpenReport (just the WHERE clause portion of the string, without the word "WHERE").