Option Compare Database
Option Explicit
Private Function basOrderby(strKey1 As String, strKey2 As String) As Integer
    Dim strSQL As String
    'Set row source for list box
    If (Len(strKey1) > 0 And Len(strKey2) > 0) Then
        strSQL = "SELECT ipcard, [tname] & [Fname] & ""   "" & [lastName] AS FirstName, ipvate "
        strSQL = strSQL & " FROM tblSalespersonContact "
        strSQL = strSQL & " WHERE ipcard LIKE " & "'*" & strKey1 & "*'" & " OR Fname LIKE " & "'*" & strKey2 & "*'"
        strSQL = strSQL & " ORDER BY ipcard;"
    ElseIf (Len(strKey1) > 0 And Len(strKey2) <= 0) Then
        strSQL = "SELECT ipcard, [tname] & [Fname] & ""   "" & [lastName] AS FirstName, ipvate "
        strSQL = strSQL & " FROM tblSalespersonContact "
        strSQL = strSQL & " WHERE ipcard LIKE " & "'*" & strKey1 & "*'"
        strSQL = strSQL & " ORDER BY ipcard;"
    ElseIf (Len(strKey1) <= 0 And Len(strKey2) > 0) Then
        strSQL = "SELECT ipcard, [tname] & [Fname] & ""   "" & [lastName] AS FirstName, ipvate "
        strSQL = strSQL & " FROM tblSalespersonContact "
        strSQL = strSQL & " WHERE Fname+lastName LIKE " & "'*" & strKey2 & "*'"
        strSQL = strSQL & " ORDER BY ipcard;"
    End If

    Me!lstSearch.RowSource = strSQL
    Me!lstSearch.Requery
End Function

Private Sub cmdSearch_Click()
    Dim response As Integer
    If IsNull(Forms![frmSearch]!txtCode) And IsNull(Forms![frmSearch]!txtName) Then
        MsgBox "¡Ãسһé͹¢éÍÁÙÅ·Õèµéͧ¡Òäé¹ËÒ¤èÐ", vbCritical + vbOKCancel, "¤Óàµ×͹"
    ElseIf IsNull(Forms![frmSearch]!txtCode) Then
        response = basOrderby("", Trim(Forms![frmSearch]!txtName))

    ElseIf IsNull(Forms![frmSearch]!txtName) Then

        response = basOrderby(Trim(Forms![frmSearch]!txtCode), "")
Else
        response = basOrderby(Trim(Forms![frmSearch]!txtCode), Trim(Forms![frmSearch]!txtName))
    End If
    Me!lstSearch.SetFocus
End Sub

Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
    ShowRecord.Enabled = True
End Sub

Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
    If Not IsNull(lstSearch) Then
        ShowRecord_Click
    End If
End Sub

Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box

    DoCmd.OpenForm "frmSalespersonContact", , , _
        "[tblSalespersonContact.ipcard]=" & "'" & Me.lstSearch.Column(0) & "'"
    
    'Close the dialog box
    DoCmd.Close acForm, "frmSearch"
    
End Sub
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
'Cancel and close the form

    DoCmd.Close

Exit_Cancel_Click:
    Exit Sub

Err_Cancel_Click:
    MsgBox Err.Description
    Resume Exit_Cancel_Click
    
End Sub