Access Combo Box (2000)

Topic

New Reply

How do I limit the contents of one combo box based on the value selected in another

Reply | Quote

Replies

Use the Before Update event of the first combo to set the recordsource of the second combo.

Reply | Quote

I think it would be better in the after update event of combo1
If your selection in a numeric value :

Me!Combo2.RowSource = "Select Field1, Field2 from Table1 Where SelectionField = " & Me!Combo1
Me!Combo2.Requery

If your selection in a string

Me!Combo2.RowSource = "Select Field1, Field2 from Table1 Where SelectionField = '" & Me!Combo1 & "'"
Me!Combo2.Requery

Reply | Quote

I have a main form for a literary Source and a subform in which a user can enter many Themes and Subthemes for that Source. The subform is a continous form type. Based on the Theme, only certain SubThemes populate the second combo box.
Your code works well; I modified it to:

Private Sub ThemeID_LostFocus()

Me!SubThemeID.RowSource = “Select SubThemeID, ThemeID, SubTheme from tblSubTheme Where ThemeID = ” & Me!ThemeID
Me!SubThemeID.Requery

End Sub

But I have a problem that I am not sure how to address. This works well for the first record in the subform, but when a user tries to enter another subform record, the entry in SubThemeID for the previous records appear as blank. The proper value is saved to the table for every subform record, but the user sees a blank and that doesn’t inspire their confidence. I understand the Requery is doing this, but how can I get this to work and still display the previously entered values on the previous records?

TIA

Reply | Quote

This is typical behavior with comboboxes on a continuous form because there is really only one combobox , the one on the current record. The rest are illusory. When you change the rowsource of the combobox, you’re seeing the current rowsource displayed in all the records, and that rowsource may not include the values that exist in the other records.

There are several ways to deal with this, but I prefer to use a textbox bound to the same field as the combobox. I put the textbox on top of the combobox (you can also shrink the combobox down until only the dropdown arrow is visible) and lock the value in the textbox. I set the tabstop property of the combobox to No so that the user has to actually click the arrow to drop the combobox down. That way, the textbox will still display the actual value even if the textbox rowsource doesn’t match that particular record’s value.

Reply | Quote

Charlotte,

What do you enter in the textbox when the bound field of the combo is an ID and you want to display another column of the combo ?

Reply | Quote

In that case, I *don’t* bind them to the same field, I bind the textbox to a calculated field that returns the display value rather than the underlying value.

Reply | Quote

Thanks

Reply | Quote

This was precisely what I needed and it works great. I did need to create the calculated textbox to display the description, rather than the ID.

Between you and Francois, my issue is solved. My thanks to both of you.

Reply | Quote

No charge … this time.

Reply | Quote