vba Combo box returned item not displayed 1

[aco636]

Hi All
As always any help greatfully received.

New to working with forms, etc. I have a project that requires me to capture a few selections from the user before executing code.

I have filled a combo box OK. WHen you drop the box you see all the required values.
When you click on one of the values, I would expect the value clicked to be shown/replace the current value showm.

However, it doesnot. I can only set the value if I use a message box to return the selected value.

I guess I need to set the list index to the corresponding list item. Is this done with some sort of Global variable or Public Function ?

Private Sub cbxSalesMan_Change()
'How to ensure the combo box displays selected value. 
'I can only get it to work if I return it in a message box.

    Dim iCbIdx As Long
    iSalesIndex cbxSalesMan.ListIndex()
    MsgBox "Salesman = " & cbxSalesMan.Value
    
End Sub
Private Sub cbxSalesMan_DropButtonClick()

    On Error GoTo cbxSalesMan_Change_Err
    Dim cn As ADODB.Connection, rc As ADODB.Recordset
  Set cn = New ADODB.Connection
  Set rc = New ADODB.Recordset
  Dim strConn As String, strSQL As String
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=" & ThisWorkbook.Path & "\myDB.mdb;"
  cn.Open strConn
  Dim sql As String
          sql = "SELECT Description FROM Salesmen;"
            rc.Open sql, cn, 3, 3
            rc.MoveFirst
    With Me.cbxSalesMan
        .Clear
        Do
            .AddItem rc.Fields(0)
            rc.MoveNext
        Loop Until rc.EOF
     
     Dim i As Integer
        i = 0
        
       End With
    
    Dim strSalseman As String
    strSalesman = cbxSalesMan.Value
       

cbxSalesMan_Change_Exit:
    On Error Resume Next
    'rc.Close
    cn.Close
    Set rc = Nothing
    Set cn = Nothing
    Exit Sub
cbxSalesMan_Change_Err:
    MsgBox "Swine Thing" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume cbxSalesMan_Change_Exit
End Sub

 

[fumei]

Could you explain what the following is supposed to be doing?

    Dim iCbIdx As Long
    iSalesIndex cbxSalesMan.ListIndex()

Specifically, the syntax of iSalesIndex cbxSalesMan.ListIndex() seems wrong, never mind that iSalesIndex does not seem to be declared.

Gerry

 

[Andrzejek]

What's your Style of your cbxSalesMan?
If it is 0 - frmStyleDropDownCombo, I would change it to 2 - frmStyleDropDownList, and you can instead of cbxSalesMan_Change use cbxSalesMan_Click event.

Have fun.

---- Andy

 

[Andrzejek]

On the second thought....
Populate your combo box in UserForm_Initialize() event and everythig will work for you just fine in Change event:

Private Sub cbxSalesMan_Change()
    [green]'Insert your Chenge code here    [/green]
End Sub
Private Sub [blue]UserForm_Initialize()[/blue]

    On Error GoTo cbxSalesMan_Change_Err
    Dim cn As ADODB.Connection, rc As ADODB.Recordset
  Set cn = New ADODB.Connection
  Set rc = New ADODB.Recordset
  Dim strConn As String
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=" & ThisWorkbook.Path & "\myDB.mdb;"
  cn.Open strConn
  Dim sql As String
          sql = "SELECT Description FROM Salesmen;"
            rc.Open sql, cn, 3, 3
    With Me.cbxSalesMan
        .Clear
        Do
            .AddItem rc.Fields(0)
            rc.MoveNext
        Loop Until rc.EOF
    End With
    
cbxSalesMan_Change_Exit:
    On Error Resume Next
    'rc.Close
    cn.Close
    Set rc = Nothing
    Set cn = Nothing
    Exit Sub
cbxSalesMan_Change_Err:
    MsgBox "Swine Thing" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume cbxSalesMan_Change_Exit
End Sub

Have fun.

---- Andy

 

[combo]

As for your original code, the DropButtonClick event fires twice. You can test it with:
Private Sub cbxSalesMan_DropButtonClick()
Msgbox "DropButtonClick"
The second click clears selection.

To fill the list, you could either use the 'Initialize' event as Andy proposed in case of static list or, for dynamic list, have it disabled, enable it and update the list on demand. In both cases more clear code can be written when list changes are not linked with any combobox events.

combo

 

[aco636]

Thanks for all you rfeedback, I have been away for a few days and will pick this up later.
Regards to all ACO

 

[aco636]

Thanks for your advice - used form_initialise and as you suggested all OK. Thanks for your help.