Filter code in Access 2007

[Misty R]

I had code written in 2003 that works fine. It doesn't work in 2007.
It is code that filters my query

In a form
I have an option group (to choose which field to filter on)
An unbound control - (txtKeyword)
they type the word they are looking for then click the option button of their choice
Here is the code:
if optchoice=1 then
DoCmd.ApplyFilter "qfltBidItems", "[Item] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

Elseif optChoice=2 then
DoCmd.ApplyFilter "qfltBidItems", "[ItemCode] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

ElseIf optchoice=3 then
DoCmd.ApplyFilter "qfltBidItems", "[SpecSection] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"
End if

I haven't read that any of these actions are not now available in 07. Any suggestions?

 

[boblarson]

I have to admit that I haven't used that one and instead use the form, or report's, filter property and the FilterOn method.

Are you using this directly with queries, or with a form?

Also, just to clean it up a bit, I would use a Select Case statement instead of the If ...ElseIf.

If you are using this with a form then you can use this instead:

Me.Filter = "[Item] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"
Me.FilterOn = True

 

[Misty R]

filter code in Access 2007

Thanks. I agree, I would use Case Is - but this is someone else's code and he won't let me change it (because he isn't familiar with that statement) So I am limited.
I have figured out that the first time I choose an option button - the filter works. But for some reason it wants to always hold onto my first option. Never let it go.
For example:
I choose Option 3, Hit Search
It filters on Option 3
I type another word in, choose to filter on Option 3, hit search
Only it still searches Option 3. I can't get it to let go of my first choice.

 

[boblarson]

What event do you have that code on? If it isn't on the Option Group's After Update event, it should be.

 

[Misty R]

The code to check the option group is on a button called Search - the onclick event
I went ahead and put it also on the after update event of the group. No luck. I can't help but wonder if there is a command in 03 that 07 doesn't like. Just can't figure out what it would be
By the way - thanks for looking at this!

 

[boblarson]

Does any of your other code work? Also, I don't think you addressed the question about what the filter is filtering. If it is a form you can use the form's filter instead of this and it will definitely work. See my post (post 2) for that.

 

[Misty R]

It is filtering a form - based on a query.
I've looked at the filter property, to filter on one field, but with the three options I need the user to be able to choose from, can I include those in the filter property?

 

[boblarson]

This would be the way to do it -

If filtering the same form as the option group is on:

If optchoice=1 then
Me.Filter = "[Item] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

Elseif optChoice=2 then
Me.Filter = "[ItemCode] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

ElseIf optchoice=3 then
Me.Filter = "[SpecSection] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

End If
Me.FilterOn = True

And if on a different form:

If optchoice=1 then
Forms!YourFormNameHere.Filter = "[Item] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

Elseif optChoice=2 then
Forms!YourFormNameHere.Filter = "[ItemCode] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

ElseIf optchoice=3 then
Forms!YourFormNameHere.Filter = "[SpecSection] Like '*'&[Forms]![frmMasterBidItems]![txtKeyword]&'*'"

End If
Forms!YourFormNameHere.FilterOn = True

 

[kajtam]

Helped me a lot!