Mutli-parameter text search - calling a query from form?

[SilFaz]

Hi there,
I know that there is a simple solution to my question, but I am having a brain block...

I created a DB to track users that have been trained in a new system and which module training sessions they attended. I have a Training table that collects the Name, Practice Group, and the date that module training took place on.

I am trying to Search create a form where I can enter all or part of the name, and/or the Practice Group, and/or the Date of module training. I created a query that works when I enter the parameters. I want to connect the query to the form, have the info from the form populate the query parameters and return the information in a subform.

My FindQ Query is this:

SELECT Training.Name, Training.Group, Training.MedChart, Training.[Pt Lists], Training.MedHist
FROM Training
WHERE 

(([Training].[Name] Like [Forms]![Frm_Find]![Name] & "*") Or ([Forms]![Frm_Find]![Name] Is Null)) 

AND (([Training].[Group]=[Forms]![Frm_SearchAttempt_Main]![Group]) Or ([Forms]![Frm_SearchAttempt_Main]![Group] Is Null)) 

And (([Training].[MedChart]=[Forms]![Frm_Find]![cldMedChart]) Or ([Forms]![Frm_Find]![cldMedChart] Is Null)) 

And (([Training].[Pt Lists]=[Forms]![Frm_Find]![cldPt Lists]) Or ([Forms]![Frm_Find]![cldPt Lists] Is Null)) 

And (([Training].[MedHist]=[Forms]![Frm_Find]![cldMedHist]) Or ([Forms]![Frm_Find]![cldMedHist] Is Null)) ;

I want this query to be called when I click Search on my Search form.

In my search form, I want Group to be a dropdown box. (I have a seperate table that holds the various groups that users can belong to.) MedChart, Pt Lists, and MedHist are all dates.

Any help is greatly appreciated. I did this several years ago, but I cannot access the DB that created the search on.

Thanks,
Silvia

 

[PHV]

Have a look here:

http://download.microsoft.com/download/access2000/Sample/1.0/WIN98Me/EN-US/Qbf2k.exe

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SilFaz]

Thank PH,
This is a very good example but not quite what I am looking for. I'd like to get a list of all of the records that meet the required parameters - eg. All the people who's name starts with A and they had MedChart training on 14/1/06.

In the subform, I'd like to be able to add information, such as the date that they were trained on another module.

There has to be a simple way of passing those parameters into the query and then to a subform using VBA, no? Unfortunately, my VBA coding skills are quite weak. I've been searching the forums but haven't found what I need yet.

Thanks, Sil

 

[SilFaz]

Any advice, anyone?
Silvia