Confirm data change on close

[Travis (New User)]

I want a data editing form to check for changes when the close button is
pressed by the user. The code I have entered reads "Run time error 13. Type
Mismatch" Any help would be greatly appreciated!

Private Sub Form_Close()

If Me.DataChange Then
Dim strMsg As String
Dim Cancel As Integer
strMsg = "Data has changed."
strMsg = strMsg & " Do you wish to save the changes?"
strMsg = strMsg & " Click Yes to Save or No to Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes
Then
'do nothing
Else
Me.Undo
Cancel = True
End If
Else
'do nothing
End If
End Sub

 

[Stuart McCall]

I want a data editing form to check for changes when the close button is
pressed by the user. The code I have entered reads "Run time error 13.
Type
Mismatch" Any help would be greatly appreciated!

Private Sub Form_Close()

If Me.DataChange Then
Dim strMsg As String
Dim Cancel As Integer
strMsg = "Data has changed."
strMsg = strMsg & " Do you wish to save the changes?"
strMsg = strMsg & " Click Yes to Save or No to Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes
Then
'do nothing
Else
Me.Undo
Cancel = True
End If
Else
'do nothing
End If
End Sub

Use the form's Unload event instead of its Close event. Notice that the
Unload event has a parameter called Cancel. Because that's supplied by
Access, you should delete the line:

Cancel = True

 

[raylopez99]

I want a data editing form to check for changes when the close button is
pressed by the user. The code I have entered reads "Run time error 13. Type
Mismatch" Any help would be greatly appreciated!

Private Sub Form_Close()

If Me.DataChange Then
Dim strMsg As String
Dim Cancel As Integer
strMsg = "Data has changed."
strMsg = strMsg & " Do you wish to save the changes?"
strMsg = strMsg & " Click Yes to Save or No to Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes
Then
'do nothing
Else
Me.Undo
Cancel = True
End If
Else
'do nothing
End If
End Sub

Don't know much about VB, after only a week or so coding, but
sometimes this occurs when you mix and match strings and numbers on
the same line. Use the CStr() convert function to convert numbers to
strings and the corresponding cast for strings to numbers.

RL

 

[Stuart McCall]

Use the form's Unload event instead of its Close event. Notice that the
Unload event has a parameter called Cancel. Because that's supplied by
Access, you should delete the line:

Cancel = True

Sorry, that should read:

Because that's supplied by Access, you should delete the line:

Dim Cancel As Integer

 

[Klatuu]

You don't say on which line the error occurs, but I am thinking the way you
are using Me.DataChange could be the problem. A form control will not
evaluate to a boolean expression. That is, if DataChange is a control. If
it is a user defined form property, then it should be cast as a boolean data
type.

 

[raylopez99]

You don't say on which line the error occurs, but I am thinking the way you
are using Me.DataChange could be the problem.  A form control will not
evaluate to a boolean expression.  That is, if DataChange is a control.  If
it is a user defined form property, then it should be cast as a boolean data
type.

Yes, Klatuu is right.

I was going to say the same thing the first time. For example, before
some helpful posters pointed out the correct syntax, the below used to
always give me this error:

'CORRECT SYNTAX:

If Not IsNull(Me.StockSymbol) Then
Str002G = Me.AcctID.Value
If Not IsNull(Me.AcctID) Then
Str001G = Me.StockSymbol.Value
End If
End If

replaces (THIS GIVES A RUN TIME ERROR OF "Type 13"--among other
errors):

If (Me.StockSymbol.Text <> Null And Me.AcctID.Text <> Null ) Then

...

End If

 

[Klatuu]

This is not really a data type issue:
If (Me.StockSymbol.Text <> Null And Me.AcctID.Text <> Null ) Then

It is a syntax issue. Nothing will ever = Null or be <> Null. Even Null =
Null will return false. The only way to check for Null is:

If Not IsNull(Me.StockSymbol) And Not IsNull(Me.AcctID) Then

In this context, don't use the Text property. It is only valid when a
control has the focus.

 

[John W. Vinson]

I want a data editing form to check for changes when the close button is
pressed by the user. The code I have entered reads "Run time error 13. Type
Mismatch" Any help would be greatly appreciated!

Private Sub Form_Close()

If Me.DataChange Then

What is DataChange? A control on your form, or a custom form property that you
have created?

Because it's not a builtin Access feature, to my knowledge.

Do you perhaps mean Me.Dirty (a Boolean indicating that the user has changed
some value in a bound control on the form)?

John W. Vinson [MVP]

 

[Travis (New User)]

Alright guys, thanks for all the help. All of your answers put me in the
right direction. I didn't do a very good job explaining exactly what I
wanted, but here is the code that I came up with after all of your
suggestions that works:

If Me.Dirty Then
Dim strMsg As String
strMsg = "Data has changed."
strMsg = strMsg & " Do you wish to save the changes?"
strMsg = strMsg & " Click Yes to Save or No to Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes
Then
'do nothing
DoCmd.Close
Else
Me.Undo
DoCmd.Close
End If
Else
'do nothing
DoCmd.Close
End If

On a form I have a close button, a save button, text boxes, drop down lists,
etc etc. I have taken away the microsoft given buttons at the top. The only
way for the user to interact with the form is by the objects I have given
them.

I wanted the save button to save the record automatically with no prompting,
but I wanted the exit button to check if there were any accidental changes
and ask the user to confirm what they wanted.

Again, thanks for all the help guys. You all got me on the right path!

- Travis

 

[Klatuu]

For your save button:

If Me.Dirty Then
Me.Dirty = False
End If

For your Exit Button, just use Docmd.Close in the click event. Put the code
to check for changes in the Form's Unload event. That way, if the user
closes the form either using the exit button or by some other means, it will
stilll do the check.

Put this in the Form Unload event:

Dim str Msg As String

If Me.Dirty Then
strMsg = "Data has changed."
strMsg = strMsg & " Do you wish to save the changes?"
strMsg = strMsg & " Click Yes to Save or No to Discard changes."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
Me.Dirty = False
Else
Me.Undo
End If
End If

 

[raylopez99]

For your save button:

    If Me.Dirty Then
        Me.Dirty = False
    End If

For your Exit Button, just use Docmd.Close in the click event.  Put the code
to check for changes in the Form's Unload event.  That way, if the user
closes the form either using the exit button or by some other means, it will
stilll do the check.

Put this in the Form Unload event:

Very interesting thread but I have a newbie question--I understand the
principle behind Save in Windows, but in Access it seems that clicking
on the red "X" in the upper right corner always saves the record
automatically. That is, for any form I've worked with to date (about
2 weeks of VB programming to be sure), it seems (maybe I'm wrong) that
no data has ever been lost this way (by simply closing the form as
described above).

In short, why bother with "save"--it's it the default to save
everything if a form/control is dirty?

RL

 

[Klatuu]

You are correct. closing the form or the application will save any changes.

There is no real good reason to use a save button. It is just that some
people are afraid they will save data they don't want to save.

--
Dave Hargis, Microsoft Access MVP

Very interesting thread but I have a newbie question--I understand the
principle behind Save in Windows, but in Access it seems that clicking
on the red "X" in the upper right corner always saves the record
automatically. That is, for any form I've worked with to date (about
2 weeks of VB programming to be sure), it seems (maybe I'm wrong) that
no data has ever been lost this way (by simply closing the form as
described above).

In short, why bother with "save"--it's it the default to save
everything if a form/control is dirty?

There is no real good reason to do this. It is just that some people are
afraid they will save data they don't want to save.

 

[RickW]

This method isn't working for me!
I am finding that the forms Dirty property is changed to False by
DoCmd.Close so the code in Form_UnLoad never recognises an unsaved record.

I feel I getting no where! Hope you can help!

Regards
Rick

 

[Klatuu]

I'm not sure what you are saying.
Docmd.Close should fire the Form Before Update, After Update, Unload, and
Close events in that order.

 

[RickW]

Thanks - sorry for the lack of info.

What I'm trying to do is use the Form_Unload event to check for unsaved
records before closing the form.

If the record is unsaved, I want to ask the user:
1. if they want to save it,
2. if they want to close without saving, or
3. if they want to cancel, and stop the form closing

I have a Close button with the code:
DoCmd.Close acForm, Me.Name, acSaveNo

and I also have enabled the standard form close button at top right corner,
but it makes no difference. Using both methods results in an the record
being saved by the time Form_UnLoad is reached. In other words, DoCmd.Close
saves the record. I cannot therefore see how the code discussed previously
can ever work as I think it is intended.

Surely this is a pretty standard requirement in coding any kind of form?
I just wish I knew how to do it in Access 2003.

By the way, I don't have any code in the Form Before Update, and After
Update events, and I have tried all combinations of arguements for
Docmd.Close. Basically I'm at the end of my tether and looking forward to
Friday!

Hope you can help now
Regards
Rick

 

[Klatuu]

I don't know what is going on in your form, but the record should not be
saved as you say it is being done. In fact, if you notice the definition of
the Unload event, it has a Cancel parameter, so you can cancel the closing of
the form if you want to. One thing you can do, is put a line of code in the
form's Before Update event to see exactly when it record is about to update.
Then run in debug mode to find out exactly when things are happening.

 

[RickW]

Thanks Dave, but I still think the method of using the Form_Dirty property in
the Form_Unload event will never work as a means of ascertaining whether the
record is saved.

I have tested this in a new mdb file and on two PCs, one with 2003 installed
and the other with 2007. Everytime the record is saved between
Form_BeforeUpdate and Form_AfterUpdate (which sounds logical), and therefore
before Form_Unload.

The form module code is:
Option Compare Database
Option Explicit

Private Sub cmdCloseForm_Click()
Debug.Print "Me.Dirty = " & Me.Dirty & " before DoCmd.Close"
DoCmd.Close
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Debug.Print "Me.Dirty = " & Me.Dirty & " at Form_BeforeUpdate"
End Sub

Private Sub Form_AfterUpdate()
Debug.Print "Me.Dirty = " & Me.Dirty & " at Form_AfterUpdate"
End Sub

Private Sub Form_Unload(Cancel As Integer)
Debug.Print "Me.Dirty = " & Me.Dirty & " at Form_Unload"
End Sub

When there is an unsaved record and cmdCloseForm is clicked the immediate
window looks like this:
Me.Dirty = True before DoCmd.Close
Me.Dirty = True at Form_BeforeUpdate
Me.Dirty = False at Form_AfterUpdate
Me.Dirty = False at Form_Unload

Am I missing something?
Regards
Rick

 

[Klatuu]

We are both missing something here.
The reason I am forgetting it is I never use it. I don't see the point and
as you are discovering, it is hard to control.

I went back and reread your original code. First, there is no such thing as
DataChanged, unless you have a control with that name.

Also, the Close event has no Cancel parameter.
Try moving your code to the Unload event.

 

[RickW]

Its OK Dave. I've actually put a the Yes/No/Cancel MsgBox in my cmdClose
sub, and disabled the standard close button. It does the job unless the user
quits the application.

I don't know what the Form_Unload event is for unless you want to ask the
user if they really meant to close the form - seems pointless.

I agree with you on DataChanged. That was in the very first thread from
Travis. I think it's something to do with pivot reports.

Regards
Rick

 

[Klatuu]

That DataChanged thing may have been the problem. Normally, I would use

Private Sub Form_Unload(Cancel As Integer)
Dim lngAnswer As Long

If Me.Dirty Then
lngAnswer = MsgBox "Unsaved Record bla bla", vbYesNoCancel)
Select Case lngAnswer
Case vbYes 'Save the Changes
'Do Nothing
Case vbNo 'Undo and Close Form
Me.Undo
Case vbCancel 'Dont close, continue editing
Cancel = True
End Select
End If
End Select
End If