Hi,
I'm trying to create a txt box that allows searching a subform of client records by either firstname surname or national insurance number, so the user could type the first name, surname and national insurance number or any combination of them and it would filter the sub form appropriately. I succeeded to a degree with the below code but it's very crude code and users must specify the in a specific order, first name, surname, national insurance number (or any combination in that order) I wondered if anyone could provide a better solution or point me in the right direction?
Private Sub btnSearch_Click()
If IsNull(Me.txtSearch) Then
Else
Dim strTxtArray() As String
strTxtArray() = Split(Me.txtSearch)
Dim strtxt1 As String
strtxt1 = strTxtArray(0)
If UBound(strTxtArray) = 1 Then
Dim strtxt2 As String
strtxt2 = strTxtArray(1)
Else
End If
If UBound(strTxtArray) = 2 Then
Dim strtxt3 As String
strtxt3 = strTxtArray(2)
Else
End If
Select Case UBound(strTxtArray)
Case 0
Me.frmClientDatasheet.Form.RecordSource = "SELECT Client.[First Name], Client.Surname, Client.NationalInsuranceNumber, Client.DateOfBirth, Client.ID FROM Client WHERE (((Client.[First Name]) Like '*'+[Forms]![InitialReferral]![txtSearch]+'*') OR (Client.[Surname]) like '*' + [FORMS]![InitialReferral]![TxtSearch] + '*' OR (Client.[NationalInsuranceNumber]) like '*' + [FORMS]![InitialReferral]![TxtSearch] + '*' )"
Case 1
Me.frmClientDatasheet.Form.RecordSource = "SELECT Client.[First Name], Client.Surname, Client.NationalInsuranceNumber, Client.DateOfBirth, Client.ID FROM Client WHERE (((Client.[First Name]) Like '*'+" & "'" & strtxt1 & "'" & "+ '*') and (Client.[Surname]) like '*' +" & "'" & strtxt2 & "'" & " + '*') "
Case 2
Me.frmClientDatasheet.Form.RecordSource = "SELECT Client.[First Name], Client.Surname, Client.NationalInsuranceNumber, Client.DateOfBirth, Client.ID FROM Client WHERE (((Client.[First Name]) Like '*'+" & "'" & strtxt1 & "'" & "+ '*') and (Client.[Surname]) like '*' +" & "'" & strtxt2 & "'" & " + '*' And (Client.[NationalInsuranceNumber]) like '*' +" & "'" & strtxt3 & "'" & " + '*' )"
End Select
End If
End Sub