Update Combo Box Value

0
votes

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.

1
ms-accessvba
Have you tried me.mycombobox.value=rs.fields(0).value – Nathan_Sav
@Nathan_Sav yes, I get the same error sadly :/ – Ryan_W4588
Does MyComboBox have a multi-value field as its Control Source? – HansUp
@HansUp no, it is a single value. – Ryan_W4588
And the error occurs at the Me.MyComboBox = line, correct? In that case, what if you go into debug mode when the error happens and run this statement in the Immediate window? Debug.Print Me.MyComboBox.Value – HansUp

1 Answers

0
votes

use this

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

for collection

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