Using linked tables or look-up tables for cascading combo boxes in a form in Access 2013

[chelonidae]

Thanks, Steve. I'll take a look at it tomorrow and play around with it.

[chelonidae]

Steve - I had a chance to look at the zip you provided. Thank you for putting in so much time!
One thing I noticed is that the values on the form for fields like Port, Area, etc look like the values I want, but in all the tables and queries, it is the lookup value ID (autonumber, PK) that is stored. My hope for the lookups is that the user only has a few choices and these are populated in the data tables for sorting, summarizing, etc. later.
Example:
Area reads as the number in the sequence it was entered in luAreas (e.g. 3, 15) instead of the areas selected (e.g. Area1, Area2). Should I be using these lookups using the lookup tabs in Design View instead of linking them using PK, FK?

[ssanfu]

I'm not quite sure what you are asking......

One thing I noticed is that the values on the form for fields like Port, Area, etc look like the values I want, but in all the tables and queries, it is the lookup value ID (autonumber, PK) that is stored.

Tables are linked using PK & FK fields. I use autonumber type fields in my tables. These fields, PK & FK, have NO real world meaning. They are NEVER displayed in form or reports. This is the "normal" method to link tables in a RDBS.

My hope for the lookups is that the user only has a few choices and these are populated in the data tables for sorting, summarizing, etc. later.

This is also called cascading combo boxes (list boxes can work the same way). Open the table "luAreas". How many total areas are in the table? Close the table.
Now open the form "frmMain". Notice there are 2 records entered. Click on the first record. Now click on the "Area" combo box in the subform. How many areas are shown?
Click on the 2nd record in the main form. Now click on the "Area" combo box in the subform. How many areas are shown? Are they the same areas as when you clicked on the first record?

Click on the NEW record row in the main form. Do not enter anything in any field. Click on the "Area" combo box in the subform.
Now how many areas are shown?

Area reads as the number in the sequence it was entered in luAreas (e.g. 3, 15) instead of the areas selected (e.g. Area1, Area2). Should I be using these lookups using the lookup tabs in Design View instead of linking them using PK, FK?

Absolutely NOT!
IMHO, there are 3 things that should never be set up/used in a table.
1) Look up FIELDS (different from look up tables) See The evils of Lookup Fields
2) Multi value fields (MVFs are hard to use and hide the true structure of the dB)
3) Calculated fields ( calculations should be in queries)




I did not create forms to add ports and areas. There should be a form to add ports; on that form there should be a subform to add areas for that port.



It might help to see these
Autonumbers--What they are NOT and What They Are

Microsoft Access Tables: Primary Key Tips and Techniques <<--Read this many times

Use Autonumbers properly