BeforeUpdate/Dirty properties

  • Thread starter Thread starter speederpro
  • Start date Start date
S

speederpro

I have a form with a "save" button and a "close" button.
I want to prompt the user if they want to save the current
record when the user try to close the form and the record
has been modified. Here is the code that I have.

sub cmdSave_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
end sub

sub cmdClose_Click()
DoCmd.close
end sub

sub Form_BeforeUpdate(Cancel as integer)
dim strResponse as string
strResponse = _
msgbox ("Do you want to save current record", vbYesNo)
if strResponse = vbNo then
Me.Undo
end if
end sub

When user modify the current record and close without
saving, it prompts the user if they want to save the
record. After responding to user response, it closes the
form. However, when I hit "save", the prompts come up too.
I found that it is cause by the
docmd.gotorecord , , acnewrec
Is there a way to prevent this from happening.

Basically,
1) I want to save the record when user hit save. No
prompts.
2) If no changes and user hit close, the form closes.
3) If there is changes and user hit close, the form
prompts the user if they want to save current changes.
 
Basically,
1) I want to save the record when user hit save. No
prompts.
Click to expand...

I usually put:

If Me.Dirty then Me.Dirty = False
2) If no changes and user hit close, the form closes.
3) If there is changes and user hit close, the form
prompts the user if they want to save current changes.
Click to expand...

Again, check the dirty property:

If Me.Dirty then
If MsgBox("Save record before closing?",vbYesNo) = vbYes then
Me.Dirty = False
Else
Me.Undo
End If
End if

Docmd.Close

(NB Your code has a problem as you have assumed that the value vbNo is a
string - it's not it's a long integer).

If you only do the above then your users still have several ways of saving
records without using your save or close buttons. You could use a module
level boolean variable to cancel the save unless you have allowed it:

'At the top of the module:
Private OKToSave as Boolean

'In the form open event:
OKToSave = False

'In the before update event:
Cancel = Not OKToSave

'Before any line that allows the record to be saved:
OKToSave = True

Hope this helps!

Andrew
 
Back
Top