Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by Support4John; 2012-08-04 at 14:24.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •