CODEWITHSUNDEEP
ms-accessvba
Ryan_W4588
Ryan_W4588

Reputation: 668

Update Combo Box Value

In Access VBA I'm attempting to update a combo box's value based on a query.

Code:

Function updateComboBox()
    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Dim user as string

    user = environ("username")

    strSQL = "SELECT [Name] FROM myQuery WHERE [UserName] = '" & user & "'"

    Set rs = db.OpenRecordset(strSQL)
    If rs.RecordCount > 0 Then
        Me.MyComboBox = rs.Fields(0)
    End If
End Function

The error I get:

You can't assign a value to this object.

How can I change the current value of the combo box?

It may be important to note that the combo box is a list of values populated from a query. It happens to be the same query strSQL is using. It is important that users can still choose other names, but it defaults to their own.

EDIT:

I ended up doing the following work around to make this work:

I set the default property of MyComboBox to be =[myTextBox].

Form_OnOpen --> Update myTextBox's value based on query. Refresh form so combobox displays myTextBox's value.

Upvotes: 0

Views: 525

Answers (1)

King
King

Reputation: 179

use this

for single data Me.MyComboBox.AddItem(***)

for collection

   Me.MyComboBox.List = rs.Fields(0)

Upvotes: 0

Related Questions