Unexpected Click event

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a Command Button on a form with the following on
click event:

Private Sub Command65_Click()
DoCmd.SetWarnings False
Me.FormResetDelete = 1
DoCmd.Save
DoCmd.OpenQuery "ArchiveCompletedFormsHistory"
DoCmd.OpenQuery "ArchiveClearFormsArchived"
DoCmd.Save
End Sub

The purpose is to have the current record marked for
archiving by inserting a 1 in the FormResetDelete field;
run the append query "ArchiveCompletedFormsHistory" to
copy the data to a history table; run the update
query "ArchiveClearFormsArchived" to reset most of the
fields on the form to null; and save the record.

The append and update queries run as expected. However;
the user has to click on the command button twice to
complete the task rather than just once. Further, after
clicking the command button twice and attempting to
navigate to the next record the following messages appears:

Write Conflict
This record has been changed by another user since you
started editing it. If you save the record, you will
overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at
the values the other user entered, and then paste your
changes back in if you decide to make changes.

Save Record Copy to Clipboard Drop Changes.

Choosing any one of the three options results in a
successful completing of the archive and reset objectives.

What changes to the code are needed to prevent the
necessity of clicking the command button twice and to
avoid the Write Conflict warning?
 
Rick said:
I have a Command Button on a form with the following on
click event:

Private Sub Command65_Click()
DoCmd.SetWarnings False
Me.FormResetDelete = 1
DoCmd.Save
DoCmd.OpenQuery "ArchiveCompletedFormsHistory"
DoCmd.OpenQuery "ArchiveClearFormsArchived"
DoCmd.Save
End Sub

The purpose is to have the current record marked for
archiving by inserting a 1 in the FormResetDelete field;
run the append query "ArchiveCompletedFormsHistory" to
copy the data to a history table; run the update
query "ArchiveClearFormsArchived" to reset most of the
fields on the form to null; and save the record.

The append and update queries run as expected. However;
the user has to click on the command button twice to
complete the task rather than just once. Further, after
clicking the command button twice and attempting to
navigate to the next record the following messages appears:

Write Conflict
This record has been changed by another user since you
started editing it. If you save the record, you will
overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at
the values the other user entered, and then paste your
changes back in if you decide to make changes.

Save Record Copy to Clipboard Drop Changes.

Choosing any one of the three options results in a
successful completing of the archive and reset objectives.

What changes to the code are needed to prevent the
necessity of clicking the command button twice and to
avoid the Write Conflict warning?
Click to expand...

I think you're mistaken in your use of the statement "DoCmd.Save". That
statement does *not* save the current record. It saves *design changes*
to the form. If your intention is to save the current record, replace
that statement with either

RunCommand acCmdSaveRecord

or

Me.Dirty = False

I notice that you issue "DoCmd.SetWarnings False", and I see no
correponding "DoCmd.SetWarnings True". Maybe you left it out of your
post by accident, but it's a very bad idea to turn warnings off and not
turn them back on again immediately. Incidentally, you may be able to
completely avoid turning them off by using

With CurrentDb
.Execute "ArchiveCompletedFormsHistory", dbFailOnError
.Execute "ArchiveClearFormsArchived", dbFailOnError
End With

instead.
 
Back
Top