Searching Data using a Form, What is the code I assoicate to the Search Button?

[jaygoodwin77]

I am currently creating a database and some help with the search function which i am trying to create using forms controls and subforms.

I have several search fields:-
Course
Instructor
Vendor
Publish
Licensed
Completed TTT

I have a button which I have created, which when pressed will search through the fields above and return the data that matches, however this is where i am currently stuck.

Can anyone help?

 

[JBurlison]

So you want to Filter tru the recordset with multiple criteria that are set via dropdowns correct?

 

[jaygoodwin77]

Yes that is correct, that is what i am trying to do

 

[MStef]

I suggest, do it via FILTER BY FORM, (CLEAR GRID, APPLY FILTER, REMOVE FILTER), icons.

 

[jaygoodwin77]

I really need the search to work through the criteria above.

I am a novice with access, so forgive if the Filters option seems long winded

 

[JBurlison]

use a query to define multple critera

 

[jaygoodwin77]

I am a complete novice to all this, could you help me understand how I use the query to define criteria and then how the filters work

 

[JBurlison]

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

look at post 9

this will show you how =D

 

[ajetrumpet]

the only problem with Ken's example is that it doesn't address what to do if one of the combo boxes are left blank!

Jay,

If I were writing a query for your combo boxes, I would write exactly this as an SQL statement:

SELECT * FROM table WHERE

(([course] = forms!YourForm!CBOcourse OR forms!YourForm!CBOcourse IS NULL) AND 

([instructor] = forms!YourForm!CBOinstructor OR forms!YourForm!CBOinstructor IS NULL) AND

([vendor] = forms!YourForm!CBOvendor OR forms!YourForm!CBOvendor IS NULL) AND

([publish] = forms!YourForm!CBOpublish OR forms!YourForm!CBOpublish IS NULL) AND

([licensed] = forms!YourForm!CBOlicensed OR forms!YourForm!CBOlicensed IS NULL) AND

([completed TTT] = forms!YourForm!CBOcompletedTTT OR forms!YourForm!CBOcompletedTTT IS NULL))

Then you could simply open up a form that is source from this query on the push of the button:

docmd.openform

 

[Kurth]

Hi!
I am sorrz for digging back up this old post but it seems very close to fixing my problem. only close though...
So I tried both JBurlinsons (Post 8) and Adams ways in my database.

Adams way works generally but only displays me with apopup, whioch will seem gibberish to the user to filter my criteria, so I did not try this any further yet, since the other way seemed more promising.

I have tried applying the same settings as proposed in post 8 to one of my forms and that did not work very well. I could get a drop down box, but it would not update the rest of my data. (Yes, I did apply the 'on change' procedure).

I want to use this procedure to find a relevant record (to be found in a query) and then display a related set of data in a subform based on another query.

I hope you can help me with this...

Cheers

Chris