Populate Combo Box Based on Command Button

[DarrenBoyer]

This is a straitforward request but I can't seem to find an answer anywhere.

I'm modifying code from Desktop Developers Handbook:
In the book's example it shows how to use command buttons to control how a list box is populated.

My situation is a little different in that I need to have a combo box populated instead of a list box.

The object of this design is to give the user's the ability to search for existing customers. However, I have some customer's who are Organization's and some who are Individual's and these are two different fields.
Right now the combo box is populated and once I select a record it nicely fills in the rest of my form. [red] However, I don't have the normal Findfirst functionality of a normal combobox.[/red] As in the records search as keys are pressed.

Private Enum ShowType
    ShowOrganization = 1
    ShowIndividual = 2
End Enum
Public Sub cmdOrgSearch_Click()
    SetcomContents ShowOrganization
End Sub
Public Sub cmdIndivSearch_Click()
    SetcomContents ShowIndividual
End Sub

Next Function Works Nicely

Private Function SetcomContents(st As ShowType)
    ' Example function to show a method of filling lists using
    ' VBA.
    
    Dim intID As String
    Dim strField As String
    Dim strTable As String
    Dim strFill As String
    Dim strSQL As String
    
    txtSelected = Null
    Select Case st
        Case ShowOrganization
            strField = "OrgName"
            strTable = "Customers"
            txtSelected.ControlTipText = "Selected Organization"
        Case ShowIndividual
            strField = "IndivLastName"
            strTable = "Customers"
            txtSelected.ControlTipText = "Selected Individual"
        Case Else
            Exit Function
    End Select
    
    ' Build the appropriate SQL string.
       
    strSQL = "SELECT DISTINCTROW [" & strField & "] FROM " _
     & strTable & " WHERE [" & strField & "] Is Not Null ORDER BY [" & strField & "]"
     
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open _
     Source:=strSQL, _
     ActiveConnection:=CurrentProject.Connection, _
     CursorType:=adOpenStatic, _
     Options:=adCmdText
    
    cmbCustSearch.RowSourceType = "Table/Query"
    Set cmbCustSearch.Recordset = rst
    
    ' Don't explicitly close the recordset here.
    ' You want it to be open for the list box to use.
    Set rst = Nothing
End Function

Here is where I have problems

Private Sub cmbCustSearch_AfterUpdate()
     ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
        Me.FindFirst "[cmbCustSearch] = '" & Me![cmbCustSearch] & "'"
        
    txtSelected = cmbCustSearch
End Sub

 

[PHV]

Have you tried this ?
strSQL = "SELECT DISTINCTROW [" & strField & "] [highlight]AS AnyName[/highlight] FROM " _
& strTable & " WHERE [" & strField & "] Is Not Null ORDER BY [" & strField & "]"
with this:
Me.FindFirst "[highlight]AnyName[/highlight] = '" & Me![cmbCustSearch] & "'"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DarrenBoyer]

Your suggestion is very new to me. I have tried it and get the following compile error:
Method or data member not found

Dim rs As Object
    Dim AnyName As String
    
    Set rs = Me.Recordset.Clone
    Me.FindFirst "AnyName = '" & Me[highlight][!cmbCustSearch][/highlight] & "'"

This was copied from the combobox wizard and I thought it should work fine.
I did declare AnyName as a variable as I couldn't make sense of the code any other way. I would appreciate some input.

 

[PHV]

Replace this:
Me[!cmbCustSearch]
By this:
Me![cmbCustSearch]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DarrenBoyer]

Whoops!
The code is as you suggested it. I just used the highlight feature for the 1st time and got the bang in the wrong spot when i was editing my post.

 

[PHV]

My guess is that you have to reference Microsoft DAO 3.x Object Library and change the typing of rs like this:
Dim rs As DAO.Recordset

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DarrenBoyer]

Thanks PH,
Your last post got me thinking outside of the box. I searched past posts with that error code and fixed up the basic combobox coding.

The Following now works to find OrgName's - but only by using the scroll bar. The 'Findfirst' method doesn't seem to be working like a normal combobox would. I've tried multiple variations of the AnyName suggestion but can't seem to make any progress.

Private Sub cmbCustSearch_AfterUpdate()
    Dim rs As DAO.Recordset
    Dim strAnyName As String
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst " [OrgName] = '" & Me![cmbCustSearch] & "'"
    Me.Bookmark = rs.Bookmark
    txtSelected = cmbCustSearch
End Sub

 

[DarrenBoyer]

I've been doing some more reading and am now wondering if this is even an available approach with access.

In the book where I got the original code from nothing is mentioned about combo boxes but they do discuss incremental searches with list boxes. I tried their sample and incremental searches is what I need but they do that with a list box and text box hybrid. I'm wondering if combo boxes can be modified to accept the command button input and then behave like a regular combo box.

Does anyone know if what I want to do can be done?