Search employees by their first/last name in the correct order [A-Z] (1 Viewer)

[Dana_]

Hello everbody,

I have a search field where I can look for an employees by writing their firstname/lastname. It works currently well, but when I look
for firstname/lastname which exists more than ones, i cannot see all of them.

E.g. there are two Persons in the table, who have the lastname Mustermann
(Susi Mustermann, Max Mustermann), when I write Mustermann in the searchfield
it shows me Max Mustermann and when I click on next button it shows me another
entry in the table which is not Susi Mustermann (but in the access table itself after Max follows Susi)

Does somebody have any idea, how could I edit my next/preveous button, such that if I click it, it would show other Mustermanns (if they exist)

The next/preveous button are default buttons from access. This is my currently code for the search Button:
Code:
Private Sub suchBefehl_Click()

Dim strSearch As String
strSearch = Me.txt_Search & ""

If Len(strSearch) Then
Me.MA_Nachname.SetFocus
DoCmd.FindRecord strSearch, acAnywhere

If InStr(Me.MA_Nachname, strSearch) = 0 Then
Me.MA_Vorname.SetFocus
DoCmd.FindRecord strSearch, acAnywhere

If InStr(Me.MA_Vorname, strSearch) = 0 Then
Me.MA_Nachname.SetFocus
DoCmd.FindRecord strSearch, acAnywhere

If InStr(Me.MA_Vorname, strSearch) = 0 Then
MsgBox "No matching record found!", vbInformation
End If
End If
End If
End If
Me.txt_Search.SetFocus
End Sub

 

[Gasman]

You are only finding that record?
Use a filter if you just want the Mustermanns

 

[Dana_]

You are only finding that record?
Use a filter if you just want the Mustermanns

No. Some Employees have the same Firstname/Lastname for example I have three Person which have the Lastname Muller.
When I look for example for Muller at shows me just one record, although I have three records with the same lastname

 

[Gasman]

No. Some Employees have the same Firstname/Lastname for example I have three Person which have the Lastname Muller.
When I look for example for Muller at shows me just one record, although I have three records with the same lastname

And if you filtered for LastName = "Muller" you would have all three?
if you do not get 3 records, then somehow the otehr two are different?

 

[arnelgp]

you can also Order your Recordsource By Lastname, firstname field (using query).

 

[Dana_]

you can also Order your Recordsource By Lastname, firstname field (using query).

I did but it still doesnt work:

And my next button is:

 

[CJ_London]

you don't appear to have a primary key to uniquely identify each record. This is needed to identify which record you have selected.

 

[Gasman]

I did but it still doesnt work:
View attachment 101815

And my next button is:
View attachment 101816

That apepars to be just LastName DESC ?

 

[arnelgp]

if you have Autonumber field (or Numeric PK field) in your form, you can use SearchForRecord:

Private Sub suchBefehl_Click()
Static ID As Long           'this will hold your PK number (or autonumber field)
Static tfNext As Boolean    'whether to search for next record
Static sSearch As String

Dim strSearch As String
strSearch = Me.txt_Search & ""

tfNext = (sSearch = Me!txt_Search)
sSearch = Me!txt_Search

If Len(strSearch) Then
    If Not tfNext Then
        DoCmd.SearchForRecord , , acFirst, "MA_Nachname Like '" & strSearch & "*'"
        ID = Me!ID              'change Me!ID with the name of your PK/Autonumber field
        tfNext = True
   
    Else
        'search for next record
        DoCmd.SearchForRecord , , acFirst, "MA_Nachname Like '" & strSearch & "*'"
        tNext = (ID <> Me!ID)   'change Me!ID with the name of your PK/Autonumber field
        ID = Me!ID              'change Me!ID with the name of your PK/Autonumber field
    End If

Else
    tfNext = False
End If
Me.txt_Search.SetFocus
End Sub

 

[Dana_]

if you have Autonumber field (or Numeric PK field) in your form, you can use SearchForRecord:

Private Sub suchBefehl_Click()
Static ID As Long           'this will hold your PK number (or autonumber field)
Static tfNext As Boolean    'whether to search for next record
Static sSearch As String

Dim strSearch As String
strSearch = Me.txt_Search & ""

tfNext = (sSearch = Me!txt_Search)
sSearch = Me!txt_Search

If Len(strSearch) Then
    If Not tfNext Then
        DoCmd.SearchForRecord , , acFirst, "MA_Nachname Like '" & strSearch & "*'"
        ID = Me!ID              'change Me!ID with the name of your PK/Autonumber field
        tfNext = True
  
    Else
        'search for next record
        DoCmd.SearchForRecord , , acFirst, "MA_Nachname Like '" & strSearch & "*'"
        tNext = (ID <> Me!ID)   'change Me!ID with the name of your PK/Autonumber field
        ID = Me!ID              'change Me!ID with the name of your PK/Autonumber field
    End If

Else
    tfNext = False
End If
Me.txt_Search.SetFocus
End Sub

Thank you for your reply but Unfortunately I get the same result with your code as before. I thought maybe I should change somehow preveous&next button?

 

[Dana_]

you don't appear to have a primary key to uniquely identify each record. This is needed to identify which record you have selected.

I have a primary key but I did this column hidden on my form

you can also Order your Recordsource By Lastname, firstname field (using query)

I ordered it by LastName but still it doesn't work.

 

[arnelgp]

can you post a sample copy of your db?