ms access - cascading combo boxes with bound form

Question

I am at my wits end here and have tried every code combination imaginable ( and what I can download) to get those elusive cascading combo boxes to work. I have 6 of them and this is for a shipping database. User has to select REGION then COUNTRY and then PORT. That is both for origin and destination, so 6 in total. I can get this to work only under the following conditions; The Form is unbound, and have to use VBA/SQL to insert the selected values into the table. Once the values are in the table, it is the ID's not the actual value (example "Asia"). I really need the form bound to the shipments table and combo boxes Control Source set to that particular Field. I have a sinking feeling I am barking up the wrong tree and it cannot be done. Any suggestions here?

Answer 1

cmbRegions.RowSource = SELECT ID, Region FROM tblRegions

cmbCountry.RowSource = SELECT ID, Country FROM tblCountries WHERE RegionID = frmForm!cmbRegion

cmbPort.RowSource = SELECT ID, Port FROM tblPorts WHERE PortID = frmForm!cmbPort

In the AfterUpdate event of `cmbRegions':

cmbCountry.Requery

In the AfterUpdate event of `cmbCountry':

cmbPort.Requery