Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Ocean, New Jersey, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving Records (XP)

    I want to save a new record when, and only when, I click on a cmd button. Access wants to save whenever I close the form (whether by clicking on a close button or the x at the upper right of the form.) Do I have to re-set the CommitOnClose property? How do you do that? I read about it, but can't find it on the property sheet for the form. In VBA? How? Are there any other ways to control this? Should I re-design so the form is unbound? This has got to be a pretty fundamental question; sorry to bother you. Thanks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Saving Records (XP)

    One option is to disable the x in the top right corner.
    Set up a boolean variable (blCloseform), and set its value to false in the onLoad event for the form.

    Use the following form unload event
    <pre>Private Sub Form_Unload(Cancel As Integer)
    If Not blCloseform Then
    Cancel = True
    End If
    End Sub
    </pre>


    Finally, have the command button set blcloseform to true.

    You will also need to remove the navigation buttons from the bottom of the form, because changes are also saved when moving to a different record.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Saving Records (XP)

    This seems counter-intuitive to people coming from the Word or Excel school, but experience has shown that people will invariably close a form without saving changes, and then complain that the system failed them when in point of fact it was user driven. For that reason, Access saves records whenever you move to another record or form, or close a form. The only way to be absolutely sure that a record isn't saved until you tell the system to make it an unbound form, and do all the work in VBA using DAO or ADO - and it's a lot more work!

    John has given you some ideas about how to approach it without resorting to that, but one issue is the use of subforms. If you use subforms - a major feature of Access - the record on the main form is automatically saved the moment the subform gets focus, so you would have to forego the use of subforms. Another approach would be to reset the dirty properties of controls and the forms as each control is updated, but that's lots of VBA too. We use bound forms in almost all applications using .mdb databases, with the exception of those where we need to do lots of validation checking involving comparing data values between controls. Hope this makes sense; if not, post back.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving Records (XP)

    According to the MSKB article 293334, CommitOnClose is not available in Access 2002 ADPs even though the on-line help says it is. Some properties and events were apparently removed from the final release but not from the help files. I didn't find any mention of it at all for MDBs, which doesn't really surprise me, since batch transactions are aimed at SQL Server, not Jet.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Ocean, New Jersey, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Records (XP)

    Wasn

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Records (XP)

    AutoNumber fields are meant to provide an easy way of generating unique ID numbers for records; they are not supposed to be meaningful in any way. If you delete a record, or cancel the creation of a new record, the ID for that field will not be reused, causing gaps in the range of ID's. This should not be a problem. If you *do* need a meaningful ID, you can use the method you described for another field. You can also maintain a separate table with just one record for this purpose.

    BTW: You can "reset" an AutoNumber field partly: after a Compact and Repair of the database (Tools/Database Utilities), the AutoNumber field will resume at the highest used number (in that table) + 1. Gaps between existing numbers caused by deleting records will *never* be reused.

  7. #7
    New Lounger
    Join Date
    Jan 2003
    Location
    Ocean, New Jersey, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Records (XP)

    Could you say a bit more about "maintaining a separate table"? Not sure I follow how that would work.
    Thanks for your help.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Saving Records (XP)

    You say : "Also, implemented John
    Attached Files Attached Files
    Regards
    John



  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Saving Records (XP)

    Have a look at the thread beginning with <!post=Automatically increment without Autonumber (Access 2000) ,215248>Automatically increment without Autonumber (Access 2000) <!/post>
    It talks about just this question.
    Regards
    John



  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving Records (XP)

    No one answered your question about posting, so I will. If you want to expand on the question or let us know it's been dealt with or just thank everyone for their help, posting it as a response to your original message is the preferred method. You did it just right. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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