Need help in storing data from SQL statement to a combobox

[CBG2112]

I would like to populate cboAssigned with the UserFullName value taken from a SELECT statement. I'm trying to populate the combo box so only assigned records are filtered on a form when a user opens the database. Do I run the SQL statement first or can i just run it directly to the code. I'm not sure how to code this though. Thanks.

      Me.cboAssigned = "SELECT tblUsers.UserFullName " & _
               "FROM tblUsers " & _
               "WHERE tblUsers.UserName='" & fOSUserName & "' "

 

[DCrake]

 Me.cboAssigned.[B]RowSource [/B]= "SELECT UserFullName " & _
               "FROM tblUsers " & _
               "WHERE UserName='" & fOSUserName & "'"

 

[CBG2112]

The solution you presented worked in storing the value to the combo box but I actually wanted a different result. I want the data taken from the SQL statement to be the value selected in the combo box. I hope this makes sense. Thanks for the help.

 

[DCrake]

So what you are sying is that you start with a list of people in the combo box rowsource and you want to preselect the person based on the FOUsername?

Me.Combo = FOUserName

 

[boblarson]

I want the data taken from the SQL statement to be the value selected in the combo box. I hope this makes sense.

Not really - why would you have a combo box and then set the value using code? If you are going to populate it then just use a Text box and use a DLookup to get the value. (by the way that is also how you would set the combo's value, but it seems odd to have a combo but set the value instead of picking the value).

 

[CBG2112]

The form has the functionality to filter the records through several combo boxes. One of them is cboAssigned which lists all the users for the database by full name. I use the function fOSUserName to check if the user has access to the database. I want to take the fOSUserName, individual's user name, and pre-select it from cboAssigned so it defaults to his/her records. So, I'm unable to use Me.Combo = FOUserName since the combo is based on the full name. I thought that i could accomplish this using SQL.

 

[boblarson]

This should work:

Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT tblUsers.UserFullName " & _
               "FROM tblUsers " & _
               "WHERE tblUsers.UserName='" & fOSUserName & "'")
Me.cboAssigned = rst!UserFullName
 
rst.Close
Set rst = Nothing

 

[CBG2112]

Thanks! It worked.