Results 1 to 8 of 8

Thread: SubForm problem

  1. #1
    Join Date
    Dec 2007
    Posts
    70

    Unanswered: SubForm problem

    Hi,

    I'm having an issue with a subform on a form (and if Pootle Flump is reading this, sorry it's me again )

    The forms are working fine and the data is entering the relivant tables. You enter details into the subform and move focus to any field in the main form, and thats fine. But as soon as you try to type into the main form the following error message appears (this happen only if you have enterd data into the subform first).

    Error msg:
    "The data has been changed.
    Another user edited this record and saved the changes before you attempted to save your changes.
    Re-edit the record."


    If you click OK and just carry on filling in the form all the data saves just fine! But I can't have this error message popping up confusing users.

    Also there are no other users using DB at mo as it is still in development, so no other 'user edited the record'!

    Any ideas?

    Cheers,
    OB1

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Are the form and subform bound to the same table?
    Paul

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Quote Originally Posted by OB1
    Error msg:
    "The data has been changed.
    Another user edited this record and saved the changes before you attempted to save your changes.
    Re-edit the record."


    If you click OK and just carry on filling in the form all the data saves just fine! But I can't have this error message popping up confusing users.
    I had this same issue and struggled with it for a while. Best solution (and only, that I could get to work) is in the afterupdate event of each updateable field, have the line:

    Code:
    DoCmd.RunCommand acCmdSaveRecord
    Some sites suggested requerying the subform, refreshing the main form, etc., but this is the best option i found. The others work, depending on the situation, but have other effects (such as resetting the subform to the first record in the recordset, etc).

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    this happen only if you have enterd data into the subform first
    Personally, I prevent users from entering data in subforms before the mainform.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Sometimes aesthetics can call for different situations. In my case, the main form and the subform were both using the same table as the control source (which was causing the problem). I ended up redesigning the main form on my project, but that isn't always an option.

  6. #6
    Join Date
    Dec 2007
    Posts
    70
    Hi,

    The form and subform are bound to seperate qrys which in turn are bound to several tables.

    I've tried what you suggested jmahaffie but do you mean adding the code to all the fields on just the subform or all the fields on both forms?

    I have attached the table relationships for the two qrys. one for subform and one for mainform if this is of any help?

    Cheers
    Attached Thumbnails Attached Thumbnails mainform.bmp   subform.bmp  

  7. #7
    Join Date
    Dec 2007
    Posts
    70
    Quote Originally Posted by jmahaffie
    I had this same issue and struggled with it for a while. Best solution (and only, that I could get to work) is in the afterupdate event of each updateable field, have the line:

    Code:
    DoCmd.RunCommand acCmdSaveRecord
    Some sites suggested requerying the subform, refreshing the main form, etc., but this is the best option i found. The others work, depending on the situation, but have other effects (such as resetting the subform to the first record in the recordset, etc).
    Thanx for your help jmahaffie, couldn't get the code to fix the problem but your hint to requery/refresh helped me find the solution.

    I needed to requery the subform in the 'on exit' event.

    Thanx again,

    OB1

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Getting the data is changed by another user error is sometimes difficult to troubleshoot. I uploaded a small example mdb in the code bank which demonstrates some ways you can get this error. I personally don't think doing an acCmdSaveRecord command after updating every field is the best approach. The key problem is that you've opened multiple recordsets based on the same table and confused MSAccess on which table you are trying to update. The best thing to do is to simplify your design so that you are only opening the same table only once and don't have that same table opening twice in any 2 or more forms/subforms. Mainforms are usually assocated with JUST the main data table and subform(s) are associated with JUST the relational table(s) (i.e. don't link the relational table in your recordset for the mainform and don't link the main data table in your recordset for the subform.) If you go by these rules and eliminate any duplicates of the same table loading in your mainforms/subforms, you can usually avoid this error.

    If you want to upload your mdb, we can take a look at it and probably find your problem fairly quickly.
    Last edited by pkstormy; 02-20-08 at 13:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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