Continuous form search

[vaughanmcd]

Hello I have this bit of code for a search on a continuous form and it is working , I want improve it a bit by adding a check box to only include a predefined word in the data on the form "True"
How would I change the code to allow the results to only include records with the word "True" from the Inventory Item Inventory Item Column?
I also want to change the search box to allow two phrases across the 3 columns, It currently will only search the term as it is written in the box, So if I want to find a record with 123 from [part number] and fox from [description] I have to type one or the other I cant type both phrases with a space.

Private Sub Command11_Click()
Me.Finder.SetFocus
Me.FilterOn = False
Me.Filter = "[Part Number] like '*" & Finder & "*' or [Universal Product Code] like '*" & Finder & "*' or [Description] like '*" & Finder & "*'"
Me.FilterOn = True
End Sub

 

[The_Doc_Man]

You would modify the assignment of Me.Filter

If you are saying that "no matter about Part Number or Universal Product Code or Description, the field Inventory Item ALSO has to equal 'True' " then you can do this in a simple 2nd step (easier to type it, the way I'm suggesting). Just after the first Me.Filter = statement, ADD one line like I am showing below:

Private Sub Command11_Click()
Me.Finder.SetFocus
Me.FilterOn = False
Me.Filter = "[Part Number] like '*" & Finder & "*' or [Universal Product Code] like '*" & Finder & "*' or [Description] like '*" & Finder & "*'"
Me.Filter = "(" & Me.Filter & ") And ([Inventory Item]= 'True' )"
Me.FilterOn = True
End Sub

There are other ways to do this, but this is the simplest, quickest way and you don't have to retype what you already typed. This filter addition will not take effect until the next line, when you turn on Me.FilterOn to enable filtering. Therefore it doesn't matter that you build the filter in two parts.

By the way, that was your first post. Welcome to the forum.

 

[theDBguy]

Hi. Welcome to AWF!

I think one approach is to use separate search input box for each search keyword.

 

[vaughanmcd]

Just to explain the check box a bit further , I was hoping to tie the check box in so that if the user checked it the filter would only include records with True in the Inventory Item column. See my attachment screen shot.

Thanks

 

[vaughanmcd]

You would modify the assignment of Me.Filter

If you are saying that "no matter about Part Number or Universal Product Code or Description, the field Inventory Item ALSO has to equal 'True' " then you can do this in a simple 2nd step (easier to type it, the way I'm suggesting). Just after the first Me.Filter = statement, ADD one line like I am showing below:

Private Sub Command11_Click()
Me.Finder.SetFocus
Me.FilterOn = False
Me.Filter = "[Part Number] like '*" & Finder & "*' or [Universal Product Code] like '*" & Finder & "*' or [Description] like '*" & Finder & "*'"
Me.Filter = "(" & Me.Filter & ") And ([Inventory Item]= 'True' )"
Me.FilterOn = True
End Sub

There are other ways to do this, but this is the simplest, quickest way and you don't have to retype what you already typed. This filter addition will not take effect until the next line, when you turn on Me.FilterOn to enable filtering. Therefore it doesn't matter that you build the filter in two parts.

By the way, that was your first post. Welcome to the forum.

Hi been playing around with the VBA code , but my knowledge of this is poor. I'm wanting to have the check box on the form to filter to show only Inventory Items that have been marked as True and the refresh the list.
Then the same in reverse, If the user takes the check mark off then list is refreshed to show all records no filtering.

The search box is working just fine on its own but I need the the check box to be a kind pre filter so if the user checks the check box the list will always only show inventory items no mater what the search box is filtering.


This is the check box code so far but only works one way, will not reset list to show all once checked.

Private Sub Check_Inventory_Item_Only_Click()
Me.FilterOn = False
Me.Filter = "[Inventory Item]= 'True'"
Me.FilterOn = True
End Sub

This is the search box , which seems to work ok, Just not sure how to put them together so the check box take priority.

Private Sub Command11_Click()
Me.Finder.SetFocus
Me.FilterOn = False
Me.Filter = "[Part Number] like '*" & Finder & "*' or [Universal Product Code] like '*" & Finder & "*' or [Description] like '*" & Finder & "*'"
Me.FilterOn = True
End Sub

I hope this make sense.
Any help would be appreciated.