Search while typing Multi Field Search Form in Ms Access

[skdn71]

Hello

With the VBA below I can only search for data in Search1 [ItemDescription] and Search2 [PPU].

What I want is that I can search for data in Search1 [ItemCode] , [ItemDescription] , [PPU], [City] in search2 also what I want is to be able to search for data [ItemCode] , [ItemDescription] , [PPU], [City]

See examples

Thank you very much for your time

Private Sub txtSearch_Change()
   Me.frmlistprod.Form.Filter = "[ItemDescription] Like '*" & txtSearch.Text & "*' and " _
    & "[PPU] Like '*" & Me.txtSearch2 & "*'"
    Me.frmlistprod.Form.FilterOn = True
End Sub



Private Sub txtSearch2_Change()
   Me.frmlistprod.Form.Filter = "[ItemDescription] Like '*" & Me.txtSearch & "*' and " _
    & "[PPU] Like '*" & txtSearch2.Text & "*'"
    Me.frmlistprod.Form.FilterOn = True
End Sub

 

[theDBguy]

I guess depending on the result you want, you'll have to play with using AND or OR or a combination of the two.

 

[MajP]

Solved - FAYT for Multi Column combo code from MajP question

I have used the great code from MajP FAYT on a few forms and some work perfectly and a few have an issue. I read the post Populating Combobox with a lot of data and the link in there. The issues I am experiencing are When I refresh the underlying table (I click a button on my form and it...

www.access-programmers.co.uk

I have a class that does this.

 

[skdn71]

what I want :
1. If I type in searcht1 the word AXE in [ItemDescription] all data containing AXE will appear and in search2 if I type 24 in [PPU] all data containing 24 will appear after completion all data will appear in [ItemDescription] all data with AXE and [PPU] 24
2. If I type in searcht1 the word KJK in [city] all data containing KJK will appear and in search2 if I type DEO in [ItemDescription] all data containing DEO will appear after completion all data will appear in [ItemDescription] all data with DEO and [City] KJK
3. If I type in searcht1 word 24 in [PPU] and [ItemDescription] all data containing 24 will appear , in search2 if I type LKD in [City] all data containing LKD will appear after completion all data will appear in [ItemDescription] and [PPU] all data with 24 and [City] LKD

Etc

 

[Gasman]

YOU have to tell Access what permutations you want to search on.
Your description makes little sense to me.
Probably best to have a search form for all your fields you want to search on, with a control for each.
Walk through all the controls on the form and if a control has a value then add that to the filter criteria.
Then filter the form with that criteria.

 

[skdn71]

Please help how

 

[Gasman]

Please help how

I have just told you how. Build an unbound form for all the fields you want to search on.
Create that, then come back for more.

 

[skdn71]

Like this ?

 

[Gasman]

Yes, but I would have s search button that tells Access when to run your process.
That will then check each of those controls and build your criteria.

Debug.Print your criteria until you get it correct, so assign that to a variable. Then you can use that as your filter for the subform. You do not really need a subform here, you could just use a Form and have those controls in the header and the data in detail.? Up to you though.

Also if you still cannot get it, you can post back the filter and the output from the debug.print.

Strings need ' surrounding them (unless that string has a ' in it), dates need # and numbers do not need anything.

Give that a go and come back if it still does not work.

 

[skdn71]

I try to use the below VBA but not working :

Private Sub txtCity_KeyDown(KeyCode As Integer, Shift As Integer)
    Me.frmlistprod.Form.Filter = "[ItemCode]Like '*" & Me.txtCity.Text & "*' or" _
    & "[ItemDescription]Like '*" & txtCity.Text & "*' or" _
    & "[PPU]Like '*" & txtCity.Text & "*' or" _
    & "[City]Like '*" & txtCity.Text & "*'"
    Me.frmlistprod.Form.FilterOn = True
End Sub

Private Sub txtItemCode_KeyUp(KeyCode As Integer, Shift As Integer)
    Me.frmlistprod.Form.Filter = "[ItemCode]Like '*" & Me.txtItemCode.Text & "*' or" _
    & "[ItemDescription]Like '*" & txtItemCode.Text & "*' or" _
    & "[PPU]Like '*" & txtItemCode.Text & "*' or" _
    & "[City]Like '*" & txtItemCode.Text & "*'"
    Me.frmlistprod.Form.FilterOn = True
End Sub

Private Sub txtItemDescription_KeyDown(KeyCode As Integer, Shift As Integer)
    Me.frmlistprod.Form.Filter = "[ItemCode]Like '*" & txtItemDescription.Text & "*' or" _
    & "[ItemDescription]Like '*" & txtItemDescription.Text & "*' or" _
    & "[PPU]Like '*" & txtItemDescription.Text & "*' or" _
    & "[City]Like '*" & txtItemDescription.Text & "*'"
    Me.frmlistprod.Form.FilterOn = True
End Sub

Private Sub txtPPU_KeyDown(KeyCode As Integer, Shift As Integer)
    Me.frmlistprod.Form.Filter = "[ItemCode]Like '*" & txtPPU.Text & "*' or" _
    & "[ItemDescription]Like '*" & txtPPU.Text & "*' or" _
    & "[PPU]Like '*" & txtPPU.Text & "*' or" _
    & "[City]Like '*" & txtPPU.Text & "*'"
    Me.frmlistprod.Form.FilterOn = True
End Sub

 

[Gasman]

What good is that?
One filter just replaces another, and that is only if your syntax is correct?
That is why I said to use a button.
You complete the search controls you want to use, then test each one and only then create the filter.

Look at how Allen Browne did it.

Microsoft Access tips: Search criteria

Free sample database for Microsoft Access 2000 and later, demonstrating how to build criteria from many optional entries, and handle different field types, exact matches, partial matches, and ranges.

allenbrowne.com

 

[Josef P.]

Why do you have 4 controls if you only use the value of one control as the filter value?

Maybe that's what you want:

Private Sub txtCity_KeyDown(KeyCode As Integer, Shift As Integer)
    SetFilter Me.txtItemCode.Value, Me.txtItemDescription.Value, Me.txtPPU.Value, Me.txtCity.Text
End Sub

Private Sub txtItemCode_KeyDown(KeyCode As Integer, Shift As Integer)
    SetFilter Me.txtItemCode.Text, Me.txtItemDescription.Value, Me.txtPPU.Value, Me.txtCity.Value
End Sub

Private Sub txtItemDescription_KeyDown(KeyCode As Integer, Shift As Integer)
    SetFilter Me.txtItemCode.Value, Me.txtItemDescription.Text, Me.txtPPU.Value, Me.txtCity.Value
End Sub

Private Sub txtPPU_KeyDown(KeyCode As Integer, Shift As Integer)
    SetFilter Me.txtItemCode.Value, Me.txtItemDescription.Value, Me.txtPPU.Text, Me.txtCity.Value
End Sub

Private Sub SetFilter( _
         ByVal vItemCode As Variant, ByVal vItemDescription As Variant, _
         ByVal vPPU As Variant, ByVal vCity As Variant)

   With Me.frmlistprod.Form
       .Filter = GetFilterString(vItemCode, vItemDescription, vPPU, vCity)
       .FilterOn = True
    End With

End Sub

Private Function GetFilterString( _
         ByVal vItemCode As Variant, ByVal vItemDescription As Variant, _
         ByVal vPPU As Variant, ByVal vCity As Variant) As String

   Dim FilterString As String

   If Len(vItemCode) Then
      FilterString = " and [ItemCode] Like '*" & Replace(vItemCode, "'", "''") & "*'"
   End If
   If Len(vItemDescription) Then
      FilterString = FilterString & " and [ItemDesxription] Like '*" & Replace(vItemDescription, "'", "''") & "*'"
   End If
   If Len(vPPU) Then
      FilterString = FilterString & " and [PPU] Like '*" & Replace(vPPU, "'", "''") & "*'"
   End If
   If Len(vCity) Then
      FilterString = FilterString & " and [City] Like '*" & Replace(vCity, "'", "''") & "*'"
   End If

   If Len(FilterString) > 0 Then
      FilterString = Mid(FilterString, 6)
   End If

   GetFilterString = FilterString

End Function

 

[CJ_London]

I try to use the below VBA but not working :

In part because the syntax is not correct

Since you are using the initial * you are not using indexing which will make this slow with large datasets - but a simplified version might be

Me.frmlistprod.Form.Filter = "[ItemCode] & [ItemDescription] & [PPU] & [City] Like '*" & Me.txtItemCode.Text & "*'"
Me.frmlistprod.Form.FilterOn = True

 

[Gasman]

If you only have one control then I would be using @MajP 's classes in a heartbeat.