Cascading combo box storing wrong value in table

[RolandC]

I have 3 cascading combo boxes. Make, Model and Trim. For them to work correctly it must be bound to column 1 (MakeID, ModelID and TrimID) and it will display the correct information on the form but it saves the ID# in the table.
I need it to save to the table as it appears on the form.

[Gicu]

That is how it should be, you should always store the ID and not the description (that can be changed\updated without impacting the value of the ID and therefore data integrity). When you need to display the description and not the ID (in a report for example) use a combo like you have in the form or bring the lookup table in the record source and grab it from there.

Cheers,

[ranman256]

make sure the combo has the correct BOUND COLUMN of the column value you want to use.

[Welshgasman]

I have 3 cascading combo boxes. Make, Model and Trim. For them to work correctly it must be bound to column 1 (MakeID, ModelID and TrimID) and it will display the correct information on the form but it saves the ID# in the table.
I need it to save to the table as it appears on the form.

Wrong. You should always save the IDs.

[RolandC]

Thank you for responding,
The table (will call it Purchased Vehicles) that contains the data has a primary ID. The MakeID, ModelID and TrimID have their own tables and primary key (ID). That's what references the Models with the Makes.
Example: Make = Chevrolet which is #1 in the ID Column.

MakeID	Make
1	Chevrolet
2	Ford

ModelID	MakeID	Model
1	1	Camaro
2	1	Caprice

TrimID	ModelID	Trim
74	179	Convertible
76	179	ZL1

When I run Reports I need it to populate Make, Model and Trim (by name) into the appropriate fields in the report. I can send a sample file if that helps.

[Micron]

In a query you equal join MakeID pk (primary key) to MakeID fk (foreign key) and ModelID pk to ModelID fk. You drag the Make, Model and Trim fields into the query. The query returns "Ford" not 2. The report recordsource is that query. You usually don't need the ID fields in the query but it doesn't hurt to have them. If you use the report wizard to create the report they will end up there but would be pretty much useless much of the time. The exception would be when you need to refer to the pk field(s) in order to attain unique references.

[Welshgasman]

You just bring those descriptive fields into the query and use in report. The key is the links to all the id fields.

[RolandC]

Thank You for the responses,
So far I think I have tried all the suggestions at least once or twice over the last couple days. I still think it's probably a simple fix that I'm overlooking. I do know that if I change the "Bound Column" to anything other than Col.1 the cascading drop down doesn't function. I will get a sample file posted. Trying to get a good working test file that is 500kb.

[davegri]

I will get a sample file posted. Trying to get a good working test file that is 500kb.

Right click on the .accdb file and compress to zip file. You'll be surprised how small the .zip is. Post that.

[Micron]

You copy db, compact & repair then zip it and post that.

[June7]

Most basic Access file is almost never below 500kb anymore. Up to 2MB zip attachment allowed.

[RolandC]

Sample file attached: Maybe! Sample_CascadingComboBoxes.zip

[June7]

Why not use Autonumber for Primary key field in tblMake and tblModel? Then Foreign key fields would be Long Integer. In any case, don't think should use Double for either.

Fields Make, Model, Trim in Vehicle Data need to be Number then in VBA don't set comboboxes to empty string - use Null. Set these 3 fields as a unique compound index to prevent duplicate sets.

Instead of referencing query object that has parameter referencing form, I would have SQL statement in RowSource. This has added benefit of allowing form to easily be used as a subform.

SELECT ModelID, Model FROM tbl_Model WHERE MakeID = [cboMake];

Primary key in tbl_Trim is named just ID.
SELECT ID, Trim FROM tbl_Trim WHERE ModelID = [cboModel];

Not every model is in Trim table so not all will show Trim options.

Otherwise, combobox settings seem correct.

Also need to requery comboboxes in form Current event so that they refresh when navigating records.
Review https://stackoverflow.com/questions/...t-show-its-val


If you really, really want to save text instead of ID's in Vehicle Data, a restructure of comboboxes will be required. ID fields could not be BoundColumn. Using ID fields to filter query will be more complicated. Filtering on indexed numeric ID is more efficient but could use text value as criteria.

Suggest not using exact same name for multiple fields. Don't use spaces in naming convention.