Parent/Child link breaks when form with subform is inserted into a third form

Question

I am working in an access database where I want to have a form with a sub form that has a tab control with multiple sub-sub forms. The top form links between primary_key1 and foreign_key1 on the sub form with the tab control, and the sub form links between primary_key2 and foreign_key2 on the sub-sub forms. Opened directly the sub form and all the sub-sub forms are linked together and function as intended. However, when inserted into the top form and opened access opens a popup asking for a value for primary_key2. Clearing the linking properties on all the sub-sub forms and testing each one, I determined that it is doing this for each of the sub forms.

As I am not sure where the problem is, I’ll provide as many details on the database structure as I can think of. Apologies for extraneous information. Below is the relationship view of the relevant tables. The Operations table is the record source for the top form, Flights for the sub form, the other four are the record sources for the sub-sub forms. The operations form and flight forms are formatted as single forms, the sub-sub forms are a mix of continuous and datasheet forms.

Relationships of relevant tables

Table	Field	Field Size
Operations	ID (primary key)	Long Integer
Operations	10 other fields	Date/time, Long Integer, Short Text, or Attachment
Flights	Flight_ID (primary key)	Long Integer
Flights	Operation_ID (foreign key)	Long Integer
Flights	57 other fields	Date/time, Long Integer, Short Text, Yes/No, Hyperlink, or Attachment
FlightCrewAssignments_JcnTbl	ID (primary key)	Long Integer
FlightCrewAssignments_JcnTbl	OperationID (foreign key)	Long Integer
FlightCrewAssignments_JcnTbl	FlightID (foreign key)	Long Integer
FlightCrewAssignments_JcnTbl	Crew_RoleID (foreign key)	Long Integer
FlightCrewAssignments_JcnTbl	CrewID (foreign key)	Long Integer

preemptive thanks for your assistance

Cameron

Nested forms should be possible up to I think 7 levels. Without more information about data structure hard to advise because issue appears to be design, not code. — June7
Added whatever I could think of about the data structure that might be relevant above, I can provide other information or the database itself if that would be better — Cameron Brinton
Not seeing a reason for sub-subforms to fail. You are using table not query for each RecordSource? Question is not really appropriate for SO as there is no code for analysis and probably require direct review of db. SO does not support attaching files - all info needed to analyze issue should be within the question. — June7
Yes most of them are using a table for the record source, with the exception of one of the sub-sub forms which is based on a query and goes through a bit of code upon the form loading to auto add some records. Have any recommendations for a more appropriate setting than SO for me to move this problem to? — Cameron Brinton
Then I can only guess issue is due to that code. There are numerous user forums that allow attaching files. Google. — June7

Cameron Brinton Cameron Brinton · Accepted Answer · 2021-01-14T21:00:31

The Tab Control was somehow corrupted either caused by or compounded by an OLE/Direct x error caused by controls which contained a special character in their name, namely: ˚ to indicate degrees of latitude and longitude. This problem further produced errors by creating hanging records without accurate foreign keys. All is working as intended after recreating the tab control, renaming the offending control names, and reformatting relationships to enforce structural integrity.

The largest mystery to me remains that the errors only surfaced when the form with the tab control was opened in another form and not when opened directly. However as it is working I am content to leave this sleeping dog lie.

Parent/Child link breaks when form with subform is inserted into a third form

1 Answers