ComboxBox Does not Save Value

[bharatvly]

Hello Folks,

I have below code and it works absolutely perfect. However, I have one minor issue and I've been racking my head on it to fix it. What below code does is, it retrieves the ComboBox list from Access database. However, when I select any item from the list, the drop down closes and It doesn't display the selection in ComboBox.Value. You know what I mean?

When I open the dropdown it again retrieves the list which is right but doesn't save the selection in Combobox.Value. Please help!

Private Sub ComboBox4_DropButt*******()

cnn.Open "Provider = Microsoft.ACE.oledb.12.0; data source= " & ThisWorkbook.Path & "\" & "Database_SDET.accdb" & ";Jet OLEDB:Database Password=L@stMinute14;"
Qry = "SELECT [Full_Name] FROM Login_Details"
rst.Open Qry, cnn, adOpenKeyset, adLockOptimistic

rst.MoveFirst
With Admin_Page.ComboBox4
.Clear
Do
.AddItem rst![Full_Name]
rst.MoveNext
Loop Until rst.EOF
End With

rst.Close
cnn.Close

End Sub

 

[Andrew Poulsom]

I think you will find that the event fires again when the user makes a selection. Does this work for you?

Dim Dropped As Boolean

Private Sub ComboBox4_DropButt*******()
    Dropped = Not Dropped
    If Dropped = True Then
        cnn.Open "Provider = Microsoft.ACE.oledb.12.0; data source= " & ThisWorkbook.Path & "\" & "Database_SDET.accdb" & ";Jet OLEDB:Database Password=L@stMinute14;"
        Qry = "SELECT [Full_Name] FROM Login_Details"
        rst.Open Qry, cnn, adOpenKeyset, adLockOptimistic
    
        rst.MoveFirst
        With Admin_Page.ComboBox4
            .Clear
            Do
                .AddItem rst![Full_Name]
                rst.MoveNext
            Loop Until rst.EOF
        End With
    
        rst.Close
        cnn.Close
    End If
End Sub

 

[bharatvly]

Nope! It isn't working. I make the selection from the dropdown but it does not save the selection in Combobox's value!

 

[bharatvly]

I Figured. I add the code to UserForm_Initilialize() instead of DropDownButt******* event.

 

[Andrew Poulsom]

Nope! It isn't working. I make the selection from the dropdown but it does not save the selection in Combobox's value!

The code I posted worked when I tested it. The event occurs whenever the drop-down list appears or disappears. The Boolean variable should prevent the latter.