Combo Boxes on Sub Form clearing the wrong data when multiple entries found

[Casey Sanders]

OK, what I have going on is this....The basic setup of the DB is as follows:



tbl_Products............... = Product List that includes Product_Code, Description, Pricing, etc.
tbl_Models...................= Contains a list of Car Models sorted by Make
tbl_Makes...................= Contains a list of Car Makes
tbl_Products_By_Model..= Creates many-to-many relationship between Models & Products, allowing a single product code to be linked to (or available for) multiple models...and allows models to have multiple products

I have attached 2 images...one is the relationships, the other is the form I'm using

On the left hand side of the form, you see where we enter the product info, description, pricing, product code, etc. and on the right side(sub form), we select which models this product is available for (which is a make/model with a specific date range)

Their are two combo boxes on the subform: one for make, one for model... The Make Combo pulls its values from tbl_Makes and the Models Combo pulls its value from a query of tbl_Models

Make Combo----------------------------------------------------------------
Control Source: Make
Row Source: SELECT tbl_Makes.ID, tbl_Makes.Make FROM tbl_Makes;
Row Source Type:Table/Query
Bound Column: 1
Column Count: 2
Column Widths: 0";

(this indicates I am pulling 2 values from tbl_Makes (the primary key and the Make Name)...it is bound to column 1 (the primary key) but does not display it and only displays the make name in the pulldown (this is working properly)

Model Combo-----------------------------------------------------------------
Control Source: Make
Row Source: SELECT qry_Models.ID, qry_Models.Model FROM qry_Models;
Row Source Type:Table/Query
Bound Column: 1
Column Count: 2
Column Widths: 0";

(this is pretty much the same as the Make Combo how it pulls 2 fields, is bound to primary key and only displays the model name in the pull down)
..however the source is a query that has criteria that only displays models that correspond to the make selected in the Make Combo

qry_Models has 3 columns (1 that links it to Makes to filter by criteria and the other 2 we pull into the form "ID" (which is hidden by the 0" column width) and the one we use to display the model name "Model"
This is the code I have under criteria of the first column of qry_Models.

[Forms]![frm_Products_By_Model_Subform]![Make]

And the following is the code I have on the Make Combo box that requeries the list of Models when the Make is changed

Private Sub Make_AfterUpdate()Model.Requery
End Sub

...When this requeries is when all data on all rows of the "Model" column changes

|Yr Start| |Yr Stop| | Make | | Model | (For instance if I have these selected )
| 1985 | | 1990 | | Chevrolet | | Camaro |
| 1990 | | 1992 | | Chevrolet | | Corvette | (FYI it works fine as long as I keep the same Make selected and do not update the Make column)
| 1991 | | 1995 | | Ford | <----And try to add a different make
(when Ford is selected on row 3 ALL previous models (here, Camaro on row 1 and Corvette on row 2) will be erased )
(as an additional note, if you click the model combo on those rows, it would show the selections for the last selected )
(make...for instance, here these combos would now show Focus, Mustang, Thunderbird, etc. which fall under the Ford Model )

Any help would be much appreciated!

[Casey Sanders]

I was able to resolve part of this issue by switching from referencing a qry on the model combo box and using an SQL statement instead:

SELECT ID, Model FROM tbl_Models WHERE Make=[cbo_Make];

This allows me to select different Makes and their corresponding models properly....the problem I am having now is that once the correct model is selected, I go down to the next record, select the make and when I select the model, the model field on the previous record becomes blank... The data is still stored in the table and when I click on it again (or check the table) the selection is still there... The only thing I can think of is that I am pulling 2 columns in the Row Source (The autonumbered ID field and the field with the Model Name) and I have the column width set to 0"; which hides the first column (the autonumber)--which the combo box is bound to. If I unhide this and change "Limit to List" to "NO", then it shows ALL of the selected data on ALL rows, however it only shows the autonumber and not the model name... any suggestions??

[June7]

This is a known issue of using dependent (cascading) combobox with Lookup alias in Continuous or Datasheet view. There is no satisfactory solution to accommodate this arrangement.

[Casey Sanders]

I read of a similar issue in Access 2007 SP2....so this has carried over to Access 2010?

[June7]

Yes, I don't think it is going away. It's not a bug. It's just a fact of life associated with Lookups.