Starting form is Activity/Command and goes to Equipment form. Equipment form is filtered by the Activity, and has a subform for Locks (but for now lets ignore it).
I can create an Equipment record (and tblActivityEquipXref record) manually using just the Equipment form's underlying query (see below). tblEquipment and tblActivityEquipXref records are created at the same time (actually tblActivityEquipXref is created first as it is the parent). tblEquipment and tblActivityEquipXref are related but I do not enforce referential integrity. I set tblActivityEquipXref.Activity_FK in Form_BeforeInsert from a global variable (set in the Activity form). And yes, the global variable is valid when the Equipment form loads.
I can create a record using the "Add" button (uses DoCmd.RunCommand acCmdRecordsGoToNew) in the Equipment form after an Equpment record has been created for the same Activity. I tested this by creating a record using the form's query datasource, then loading the Equipment form for that same Activity and creating a second equipment record. Records are saved in the Equipment form using "DoCmd.RunCommand acCmdSaveRecord" (cmdSave_Click).
The problem occurs (I get Error 2501 executing DoCmd.RunCommand acCmdSaveRecord) when there are no Equipment records (for a given Activity) so I am entering equipment data for the first time for a given Activity and the Equipment form loads in insert/add mode.
It seems Access makes a distinction between a form loading in "Insert/Add" mode and using "DoCmd.RunCommand acCmdRecordsGoToNew". Why does my form behave differently in these two contexts? This is the crux of the issue and what I need an expert explain to me.
Query (qFrmEquipment) for the Equipment form is:
FROM tblEquipmentType INNER JOIN
((tblContainerLockConfig RIGHT JOIN
(tblModelNumber INNER JOIN
(tblManufacturer INNER JOIN tblEquipment ON tblManufacturer.Manufacturer_PK = tblEquipment.Manufacturer_FK)
ON tblModelNumber.Model_PK = tblEquipment.Model_FK)
ON tblContainerLockConfig.LockConfig_FK = tblModelNumber.ContainerLockConfig_FK)
INNER JOIN tblActivityEquipXref ON tblEquipment.Equipment_PK = tblActivityEquipXref.Equipment_FK)
ON tblEquipmentType.EquipmentType_PK = tblModelNumber.EquipmentType_FK;
I tried eliminating all tables in the query above, except for tblActivityEquipXref and tblEquipment, but still error 2501.
I tried moving Me.Activity_FK = g_lActivityPK to just before "DoCmd.RunCommand acCmdSaveRecord" (in cmdSave_Click), but still error 2501.
I tried changing the "DoCmd.RunCommand acCmdSaveRecord" statement to "Me.Dirty = False", it doesn't make a difference (other than a different error msg), the record still won't save.