Unanswered: Using ComboBox to populate SQL in ListBox
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
' basError.LogError VBA.Err, VBA.Error$, "Form_frmSearch - txtInputSearch_Change()"
I've had a look through the Codebank example and cant find what I'm trying to do.
What I am trying is...
A form with a ComboBox (gives options for search)
TextBox (for User to input their search for)
List Box that on open loads with all data from the SQL (That works ok)
I then want to be able to choose a search option from the ComboBox and then type in my search into the TextBox, as I'm doing this I want to ListBox to filter my search so all I am left with is the records that match my input int the TextBox.
My problem is that once I select the option from the ComboBox, the selection disappears and when input my search criteria nothing happens to my list box
No that doesnt seem to do what I'm looking at doing,
I want to have a form which has a ComboBox, TextBox and a ListBox.
In the List box will be all the records I wish to search through, the ComboBox will hold the Search Options (Name, Surname etc) and the TextBox will be where the user inputs the criteria. As the TextBox is being populated I want the list box to filter through its records and leave me with all the records that match my criteria. From this I will then have the option to either double click on the record or single click and have a select button that will take that data and populate another from with the record.