I have a ComboBox which has 2 columns (actual Field Names & Names I want to show as drop down), the actual field names are column1 and show as 0cm width so they arent seen by the user. I then have a TextBox so that the user can input their own search criteria. I am trying to get my search data to auto adjust the ListSearch when the user is inputting their search.

This is my code that I have, I have 2 issues.
1 when I select my search on in the ComboBox and then click on the TextBox it clears the ComboBox Values.
2 When the TextBox has data entered it doesnt search the ListBox

How can I get these to work?


Private Sub txtInputSearch_Change()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
'**************************************
' On Error GoTo Err
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=PODIATRY LIVE;Integrated Security=SSPI;"
If Not IsNull(cboSearchOn) Then
If Not IsNull(Me.txtInputSearch.Text) Then
sQRY = _
"SELECT " & vbCrLf & _
"dbo.V_PAT_NHS.Patnt_RefNo_NHS_Identifier, " & vbCrLf & _
"dbo.V_PAT_NHS.Forename, " & vbCrLf & _
"dbo.V_PAT_NHS.Surname " & vbCrLf & _
"FROM dbo.V_PAT_NHS " & vbCrLf & _
"WHERE dbo.V_PAT_NHS." + Me.cboSearchOn + " LIKE '%" & Me.txtInputSearch.Text & "%' " & vbCrLf & _
"ORDER BY dbo.V_PAT_NHS.Patnt_RefNo_NHS_Identifier "
Me.cboSearchOn.RowSource = sQRY
Me.lstSearch.RowSource = sQRY
End If
Else
Me.cboSearchOn.SetFocus
Me.cboSearchOn.Dropdown
End If
'Err:
' basError.LogError VBA.Err, VBA.Error$, "Form_frmSearch - txtInputSearch_Change()"
End Sub