Easy search multiple fields

[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.

 

[Allen Browne]

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

 

[Nathan Guill]

Thank you. This helped.

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.

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.