saving a record

[Garret]

Hello, I have a form where there are two main ways to save. The first
is doing anything to move off of the current record, so that Access
wants to save it anyway, and its Before & After update triggers. Here
I have this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'if a record has been changed
If Form.Dirty = True Then
'prompt the user to save
ans = MsgBox("You have changed a record. Do you want to save?",
vbYesNoCancel + vbQuestion, "Save Record?")
If ans = 7 Then 'if user selects 'No'
Me.Undo 'undo the changes
ElseIf ans = 2 Then 'if user selects
'Cancel'
Cancel = True 'don't undo, don't save
End If
End If
End Sub

I also have the second way, a command button that uses the default
code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

Whenever the command button is pressed, the BeforeUpdate action is
triggered first anyway, and the messagebox I created pops up. The
problem is, when I press Cancel, it says "DoMenuItem action canceled"
in a popup box. When I close the form, if the record was not saved and
the message box pops up, if the user presses cancel, then an error
message says "You can't save this record at this time...close database
anyway?"
How can I prevent the "Domenuitem action canceled" box from coming up,
and how can I make the close action become canceled if the user presses
cancel to save?

 

[Allen Browne]

Yes, Access will fire Form_BeforeUpdate before the record is saved.

You can use your error handler to ignore the message that the macro did not
complete. Temporarily add a quote mark to the first line, i.e.:
'On Error GoTo Err_cmdSave_Click
Then when it fails, write down the error number.

Then remove the quote from the line above, and in the error handler part,
avoid that number, e.g.:

Err_cmdSave_Click:
If Err.Number <> xxxx Then
MsgBox Err.Description
Resume Exit_cmdSave_Click
End If
End Sub

 

[Garret]

It was not an error though that I was receiving (there was no error #).
If I change a record and then try to move to a different record, the
msgbox comes up and if I press cancel, then it does not move to another
record. If I press the actual save button, and then press cancel, I
get the message "DoMenuItem action was canceled", which is a useless
message that the user does not need to see, and they might be confused
if they do.
The other problem is, if the form is dirty, then if I try to close the
form and the message box comes up and I press cancel, I get the YesNo +
exclamation box: You can't save this record at this time
(Database Name) may have encountered an error while trying to save a
record.
If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway?
What I want to happen is that if the user presses cancel, then it will
just go back to the form as if the user never clicked the form close
button (as it does in most Windows applications I believe).

 

[Garret]

-

 

[Allen Browne]

Instead of the DoMenuItem, try either of these:
If Me.Dirty Then Me.Dirty = False
RunCommand acCmdSaveRecord
Either one should produce a trappable error.

To avoid the warning message, have your cancel button undo the record before
you close the form:
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name

 

[Garret]

Instead of the DoMenuItem, try either of these:
If Me.Dirty Then Me.Dirty = False
RunCommand acCmdSaveRecord
Either one should produce a trappable error.

If I do this, I get the same error except instead of "DoMenuItem action
was canceled" I get "RunCommand action was canceled"

To avoid the warning message, have your cancel button undo the record before
you close the form:
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name

Having this code makes it so if the user presses 'Cancel', its the same
as if the user presses 'No'. If I'm using MSWord (or anything really)
and I click the X in the top right, it asks if I want to save first.
If Yes, it saves and closes. If No, it doesn't save and closes, if
Cancel, it doesn't save and doesn't close. I want the Cancel to work
the same way on mine. Thanks for your help.

 

[Allen Browne]

Because Access saves the record by default, I'm not clear what your Cancel
button would do.

If it leaves the record dirty and does not close the form, that would be the
same as not pressing anything?

 

[Garret]

Because Access saves the record by default, I'm not clear what your Cancel
button would do.
If it leaves the record dirty and does not close the form, that would be the
same as not pressing anything?

Pretty much, this would happen only if the close button is pressed,
then the user goes "Oh wait, I still need to change 1 thing on this
record", so he presses cancel, changes something, then closes out and
can press Yes or No. What if the user made 10 changes, but had to add
that last 11th change here? It's easier this way than closing out,
having to reopen the form, refinding the record he was on, and
re-entering all his changes again. (or if he pressed Yes he wouldn't
have to re-enter everything, but theres still a lot of extra work).

Are there any other ways to save so that I don't run into the
RunCommand or DoMenuItem message boxes? If not, I might just make a
YesNo box appear instead (although if the user doesn't use the command
button to save, then there is no problem, AKA: he moves to a different
record and triggers the before update code)

 

[Allen Browne]

Okay, I think I'm losing sight of the intended goal here.

Form_BeforeUpdate is the best place for validation.

Form.Undo undoes the form when you want to lose the user's changes.

Me.Dirty = False forces the save, firing Form_BeforeUpdate first.

That's all I ever do.

 

[Garret]

Okay, I think I'm losing sight of the intended goal here.

Ok, to sum it up again, this is my structure:
I have this code on the BeforeUpdate event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = False Then
'if a record has been changed
If Form.Dirty = True Then
'prompt the user to save
ans = MsgBox("You have changed a record. Do you want to
save?", vbYesNoCancel + vbQuestion, "Save Record?")
If ans = 7 Then 'if user selects
'No'
Me.Undo 'undo the changes
ElseIf ans = 2 Then 'if user selects
'Cancel'
Cancel = True 'don't undo, don't
save
End If
End If
End If
End Sub

This code will trigger when the form is dirty (changes have been made
to a record) and the user tries to move off the current record (either
by moving to another record or closing the form (only ways I know of)).
When the User tries to go to a different record, this code works just
fine: 'Yes' saves the record and moves the other record, 'No' doesn't
save the record and moves to the other record, and 'Cancel' doesn't
save the record and doesn't move to another record. The two problems
I'm getting are these:

1) If the User closes the form while the form is dirty, the
BeforeUpdate triggers, and they get the msgbox. If they press Cancel,
what I want is for the same thing to happen as if they pressed Cancel
when they were moving to another record : Doesn't save, but keeps the
form open and the record current as if nothing ever happened. But
instead, I get the "Cannot save this record at this time...Do you want
to close the database now?" error. Pressing 'No' here, in effect, has
the same result that I want it to have since it just goes back to the
form as if nothing happened.

2)I have a command button that saves the record, since it makes more
sense logically to save with a button than to have to close the form or
move to another record to trigger the save event. Here is where I just
want the record to be saved, no questions asked.

Thanks for all your help so far Allen, hoping you can still help me =).

 

[Allen Browne]

Thanks for the summary.

So the issue is with the message that Access pops up after Form_BeforeUpdate
is processed if that was triggered by closing the form?

You can trap that message in the Error event of the Form, and ultimately you
can block the closure of the form by cancelling its Unload event.

However, my tests with the interaction of these events has not produced a
better set of messages than the built-in ones. You end up with either too
many messages (letting them know the form close is not permitted at
present), or too few messages (where they can't get out and don't understand
why). Either way, you run the danger of a frustrated user just crashing out
of Access, and therefore corrupting the database.

Hopefully that's enough of a lead to give you some options to try, even if
it's not a good outcome for what you had in mind.

 

[Garret]

Ok, thanks for your help. I think the key to solving this problem lies
within knowing the order that events occur. BeforeUpdate, Error,
Close, Unload, etc. I'm not sure the order that these fire off, but
I'm heading to look it up now.

I'm thinking some kind of flag variables can be used depending on what
the user does to run the code a little differently depending if they
click the save button, move to a different record, or close out.
For the cmdSave button at least, I can set up a flag when clicked to
skip over all the checking in the BeforeUpdate event. So it would look
something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If SaveFlag = 0 Then
If Me.NewRecord = False Then
'if a record has been changed
If Form.Dirty = True Then
'prompt the user to save
ans = MsgBox("You have changed a record. Do you want to
save?", vbYesNoCancel + vbQuestion, "Save Record?")
If ans = 7 Then 'if user
selects 'No'
Me.Undo 'undo the
changes
ElseIf ans = 2 Then 'if user
selects 'Cancel'
Cancel = True 'don't undo,
don't save
End If
End If
End If
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "The Record was Saved", vbOKOnly, "Record Saved"
SaveFlag = 0
End If
End Sub

Private Sub cmdSave_Click()
SaveFlag = 1
GoTo Form_BeforeUpdate
End Sub

I'm still working out the bugs. The GoTo statement doesn't work. When
I take that code line out, click the button (so saveflag value
changes), then move to another record, the BeforeUpdate fires off but
it gives me an error on the "DoCmd.DoMenuItem acFormBar,
AcRecordsMenu,..."

 

[Garret]

I figured it out, here is my new code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
If Form.Dirty = True Then
SaveFlag = 1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Record was Saved", vbOKOnly, "Record Saved"
SaveFlag = 0
End If

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

There is a checker at the beginning of the beforeupdate, that makes
sure it doesnt run the code if saveflag is 1 (AKA the button has been
pressed). One problem solved.
Now just lies the problem of the error message that comes up when you
close the form and press cancel when it prompts for save.
How does Access's save system work regularly. If I hadn't implemented
any of this code, Access always saves a record whenever you make
changes to any of the fields. Do you know anything about this?

 

[Allen Browne]

Garret, I need to move on from this thread.

I have explained the things Access does as best as I can.

Regards

 

[Garret]

Alright Allen, thanks for all your help though. It opens up alot of
options and things I can try out. I appreciate it very much.