Let's back up a little here. Since you're using the same
text box for all the search fields, I think we're always
going to have this problem. If you enter a number in the
SearchField text box, the search string will end up looking
like:
[MailboxNo.] = 123 OR Lname = "123" OR ...
which will work ok.
However, if you enter a name in the SearchField text box,
the search string will end up like this:
[MailboxNo.] = Smith OR Lname = "Smith" OR ...
and you'll get a syntax error because Smith is not a valid
term in an SQL statement.
All that adds up to a fundamental conflict caused by the
ambiguity of using one text box for several fields with
different types. If you'd be willing to use separate text
boxes for each possible criteria, this would be easy to
avoid.
Create 3 text boxes named txtMb, txtLN and txtFN. The users
would then indicate what they wanted to search for by typing
something in one (or more) of the text boxes and the code
behind your search button would be along the lines of this
air code:
Dim strWhere As String
If Not IsNull(txtMb) Then
If IsNumeric(txtMb) Then
strWhere = strWhere & " AND [MailboxNo.]=" & txtMb
Else
MsgBox "invalid mailbox number"
End If
End If
If Not IsNull(txtLN) Then
strWhere = strWhere & " AND [LastName]=""" & txtLN & """"
End If
If Not IsNull(txtFN) Then
strWhere = strWhere & "AND [FirstName]=""" & txtFN & """"
End If
Me.Recordset.FindFirst Mid$(strWhere. 6)
If Me.Recordset.NoMatch Then
. . .
This has the added advantage(?) that users could search for
records that matched multiple criteria such as Smith and
Jack.
James wrote:
I am trying to make a field search these fields and one more called
"Company". I orginally had the second and third FindFirst lines the same as
the first, but I got a type mismatch error. The problem is that nothing
happens. Is this the correct way or should I do something else, and why
won't it do anything?
-------------start code---------------
With Me
.Recordset.FindFirst "CustomerInfo.MailboxNo. = " & !SearchField
.Recordset.FindFirst FirstName = !SearchField
.Recordset.FindFirst LastName = !SearchField
If .Recordset.NoMatch Then
MsgBox "Record can't be found.", , "PackageLog 2005"
.Recordset.MoveFirst
Else
End If
!SearchField.SetFocus
End With
:
You need to do the search with a single FindFirst.
If I understand what you're trying to do with this, I think
you might want to use:
.Recordset.FindFirst "[MailboxNo.] = " _
& !SearchField _
& " OR FirstName = """ & !SearchField & """" _
& " OR LastName = """ & !SearchField & """"
Note the additional quotes that are needed for Text type
fields. I can't tell if the MailBoxNo field is a numeric
type or a text field, so I don't know if you need the quotes
there too.
Also note that you either have an extra period at the end of
the MailboxNo field or you need to enclose the name in sqare
brackets.
