Easy search multiple fields

  • Thread starter Thread starter Nathan Guill
  • Start date Start date
N

Nathan Guill

I have a database (and subsequent form) that has three
separate fields I
would like to be able to search for the same information.
The Fields are:
Part Number, Reference Part Number, Additional Part
Numbers. The reason I
want to search in all three at once is so that I can bring
up all records
that pertain to the part number I am searching for without
having to go back
and search in each field individually. Also, I don't want
to have to make a
separate search code for each part number. I know that
this is not needed, I
just don't know how I can create a search function that
would do this
automatically. Any help would be appreciated. Thanks in
advance.
 
Let's assume you have an unbound text box on your form where the user can
enter the part number to be found. The text box is named txtFindPart. The 3
fields are of type Text.

Set the Filter of the form in the AfterUpdate event procedure of the text
box:

Private Sub txtFindPart_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.txtFindPart) Then
If Me.Dirty Then 'Save before filter.
Me.Dirty = False
End If

strWhere = "([Part Number] = """ & me.txtFindPart & _
""") OR [Reference Part Number] = """ & me.txtFindPart & _
""") OR [Additional Part Numbers] = """ & me.txtFindPart & """)"

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


Alternatively, if the part number will be unique and so you just want to go
to that record, you could FindFirst in the RecordsetClone of the form:

Private Sub txtFindPart_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.txtFindPart) Then
If Me.Dirty Then 'Save before move
Me.Dirty = False
End If

strWhere = "([Part Number] = """ & me.txtFindPart & _
""") OR [Reference Part Number] = """ & me.txtFindPart & _
""") OR [Additional Part Numbers] = """ & me.txtFindPart & """)"

With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub
 
Thank you. This helped.

Allen Browne said:
Let's assume you have an unbound text box on your form where the user can
enter the part number to be found. The text box is named txtFindPart. The 3
fields are of type Text.

Set the Filter of the form in the AfterUpdate event procedure of the text
box:

Private Sub txtFindPart_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.txtFindPart) Then
If Me.Dirty Then 'Save before filter.
Me.Dirty = False
End If

strWhere = "([Part Number] = """ & me.txtFindPart & _
""") OR [Reference Part Number] = """ & me.txtFindPart & _
""") OR [Additional Part Numbers] = """ & me.txtFindPart & """)"

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


Alternatively, if the part number will be unique and so you just want to go
to that record, you could FindFirst in the RecordsetClone of the form:

Private Sub txtFindPart_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.txtFindPart) Then
If Me.Dirty Then 'Save before move
Me.Dirty = False
End If

strWhere = "([Part Number] = """ & me.txtFindPart & _
""") OR [Reference Part Number] = """ & me.txtFindPart & _
""") OR [Additional Part Numbers] = """ & me.txtFindPart & """)"

With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Nathan Guill said:
I have a database (and subsequent form) that has three
separate fields I
would like to be able to search for the same information.
The Fields are:
Part Number, Reference Part Number, Additional Part
Numbers. The reason I
want to search in all three at once is so that I can bring
up all records
that pertain to the part number I am searching for without
having to go back
and search in each field individually. Also, I don't want
to have to make a
separate search code for each part number. I know that
this is not needed, I
just don't know how I can create a search function that
would do this
automatically. Any help would be appreciated. Thanks in
advance.
Click to expand...
Click to expand...
 
Back
Top