Search Box

[Kila]

I have a form with a search box based on a Last Name field. How can I alter it to search by last name only:
Jones

OR last name AND first name with a comma in between:
Jones, John

Thanks for any suggestions!

 

[Ukraine82]

I suggest your make a cascading combo box. If you're not sure how to do it search the forum on cascading combobox.

hth,
Michael

 

[NewfieSarah]

I am trying to figure out the same thing for one of my forms, but for the mean time I have been using this code, for text box, and listbox... maybe this will help you. inthe mean time if I find anything more, I will let you know, and if you find something it would be great if you could pass it on..

CODE:

Private Sub Form_Open(Cancel As Integer)
'this will cleard the form to load as normal when relaoded after a search
Me!lstResult.RowSource = " "
End Sub

Private Sub lstResult_DblClick(Cancel As Integer)
'this will display the form for that specific record
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Personal"

stLinkCriteria = "[Last Name]=" & "'" & Me![lstResult] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Private Sub txtLastName_AfterUpdate()
'this will search for the record in the table and list it in the list box
Dim strSQL As String
strSQL = "SELECT DISTINCT [Last Name],[Emp Num],[First Name] FROM Personal"
strSQL = strSQL & " WHERE [Last Name] Like '" & Replace(Me!txtLastName, "'", "''") & "*'"
strSQL = strSQL & " ORDER BY 1"
Me!lstResult.RowSource = strSQL
End Sub