Saving records

[Guest]

I am trying to put the following code in the after update event:

Private Sub Form_AfterUpdate()
On Error GoTo Err_cmdsave_Click

Dim strUpdate As string
strUpdate = MsgBox("Do you really want to save this form?", vbYesNo,
"Amend Records")

If strUpdate = vbYes Then DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70

ElseIf strUpdate = vbNo Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
Exit Sub
Exit_cmdsave_Click:
Exit Sub

Err_cmdsave_Click:
MsgBox Err.Description
Resume Exit_cmdsave_Click

End Sub

but it keeps telling me my if statement is wrong. This project is my first
foray into coding, and I am doing it from the help menu in visual basic, but
I cannot track where the error is looking at the code.
Can someone help me please? I am trying to prompt the user to save their
stats prior to updating the database, otherwise do the equivalent of hitting
the escape button and not saving them prior to leaving the form.

Thanks

 

[John W. Vinson]

Can someone help me please? I am trying to prompt the user to save their
stats prior to updating the database, otherwise do the equivalent of hitting
the escape button and not saving them prior to leaving the form.

Well, the AfterUpdate event - as the name implies - executes *after* the data
have been updated to disk. It cannot be cancelled. If you really want to do
this... Use the BeforeUpdate event instead, and don't use the obsolete Menu
code! Try

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
iAns = MsgBox("Do you want to save this record?", vbYesNo)
If iAns = vbNo Then
Cancel = True
Me.Undo
End If
End Sub

Do note, however, that users will find this VERY annoying. "If I didn't want
to save the record, why the heck would I have *entered* it!?" In practice,
you'll see that users will just instantly and mindlessly click Yes, and your
code will be of no value.

If you want to check the data for internal consistancy and validity, and only
issue a message if there is something *wrong* with the data, you can use the
same sort of logic... but I'd really recommend against using just a
confirmation like this.

John W. Vinson [MVP]

 

[Guest]

Thanks! It almost works, apart from throwing up a message "you can't go to
the specified record when I select no.

People are used to saving information eg excel and word, found in the dummy
run that they were entering the same information over and over for many
reasons, and one reason was because they weren't prompted to save, so they
thought something was wrong. Another reason was because they were getting
distracted and not finishing their stats....so entering the same information
again because they didn't finish the form the first time.

 

[John W. Vinson]

Thanks! It almost works, apart from throwing up a message "you can't go to
the specified record when I select no.

That's odd. You're not going to any record, if you use the code I posted.
What's the Recordsource of the form? What's its Data Entry property?

People are used to saving information eg excel and word, found in the dummy
run that they were entering the same information over and over for many
reasons, and one reason was because they weren't prompted to save, so they
thought something was wrong. Another reason was because they were getting
distracted and not finishing their stats....so entering the same information
again because they didn't finish the form the first time.

I would suggest solving this in another way. Is there some field or
combination of fields that is unique to a record, that you can use to identify
duplicates? If so you can put code in the BeforeUpdate event of the control
(or controls) bound to that field, to warn the user if they're starting to
enter a duplicate. If the users want a save button, that's easy - just put a
command button named cmdSave, labeled SAVE with its Click event:

Private Sub cmdSave_Click()
If Me.Dirty = True Then
Me.Dirty = False
End If
End Sub

Won't do any harm and may ease their minds.


John W. Vinson [MVP]