Bound Cascading Combo problems

[Sgt Bilkp]

Hi,

I am having some problems with cascading combo boxes. I have followed the good advice on here, and managed to learn how to make cascading combo boxes, however i cannot make them bound to each record.

I have two tables

tblRegions - with ID, Region, Location fields
tblDatamain - with cmbRegion and cmbLocation

Any advice or guidance that points me in the right direction?

 

[CraigDolphin]

To bind a control to a field in a table then your form must use the approrpiate table as its recordsource (or a query of that table) and then you choose the approrpiate field in the control source property for the combo.

Remember that combo stores a value which may be different than what you 'see' (check if the column width for the first colum is set to 0). You need to ensure that your combo is bound to the field that equates to the value being stored (not displayed).

If your form is based on a query, you will not be able to edit the values if the query itself is non-updateable. Queries can become non-updateable under certain situations where the db might have trouble trying to figure out which exact record needs to be changed. For example, groups/total queries aggregate data and make the query non-updatable because the db does not know exaclty which of the individual records needs to be modified.

 

[Sgt Bilkp]

I think i am with you on that...

Can i try and explain my example. I have a table called tblData (the main table). tblData contains fields such as ID, Name, DOB, Address etc and then one field for Region and one for Location. Region and Location use a lookup in the table to another table called tblAreas. tblAreas contains the data for Regions (North, East, South, West) and so on with respective Regions.

In the form, frmMain, there are the requisite fields for names etc and two combo boxes called cmbRegion and cmbLocation.

When i run a query builder on the combo boxes and input the data, i can get them to both work as cascading combo boxes fine. When i try to link the Control Source of the Combo boxes tot he field in tblData, it all goes wrong.

 

[CraigDolphin]

Chances are that the table lookups in the fields are fouling you up. There's a lot of strong opinions regarding table-level lookups and they're almost universally against their usage. Try removing the table lookups and re-doing your combos on the form.

Alternatively, post a sample db (remove any sensitive data, compact and repair, then zip your db and attach it to the post) and I'll have a look at it.