Clearing the query on open of a search form

[DanEaton]

I have a "Cross-Reference Search Form" which has several combo boxes to choose from. I finally got this thing to work but there is one minor problem that is a major annoyance. I based a form on a query, and put in SQL criteria for the query which pretty much said
ie.
....WHERE ((field name)=[forms]![frmName]![Control] OR IsNull...) AND ...

Anyway, the SQL wasn't the problem, but it kinda is. You see, because I have allowed certain combo's to be null, when I open the form, it assumes that everything is Null, thereby displaying EVERY SINGLE match, AKA everything. This is nothing more than an aesthetic problem, but aesthetics are very important where I work so this will not suffice. As you can imagine, I have a "search" button that is essentially requering the forms underlying values based on the combo boxes being selected, so this problem 'disappears' after one search. Has anyone ever ran into this before? I just want it to be cleared when I open up the query...Thanks Dan Eaton
deaton@caemachinery.com

 

[JimInKS]

You can set the "default" value property for each combo on the form.

 

[EnidtheDog]

Hi Dan,

This may be more than astehtics as you are using unnecessary resources populating your search with unneeded records.

There is an alternative...

I have the search results returned in a listbox. Initially the listbox is unbound - it has no Row Source.

The RowSource is set by an after update event on the enquiry fields.. eg:

MyCriteria Afterupdate()
Me![List0].RowSource = "SELECT ACNumber, JobNo, Status, FROM tblJobs WHERE ... etc"

This way the search form loads quickly with no records returned. The search ONLY occurs after a user has requested it...

 

[DanEaton]

Thanks for the responses. JimInKS, are you suggesting that I have the combo's set to defaults value's in the OnOpen event of the form? This might work if I set the combo's to obscure values that wouldn't be in the query I guess...As for EnidTheDog's list boxes, I like the idea, but my boss does not, he wants it to remain in the same format (I didn't mention it earlier but my other two search forms are in that format, but they work). If anybody knows how to get around having all the records come up I'm still searching for answers...also, for some new reason, the form will only display one result at a time...is there some property that needs changing? Dan Eaton
deaton@caemachinery.com

 

[DanEaton]

Figured it out, Thanks for your help. Dan Eaton
deaton@caemachinery.com