• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

Filter Button (1 Viewer)

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
I'm using the following in a command button on a form...

DoCmd.GoToControl "Field1"
DoCmd.RunCommand acCmdFilterMenu

How can I make it go straight to a contains text filter?

Also, can I combine fields for this search?

Thanks
 

Minty

AWF VIP
Local time
Today, 14:29
Joined
Jul 26, 2013
Messages
7,168
The more common way to do this would be to capture the value of Field1 and use that in your filter? Something like

Code:
Me.Filter = "Field1 Like '*" & Me.Field1 & "*'"
Me.FilterOn = True
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
that won't work. I don't know that value. I want to be able to search for say "apple" or something I can type in the search field.
My above code works just fine, I just want it to popup into the "contains text" filter
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:29
Joined
Oct 29, 2018
Messages
11,085
that won't work. I don't know that value. I want to be able to search for say "apple" or something I can type in the search field.
My above code works just fine, I just want it to popup into the "contains text" filter
Hi. Maybe in conjunction with Minty's code, you could also use the InputBox() function.
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
Can you give me an example of how that would look?

Thanks
 

Micron

AWF VIP
Local time
Today, 09:29
Joined
Oct 20, 2018
Messages
3,383
So you type in"apple", exit the control and Minty's code runs in the AfterUpdate event for that control - no input box and code needed.
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
All I want is to be able to get to the custom filter popup, so I can enter a value. All done within the command button

1590502638279.png
 

Micron

AWF VIP
Local time
Today, 09:29
Joined
Oct 20, 2018
Messages
3,383
So now the filter input and the records are on 2 separate forms? Or is that just a cropped picture of the form with the records?
You need to tell us exactly what you're doing, otherwise we're just guessing.
It seems you are over-complicating things by having 2 forms when you can do this on the form with the records much easier. If that's of no interest, then you need to pass the value back to the main form from whatever takes the input. In that case, I'd go with the Input Box, but now you've got 5x as much code and need to cancel the filter code if the user doesn't enter anything.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:29
Joined
Oct 29, 2018
Messages
11,085
All I want is to be able to get to the custom filter popup, so I can enter a value. All done within the command button

View attachment 82428
I don't think you'll be able to, so the InputBox() would probably be the next best thing. For example:
Code:
Dim strFilter As String
strFilter = InputBox("Field1 contains")
If strFilter<>"" Then
    Me.Filter = "Field1 Like '*" & strFilter & "*'"
    Me.FilterOn = True
End If
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
So now the filter input and the records are on 2 separate forms? Or is that just a cropped picture of the form with the records?
You need to tell us exactly what you're doing, otherwise we're just guessing.
It seems you are over-complicating things by having 2 forms when you can do this on the form with the records much easier. If that's of no interest, then you need to pass the value back to the main form from whatever takes the input. In that case, I'd go with the Input Box, but now you've got 5x as much code and need to cancel the filter code if the user doesn't enter anything.
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
Sorry, I not sure what you are talking about. One form only.
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
I don't think you'll be able to, so the InputBox() would probably be the next best thing. For example:
Code:
Dim strFilter As String
strFilter = InputBox("Field1 contains")
If strFilter<>"" Then
    Me.Filter = "Field1 Like '*" & strFilter & "*'"
    Me.FilterOn = True
End If
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
Perfect!! Now, how can I combine or look at multiple fields?
 

Minty

AWF VIP
Local time
Today, 14:29
Joined
Jul 26, 2013
Messages
7,168
Just change the filter string to accommodate, something like

"Field1 Like '*" & strFilter & "*' OR Field2 Like '*" & strFilter & "*'"

Note that this will get painfully slow on larger datasets, as the wild cards at the start of the search expression(s) negate the use of indexes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:29
Joined
Oct 29, 2018
Messages
11,085
Perfect!! Now, how can I combine or look at multiple fields?
Hi. When you use the FilterMenu, you are limited to an AND operation. So, just to clarify, do you also want an AND comparison or an OR instead?
 

kitty77

Registered User.
Local time
Today, 09:29
Joined
May 27, 2019
Messages
232
I want to be able to check three fields at one time for a value, so I think OR works here.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:29
Joined
Oct 29, 2018
Messages
11,085
I want to be able to check three fields at one time for a value, so I think OR works here.
Then, Minty's sample code should work for you. Have you tried it yet?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom