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 Fname, [strSalespersonID]"
        strSQL = strSQL & " FROM Fname "
        strSQL = strSQL & " WHERE Fname LIKE " & "'*" & strKey1 & "*'" & " OR Model LIKE " & "'*" & strKey2 & "*'"
        strSQL = strSQL & " ORDER BY Fname;"
    ElseIf (Len(strKey1) > 0 And Len(strKey2) <= 0) Then
        strSQL = "SELECT Fname, [Fname]"
        strSQL = strSQL & " FROM tblSalespersonContact "
        strSQL = strSQL & " WHERE Fname LIKE " & "'*" & strKey1 & "*'"
        strSQL = strSQL & " ORDER BY Fname ;"
    ElseIf (Len(strKey1) <= 0 And Len(strKey2) > 0) Then
        strSQL = "SELECT Fname, [Fname] "
        strSQL = strSQL & " FROM tblSalespersonContact"
        strSQL = strSQL & " WHERE Model LIKE " & "'*" & strKey2 & "*'"
        strSQL = strSQL & " ORDER BY Fname;"
    End If
    
    Me!lstSearch.RowSource = strSQL
    Me!lstSearch.Requery
End Function

Private Sub cmdSearch_Click()
    Dim response As Integer
    If IsNull(Forms![frmSearchName]!txtCode) And IsNull(Forms![frmSearchName]!txtName) Then
        MsgBox "กรุณาป้อนข้อมูลที่ต้องการค้นหาค่ะ", vbCritical + vbOKCancel, "คำเตือน"
    ElseIf IsNull(Forms![frmSearchName]!txtCode) Then
        response = basOrderby("", Trim(Forms![frmSearchName]!txtName))

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

        response = basOrderby(Trim(Forms![frmSearchName]!txtCode), "")

    End If
    Me!lstSearch.SetFocus
    
      
    
End Sub

Private Sub Form_Open(Cancel As Integer)
txtCode.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.Fname]=" & "'" & Me.lstSearch.Column(0) & "'"
    
    'Close the dialog box
    DoCmd.Close acForm, "frmSearchName"
    
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