Welcome to the forum blues66!
The best advice I can give you regarding comboboxes is this...
1. Comboboxes are used so that you can enter/display a "description" type value but store the ID value (such as ProductID) in the table. Keep this rule in mind:
- Your form's recordsource should "JUST" be based upon the main data table (ie. don't link your relational tables in the recordsource of the form.)
- Comboboxes then let you "display" the corresponding description value of an ID field (using the rowsource query of the combobox which is based upon JUST your relational table that has ProductID and ProductDescription.) So for example, I have a combobox on a form where the controlsource of that field = ProductID (the value I actually want to store in the main table, but I want to "display/enter" the ProductDescription into the combobox.) Thus, the rowsource for my combobox is based on my relational "lookup" table ONLY which has all the ProductID's and ProductDescriptions. The 1st column of the rowsource query is the ProductID and the 2nd column of the rowsource query is ProductDescription. I set my column width for the combobox = 0";2" (ie. hide the 1st column (ProductID) and show the 2nd column (ProductDescription)).
Try to think of a combobox as a way to type in the description value but actually store an ID value. This eliminates the need to "link" your relational table into the RecordSource for the form (- the recordsource for the form should again, "JUST" be based on your data table and not linked with any relational tables.)
The thing I like to do when dealing with comboboxes is put in this code in the AfterUpdate event of a combobox (for example, ProductID) to make sure I'm retrieving/storing the correct value (given that the bound column is column 1 and the column widths = 0";2").
Private Sub ProductID_AfterUpdate()
msgbox "The value being stored is: " & me!ProductID
msgbox "The value being displayed is: " & me.ProductID.column(1)
Now you can use a combobox to populate another combobox on a form (but again, keep the rule that the form's recordsource is JUST based on my main data table and the comboboxe's rowsources are JUST based on the relational lookup tables.)
I'll add to this later....sorry...I've got to run.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)