Form/record error: "DB engine cannot find a record in table... "

[skydivetom]

Hello Experts:

I need some assistance with identifying underlying cause as to why I cannot create/save a record via a sub form.

Allow me to provide some background first:
- Attached are 2 DBs: "01 Old DB" (aka "Old") and "02 New DB" (aka "New").
- "Old" was created a while ago and -- although it works -- the forms are antiquated and required an 'overhaul'.
- "New" contains updated forms and a more friendly naming convention of, e.g., table names; however, I have run into an issue NOT allowing me to create/save new records in the sub form.
- Specifically, in "New", the error is: "Access DB engine cannot find a record in table T112...". (see attached PNG).

********** Begin "Old" **********

First things first... let's start by reviewing the data entry process in database "Old". By default, form "frmFlights" opens up:

On the main form, do the following:
1. Click on "New Record" (e.g., record #3) from the record navigator.
2. A 'travel date' is automatically picked. Change date if desired.
3. For testing only, leave default value "Pleasure" in the option group.
4. From drop-down 'Geography", pick any value (testing only). Picking a value from the drop-down activates the sub form.

Next, on the sub form, do the following:
a. In the 2nd field "Airport - City", select any of the 5 available (sample) airports.
b. Based on a., all fields (i.e., 'state' through 'country' will be automatically be added via sub form's routine/function).
c. Lastly, select a (any) date from sub form's date picker ('Dates').
d. Close main form and the record is automatically entered across the 4 tables (incl. junctions/join tables).

********** End "Old" **********


********** Begin "New" **********

Now, let's review database "New". Again, the interface/forms are (in my view) more appealing but unfortunately, I can create/save a record in the sub form. Again, let's review the process:

On the main form, do the following:
1. Click on the GREEN "Create New" command button (top of form... right to the "Go to" drop-down.
2. Pick a 'travel date'.


3. For now, 'travel purpose' has default value "Business" (that's ok and will be changed later on).
4. Select value "Trans-Atlantic" from 'Geography' drop-down.

... so far so good.

But now the problem is encountered in the sub form.
a. Attempt to select any value from drop-down "Airport". Notice, auto-number field "Flt-Leg ID" does NOT receive any value ("Old" does immediate create such value).
b. Other fields 'State' through 'Country" (just like in "Old") should be auto-populated but it doesn't right now. I don't think that's the issue though.
c. Pick a date from field "Flt-Leg Date".

Now, just having selected, e.g., "Hamburg" updates the "Last Modified" field (admin field only), but again there's no value for the required "Flt-Leg ID"... which I think is the underlying cause.

********** End "New" **********

Without a major redesign of the entity relationships, does anyone know as to why the modified process ("new" vs. "old") doesn't work in database "New"? Naturally, the field names have been updated throughout.

If yes, what needs to be modified so that I can add any number of flight legs for any "parent flight trip"? Ideally, pls provide a fixed form/sub form that would alleviate the data entry issue.

Thank you for your assistance in advance.

[skydivetom]

Found the error... I chose "Airport" from the table T111_AIRPORTS vs. from T112_FLIGHT_LEGS.

That solved the issue.