Help finding a record(s) from multiple fields on a form

[rapierwit]

I have a table that has 2 fields lastname and firstname. I would like to be able to type on a form a firstname and lastname and be able to locate the matching records.

For example on a form type in smith and john and return all records that match, or just smith and leave the other blank and return all the smith records.

 

[LittleSmudge]

In the header of the form put a text box control called txtFirst and another text box control called txtLast and a button called cmdFind

Then in the cmdFind.OnClick event put

Private Sub cmdFind_Click()
Me.FilterOn True
If IsNull(txtFirst) AND IsNull(txtLast) Then
Me.FilterOn False
ElseIf IsNull(txtFirst) Then
Me.Filter = "LastName = '" & txtLast & "'"
ElseIf IsNull(txtLast) Then
Me.Filter = "FirstName = '" & txtFirst & "'"
Else
Me.Filter = "FirstName = '" & txtFirst & "' AND LastName = '" & txtLast & "'"
End If

End Sub



QED.

G LS

 

[rapierwit]

i keep getting an error the says

invalid use of property

it highlights

Me.FilterOn True

 

[LittleSmudge]

Oops. Sorry about the typo rapier. I missed out the equals sign.

Me.FilterOn = True



and - of course

Me.FilterOn = False



QED.

G LS

 

[rapierwit]

i'm sorry to keep bothering you, but i have tried all day and can't get it to work. The error i get now is run time error 13 type mismatch. It highlights the else line. It does work if i only enter the last name. If i enter both it errors. These are all text fields so i'm not sure where there is a mismatch.

 

[LittleSmudge]

Don't worry about coming back it it's not working rapier.

I've looked at it again and the only thing I can think of is a typo in the quotes

Else
    Me.Filter = "FirstName = '" & txtFirst & "' AND LastName = '" & txtLast & "'"

If you still cannot see the problem COPY the actual code from the IF to the End If and post it. I'll then have a look at what you are actually doing.

Err 13s are usually easy enough to fix - but often take a lot of gazing to get there.

G LS