How to do more than one filter at the same time?

[data808]

I have a split form that the datasheet is on the bottom. Then I have 5 unbound text boxes on the top of this split form, each with its own filter button and of course a clear filter button on the side. The problem is I can only use one filter at a time. Here is the code I am using behind the filter button for each of my unbound text boxes:



Private Sub cmdFilterDateIssued_Click()

Me.Filter = "[Date Issued:] Like '*" & Me.txtFilterDateIssued & "*'"
Me.FilterOn = True
Me.Requery

End Sub

Here is another one:

Private Sub cmdFilterOpLicNum_Click()

Me.Filter = "[Op Lic:] Like '*" & Me.txtFilterOpLicNum & "*'"
Me.FilterOn = True
Me.Requery

End Sub

How would I go about setting it up so I can first do a search on Date Issued? Then add on another search to narrow the results even further by doing the next search on the Operator Licenses?

Any help is appreciated. Thanks.

[pbaldy]

I'd either have one button or no button. I'd have a function that tested each textbox and built a filter using any that were filled out. I'd call that function from the after update event of each textbox, or use a button if there were performance issues with the first. Building SQL in code is demonstrated in the sample db here:

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

You would tweak it to just build a filter.

[data808]

Still a beginner in vba and don't know anything about sql. Would you have a template that I can just change the code to fit my situation?

[pbaldy]

Just that sample db.

[June7]

Maybe this will also help http://allenbrowne.com/ser-62code.html

[data808]

Hey June7,

I think you sent this to me before and its making a little more sense now. Is this a split form that Allen Browne is using? The part I'm not to clear on is at the end on the before insert event when he talks about allowing additions. Does this mean that if its a frontend/backend database with multi users entering data, you will not be able to see new records being entered unless you refresh the page by closing the form and reopening it? Could you explain this Before Insert sub and what exactly its doing?

[June7]

I believe this code was written before split form was ever a feature in Access. Shouldn't make any difference.

I've never noticed the BeforeInsert procedure. Did you explore the link Allen shows in the procedure comment?

A form does not have to be closed and reopened to display new records, a Requery will do that.

[data808]

Yeah I followed the link but it didn't make sense to me. Anyway, thanks for the info. I'll try it out and let you know how it goes.