Load combo Box values via SQL from other form

[Fattire]

I need to have a combo box display the value assigned to the primary key while still showing the remaining values assigned to the combo box so that if a user wants to they can choose a different value and hit save.
Combo box’s rowsource is normally a SQL statement.

varStatusID = rec1!StatusID

'Forms!frm_Edit!cbo_Status.RowSource = " SELECT StatusID, StatusName FROM lkp_Status WHERE StatusID = " & varStatusID

This code only loads one value into the combo box which is the current varStatusID assigned to the primary key - I need all 3 values to load, but I only want the status assigned to the primary key to appear in the combo box when the form loads.

Any help would be greatly appreciated.

 

[Fattire]

Figured it out... just needed ".Value" at the end, not ".Rowsource"

Forms!frm_Edit!cbo_Status.Value = varStatusID

.Value is matching up column(0) in the combo box which is the StatusID, and I have the column widths set to 0" and 2" so that only the second column appears which of course is column(1) which is the actual value I needed to see in there

 

[Fattire]

Actually, this is a better explanation - the combo box is first loaded with all the values from the table

    With cbo_Status
        .RowSource = "   SELECT StatusID, StatusName " & _
                     "     FROM lkp_Status " & _
                     " ORDER BY StatusName ASC "
        .ColumnCount = 2
        .Requery
        .SetFocus
    End With

Then after all the values are loaded, I can match up the recordset value (StatusID) to the value already loaded in the combo box (StatusID - column(0) and StatusName column(1)...)

varStatusID = rec1!StatusID

Forms!frm_Edit!cbo_Status.Value = varStatusID

it worked, thats all I care about at this point!