1 combo box - 4 sources?

[tweetyksc]

In one of my databases, the user will edit/add information to 4 tables: Items, Users, Catalogs, and Depts.

On the main form, there is an option button for all four.
I would like to have ONE combo box that will display the information to edit depending on what is chosen in the option (instead of having a separate combo box next to each).

Ex: If "Items" is selected, the combo box will show ItemIDs; if "users" is selected, LastNames.

I know how to set up combo boxes individually, what I don't know is how to set it up so the option selected will affect what displays in the combo.

 

[dcx693]

When you change the value of the option chosen, you will need to tell the combo box what to display. You do that by changing the combo box's rowsource property.

For example, if the option group can have values equal to Items, Users, Catalogs, and Depts, have some code in the option group's After Update event like this:

Dim strDisplayField as String
Select Case Me.optGroup1
    Case is = "Users"
        strDisplayField="LastNames"
    Case is = "Items"
        strDisplayField="ItemIDs"
    [i]etc....[/i]
End Select

Me.combobox1.RowSource="SELECT " & strDisplayField & _
    " FROM " & Me.optGroup1 & ";"

You might want to consider creating a saved query for each individual table instead, and just use the query as the rowsources. You would then call them like this:

Dim strQuery as String
Select Case Me.optGroup1
    Case is = "Users"
        strQuery="qryUsers"
    Case is = "Items"
        strQuery="qryItems"
    [i]etc....[/i]
End Select

Me.combobox1.RowSource=strQuery