Avoid Auto Save Of Record WHile Navigating

[Guest]

Access forms automatically save a record when the user navigates to the next record or closes the form. How can I prevent this behavior? I'd rather place a button on the form which explicits allows a user to save a record:
DoCmd.acRunCommand acCmdSaveRecord

This way, multiple users can be viewing the same record set with no locking issues.

Thanks...

 

[Ken Snell]

So long as the user isn't editing the record, there are no locking errors.
If you just want to let the user view and not edit records, set the form's
AllowEdits property to No.

--

Ken Snell
<MS ACCESS MVP>

Access forms automatically save a record when the user navigates to the

next record or closes the form. How can I prevent this behavior? I'd
rather place a button on the form which explicits allows a user to save a
record:

 

[Guest]

How can I prevent an Access form from automatically saving the record each time the user navigates to a new record?
How can I prevent the automatic saving of changes when the form closes?

 

[Ken Snell]

I conclude that the user is editing the record and you don't want to save
the changes? Only way to do that is to use an unbound form, and then you'll
need to write and run VBA code to do the saving of records yourself. Lots of
work.

If you set AllowEdits to No, there is no saving of records because nothing
was changed.

Perhaps I'm not understanding your setup. Could you give us some more info
about it?

--

Ken Snell
<MS ACCESS MVP>

How can I prevent an Access form from automatically saving the record each

time the user navigates to a new record?

 

[Annelie]

I was given this answer by Allen Browne in my post from 06/16/04 and it is
working very well for me.
It only kicks in if a form is changed not on new data. I was also looking
for an answer to the user accidentally changing a form.
Use the BeforeUpdate event of the *form* (not a control):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
If MsgBox("Save the change?", vbOkCancel) <> vbOk Then
Cancel = True
Me.Undo
End If
End If
End Sub

Annelie

 

[Ken Snell]

This will work to not save the changes. Note that, while the record is being
edited ("dirty"), the record will be locked and others will not be able to
make changes to it.

 

[Guest]

I have a classic situation with a multitude of users cycling through the same large record set. The front end is Access 2002 using DAO bound forms to ODBC links to SQL Server 2000. I've experimennted with an Access ADP front end, which uses OLDDB direct, and found the same behavior with forms.

I have created a RECORD_LOCK bit field and tried writing to it prior to loading a form but it never works reliably under actual load of 20 users. It is sluggish when people pull record sets of 1000.

Your solution may be correct. I could populate a local table which the form is bound to, then execute an UPDATE query whenever I want to send a record's info to the back end table.

An alternative is an unbound form loaded by an ADO recordset.

TG

 

[Ken Snell]

Ahhhhh.... I have not worked with SQL Server 2000 as the backend database,
so its recordlocking behavior may be very different from Jet's behavior
(which is the database on which I have my experience at this time). So, take
my comments with a pound of salt with respect to how they may apply.

You may want to start a new thread and point out the database structure,
including SQL Server 2000 as the backend, and someone with more experience
with it may be able to give you a better solution.

--

Ken Snell
<MS ACCESS MVP>

I have a classic situation with a multitude of users cycling through the

same large record set. The front end is Access 2002 using DAO bound forms
to ODBC links to SQL Server 2000. I've experimennted with an Access ADP
front end, which uses OLDDB direct, and found the same behavior with forms.

I have created a RECORD_LOCK bit field and tried writing to it prior to

loading a form but it never works reliably under actual load of 20 users.
It is sluggish when people pull record sets of 1000.

Your solution may be correct. I could populate a local table which the

form is bound to, then execute an UPDATE query whenever I want to send a
record's info to the back end table.

 

[Guest]

Thanks, Ken... I have a workaround which is theoretically perfect for the front end. I'll deal with locking issues after the UPDATE query.

TG