Error 3201: You cannot add or change a record because a related record is r

Topic

New Reply

Error 3201: You cannot add or change a record because a related record is required in the table tbl_RevTran_Master

I’m using Microsoft Access 2010 (14.0.6024.1000) SP1 MSO (14.0.6112.5000)
I’m using Microsoft Windows 7 Professional Service Pack 1

I have a Point_of_Sale (POS) system (like an Invoicing system) that is developed and maintained on a Access 2000 computer and full the system is distributed to Access 2000, 2002, 2003 and 2007 users and everything has been working flawlessly for several years.

I use and front end (C:App.mdb) and back end (F:App_Be.mdb) split database technique

Master/Detail Record are as follows:

tbl_RevTran_Master, lngRevTranID Primary Key
1 record for each transaction

tbl_RebTran_Detail, lngRevTranID Forign Key
1 or many record(s) for each transaction

Relationships on lngRevtranID
Checked Enforce Referential Integrity
Checked Cascade Update Related Fields
Checked Cascade Delete Related Records

My POS screen has a from Header
Detail section for the tbl_RevTran_Master
Subform for tbl_RevTran_Detail

1. When the form is opened the last transaction (Master and Details) that were entered appears on the screen

2. The form has a Add, Edit, Delete button

3. When the Add button is clicked

4. Detail section Form_BeforeInsert is fired

5. tbl_RevTran_Master AutoNumber lngRevTranID copied into tbl_RevTran_Detail lngRevTranID,

6. also copies date, user name, etc into the tbl_RevTran_Master record,

7. and tbl_RevTran_Master record is automatically saved (without code)

8. DoCmd.GoToRecord , , acNewRec (this clears the SubForm and allows the operator to enter detail records)

9. Detail line item records are entered as needed

The above description is how the system works OK on 2000, 2002, 2003 and 2007 systems

When operating on a 2010 system:

First time running the C:App.mdb under Access 2010, the App seems convert OK, and it compiles clean and the system opens OK

When I do step 9. Detail line item records are entered as needed, and start entering data, I get the following error message:

Error 3201: You cannot add or change a record because a related record is required in the table tbl_RevTran_Master

Bottom line: the tbl_RevTran_Master record is not being saved to disk the instant I move focus to the subform as described in step 7 above

Keep in mind the same App.mdb & App_Be.mdb system works on 2000, 2002, 2003 and 2007 systems

Please help

Thanks, John
11397335

Reply | Quote

Replies

Exactly when do you get the error message? As soon as you start entering data in a new detail record, or after you move off of a new detail record?

Reply | Quote

In the second field in tbl_RevTran_Detail I have:

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord ‘to save detail
End If

Also as a test I did the following:

If I just click Add and at step 6

if I press Esc Key once the detail record is cleared tbl_RevTran_Detail, lngRevTranID returns to AutoNumber which is OK

If I click Esc Key a second time tbl_RevTran_MasterlngRevtranID returns ti AutoNumber, on 2000, 2002, 2003 and 2007 systems this is saved and the numerical AN stays

Any other thoughts

Reply | Quote

Looks like the problem is solved for now by inserting If Me.Dirty Then Me.Dirty = False

Code has worked OK for the past 12 years without the command on 2000, 2002, 2003 and 2007 systems

Is this considered a MS Access bug or a programming error I got away with for 12 years?

Private Sub Add_Record_Click()

gloSetValue “FinalizeButtonClicked”, “Add”
gloSetValue “POSButtonClicked”, “Add”
Me.ButtonClicked = gloGetValue(“POSButtonClicked”)
mstrButtonClicked = “Add”
cboLookupSequence.SetFocus
FormState (“Unlocked”) ‘ Locked or Unlocked Me.strOwnerName.Locked = False
ControlState (“Edit”) ‘ Normal or Edit Me.strOwnerName.Enabled = True

strLookupCriteria = Null
DoCmd.GoToRecord , , acNewRec

Me.strProfileCode = GetPref(“Profile Code”) ‘ this forces autonumber in master record tbl_RevTran_Master

‘ the following command solved the problem
If Me.Dirty Then Me.Dirty = False

Me.frm_POS_Detail.SetFocus
Me.frm_POS_Detail.Form.strTranDesc.SetFocus
Me.frm_POS_Detail.Form.strTranDesc = Null
Me.frm_POS_Detail.Form.strTranCode2.SetFocus

End Sub

Reply | Quote