Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    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?


    Code:
    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you going for a custom query builder-esque thing?
    Sounds like it - if so there's an example in the codebank that does stuff like this
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •