Select which query to run on main form

[Keefay78]

So I have a table with operator and job types (and other fields with hours worked etc)


I wanted to run a search on my main form to select an operator and a job type and get a list so I could work out hours etc.

I struggled a bit with multiple search criteria so made multiple queries instead, (like a query for operator1 and jobtype1, a query for operator1 and jobtype 2) etc..
this probably isnt the most efficient way but so far it works.

Is there a way on my form I could select one of these queries and have the results shown in a subform underneath?

Thanks for any help.

[Minty]

That is very inefficient, have a look here at the best way to build a search form;

http://allenbrowne.com/ser-62.html

It has an example db to download and the whole site is an excellent resource for Access.

[rpeare]

I think all you need is a single query with a field with a calculated value

i.e.

Show: iif(isnull(forms!formname!operator) and isnull(forms!formname!jobtype), 1, -- This part says if both the operator and jobtype are missing, show everything
iif(not isnull(forms!formname!operator) and forms!formname!operator = [Operator] and isnull(forms!formname!jobtype), 1, --- this part says if the operator on the form is filled in and it matches the operator in your data set and the jobtype is null show the record
iif(not isnull(forms!formname!jobtype) and forms!formname!jobtype = [JobType] and isnull(forms!formname!operator), 1, --- this part says if the jobtype on the form is filled in and it matches the jobtype in your data set and the operator is null show the record
iif(forms!formname!jobtype = [jobtype] and forms!formname!operator = [operator], 1, 0)))) ---- this last part assumes both the job type and operator are present on your form and if they are and match anything in your data show the record.

Then all you need is a criteria of 1 in the SHOW column and you don't need to change the record source or row source at all, just requery the form/listbox.

[Gicu]

As the others said there are better ways to do what you want, but to answer your initial question all you need is to add a combo box on your main form that lists the names of the queries (either as a value list or better from a small table) then simply add code to the combo's afterupdate event to do Me.sfrmYourSubformName.Form.Recordsource=me.cboYou rComboName.

Cheers,
Vlad