Message Box problem

[Tony Williams]

I have a subform on a tabcontrol. The subform is in continuous form format
with two combo box controls. I want a message to pop up asking the user if
they are sure they want to save the changes. I have this code in the
AfterUpdate property of the form


If vbNo = MsgBox("Are you sure you want to save the changes to the Primary
and Secondary Codes?", 36, "Save Changes") Then
Me.Undo
Cancel = True
End If

However the message appears each time the user adds a record, (the user can
add as many records to the sub form as they wish), rather than when the form
or main form is closed. I've tried putting the code in the On Close property
but I get the message even if they haven't made any changes. I've tried
changing the code on the OnClose property to this but I don't get a message
at all then.

If Me.Dirty Then
If vbNo = MsgBox("Are you sure you want to save the changes to the Primary
and Secondary Codes?", 36, "Save Changes") Then
Me.Undo
Else
Cancel = True
End If
End If

Can anyone see what I'm doing wrong?
TIA
Tony Williams

 

[Dave Jones]

Hi Tony,

The After Update event fires everytime the current record
is saved after making changes or when a new record is
saved for the first time.

The On Close event fires just before the form is closed,
after all changes to the current record have been saved,
therefore Me.Dirty will be false.

I would use the Before Update event to ask the user if
they want to save the changes to the record before they
are saved. It makes sense to ask this each time a record
is amended. Remember that only one record may be edited at
a time, so it is impossible to ask the user if they want
to save the changes they made to the previous ten records
when they exit. When they move from the first to the
second record, any changes to the first one are saved
then, NOT when you close the form.

If you really only want to ask for confirmation when a
record is changed and not when the user adds a new record,
test the Newrecord property. It willl be true if it is a
new record, and false if an existing one is being edited.

Dave

 

[Rick Brandt]

I have a subform on a tabcontrol. The subform is in continuous form format
with two combo box controls. I want a message to pop up asking the user if
they are sure they want to save the changes. I have this code in the
AfterUpdate property of the form


If vbNo = MsgBox("Are you sure you want to save the changes to the Primary
and Secondary Codes?", 36, "Save Changes") Then
Me.Undo
Cancel = True
End If

However the message appears each time the user adds a record, (the user can
add as many records to the sub form as they wish), rather than when the form
or main form is closed. I've tried putting the code in the On Close property
but I get the message even if they haven't made any changes. I've tried
changing the code on the OnClose property to this but I don't get a message
at all then.

If Me.Dirty Then
If vbNo = MsgBox("Are you sure you want to save the changes to the Primary
and Secondary Codes?", 36, "Save Changes") Then
Me.Undo
Else
Cancel = True
End If
End If

Can anyone see what I'm doing wrong?

First off you must have that code in the BeforeUpdate event of the form not the
AfterUpdate since AfterUpdate does not have a Cancel argument. That being the
case, BeforeUpdate is the proper place for it.

Second, you are not understanding how Access forms work. When you move to a
different record after inserting or editing a record the record you just left is
automatically saved. It has been written to disk at that point so you do not
have the option of NOT saving it anymore.

If you want to be able to insert a series of records and then offer the ability
to save or cancel the whole set, you would have to bind your form to work
tables and use a Delete query to remove the records when they choose close the
form. You would proceed the Delete query with an Append query to copy the
records from the work tables to the live tables when the user chose to save
them. The form's Close event would be the logical place to offer the choice.

Using temp tables as described is fairly straight-forward with new record
insertions, but doesn't offer an easy way to edit existing records. Even new
record insertions are complicated if you might have more than one user entering
records at a time. If you wanted to use an AutoNumber or a custom incrementing
value as a key, it would be complicated to enforce uniqueness across work
tables.

My advice is to get rid of the confirmation prompts completely. They are a
waste of time and an annoyance to the user. If they didn't want to save the
changes then why did they bother entering them? If they change their mind, that
can happen just as easily after they've answered a confirmation prompt as
before.

 

[Tony Williams]

Thanks Rick that makes a lot of sense. I never did like the idea of all
these prompt screens popping up but it was part of what my user wanted. I
think I'm going to try and convince them that they aren't necessary!
Thanks again for the advice and the pointers on record saving.
Tony

 

[Rick Brandt]

Thanks Rick that makes a lot of sense. I never did like the idea of all
these prompt screens popping up but it was part of what my user wanted. I
think I'm going to try and convince them that they aren't necessary!
Thanks again for the advice and the pointers on record saving.
Tony

A good compromise is to lock the form initially and offer an "Edit" button that
unlocks the form for making edits. Essentially you are asking the question
before they make the changes instead of afterwards. It guards against a
completely accidental edit because before the record can be changed the user has
to at least indicate his intention to change it.

 

[Tony Williams]

Thanks Rick I think the users will like that idea!
Tony