Asteroid_Miniic
asked on
MS-Access error 2501 when saving a record
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.Activ ity_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:
SELECT tblActivityEquipXref.Activ ity_FK
, tblActivityEquipXref.Equip ment_FK
, tblActivityEquipXref.Build ingNumber
, tblEquipment.Equipment_PK
, tblEquipment.Model_FK
, tblEquipment.Manufacturer_ FK
, tblEquipment.SerialNumber
, tblEquipment.Comments
, tblEquipment.RoomNumber
, tblManufacturer.Manufactur erAbbrev
, tblModelNumber.EquipmentTy pe_FK
, tblModelNumber.ModelNumber
, tblEquipmentType.Equipment Type
, tblContainerLockConfig.Num berOperato r
, tblContainerLockConfig.Num berOfLocks
FROM tblEquipmentType INNER JOIN
((tblContainerLockConfig RIGHT JOIN
(tblModelNumber INNER JOIN
(tblManufacturer INNER JOIN tblEquipment ON tblManufacturer.Manufactur er_PK = tblEquipment.Manufacturer_ FK)
ON tblModelNumber.Model_PK = tblEquipment.Model_FK)
ON tblContainerLockConfig.Loc kConfig_FK = tblModelNumber.ContainerLo ckConfig_F K)
INNER JOIN tblActivityEquipXref ON tblEquipment.Equipment_PK = tblActivityEquipXref.Equip ment_FK)
ON tblEquipmentType.Equipment Type_PK = tblModelNumber.EquipmentTy pe_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.
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.Activ
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:
SELECT tblActivityEquipXref.Activ
, tblActivityEquipXref.Equip
, tblActivityEquipXref.Build
, tblEquipment.Equipment_PK
, tblEquipment.Model_FK
, tblEquipment.Manufacturer_
, tblEquipment.SerialNumber
, tblEquipment.Comments
, tblEquipment.RoomNumber
, tblManufacturer.Manufactur
, tblModelNumber.EquipmentTy
, tblModelNumber.ModelNumber
, tblEquipmentType.Equipment
, tblContainerLockConfig.Num
, tblContainerLockConfig.Num
FROM tblEquipmentType INNER JOIN
((tblContainerLockConfig RIGHT JOIN
(tblModelNumber INNER JOIN
(tblManufacturer INNER JOIN tblEquipment ON tblManufacturer.Manufactur
ON tblModelNumber.Model_PK = tblEquipment.Model_FK)
ON tblContainerLockConfig.Loc
INNER JOIN tblActivityEquipXref ON tblEquipment.Equipment_PK = tblActivityEquipXref.Equip
ON tblEquipmentType.Equipment
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gents, thank you for your comments. I'll simplify:
1) I can manually create records using my query qFrmEquipment, no problem
2) My form frmEquipment uses query qFrmEquipment as it's data source
3) If frmEquipment opens with existing equipment records (for a given Activty), I can create new equipment records
4) When no Equipment records exist (for a given Activity), frmEquipment loads empty. I enter data to create a new equipment record and when "DoCmd.RunCommand acCmdSaveRecord" executes (in cmdSave_Click), I get error 2501
Why are actions 3 and 4 different?
I apologize for lack of code, but the application exists on a secure private network and I'm not allowed to export code.
1) I can manually create records using my query qFrmEquipment, no problem
2) My form frmEquipment uses query qFrmEquipment as it's data source
3) If frmEquipment opens with existing equipment records (for a given Activty), I can create new equipment records
4) When no Equipment records exist (for a given Activity), frmEquipment loads empty. I enter data to create a new equipment record and when "DoCmd.RunCommand acCmdSaveRecord" executes (in cmdSave_Click), I get error 2501
Why are actions 3 and 4 different?
I apologize for lack of code, but the application exists on a secure private network and I'm not allowed to export code.
ASKER
Thank you PatHartman!
You were right, after double-checking my BeforeUpdate event, there was a line of code Canceling the event if action 4 conditions are met. It always seems the simplest answer is the solution. I must have stared at that code hundreds of times in the past 2 days and just didn't recognize it. The disadvantage of being the only IT guy in the dept (in the building)-- no one to bounce ideas off.
You were right, after double-checking my BeforeUpdate event, there was a line of code Canceling the event if action 4 conditions are met. It always seems the simplest answer is the solution. I must have stared at that code hundreds of times in the past 2 days and just didn't recognize it. The disadvantage of being the only IT guy in the dept (in the building)-- no one to bounce ideas off.
My programmers used to think I was Karnack the Magnificent (from the Johnny Carson show if you are to young to recognize the name). They would explain their symptoms and I would tell them what was wrong with the code. You have to make a lot of mistakes to get to the point of debugging from afar. My curse is remembering most of them:)
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Accept: PatHartman (https:#a40301039)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
MacroShadow
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Accept: PatHartman (https:#a40301039)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
MacroShadow
Experts-Exchange Cleanup Volunteer
Your question is somewhat rambling, and without a sample database is hard to follow. From what I can tell, you have a form you're opening in "Insert/Add mode", which I assume means you've set DataEntry = Yes, or something to that effect, and at some point you get a 2501 error. If you could distill this down to a concrete question or two, we might be able to provide better help.
That said, I think it's your query causing the troubles. In general, trying to add Parent and Child records at the same time (which, I believe, is what you're trying to do) is troublesome at best, and should generally be avoided. Access expects to find a valid Parent record before it can add a Child record, and using a JOIN query in that manner does not allow you (or Access) the luxury of checking before you add it. In general, a Form in Access should act on ONE and ONLY ONE entity at a time. Use Subforms if you must show Parent/Child relationships.