Saving Records (XP)

**newbee** · 2003-01-20 19:56

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.

**johnhutchison** · 2003-01-20 20:46

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.

**WendellB** · 2003-01-20 21:10

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.

**charlotte** · 2003-01-21 04:01

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.

**newbee** · 2003-01-23 15:25

Wasn

**HansV** · 2003-01-23 15:46

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.

**newbee** · 2003-01-23 17:06

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

**johnhutchison** · 2003-01-23 21:40

You say : "Also, implemented John

**johnhutchison** · 2003-01-23 21:45

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.

**charlotte** · 2003-01-24 02:36

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>

Thread: Saving Records (XP)

Thread Tools