Undo changes to record shown through a form

[kilando]

Hi everybody.

I'm working on an Access 2000 DB using VBA and I'm trying to set the close button on a form so when it is clicked the user gets asked if they want to save any changes.

I use the dirty event to see if any of the values for the records have been changed and this works ok. My problem comes when No is selected from the message box. It runs through the code and then I get an error message saying: "Object doesn't support this property or method"

My code for the event looks like this:

Private Sub ExitButton_Click()
Dim x As Integer
Dim cltC As Control

If Me!Update.Enabled Then
x = MsgBox ("Save Changes?", vbYesNoCancil + vbQuestion)
If (x = 6) Then 'Yes Clicked
DoCmd.Close
End If
If (x = 7) Then 'No Clicked
'For Each Control
For Each cltC in Me.Controls
If cltC.ControlType = acTextBox Then
'Restore Old Values
cltC.Value = cltC.OldValue
End If
Next cltC
DoCmd.Close
End If
If (x = 2) Then 'Cancil Clicked
'Do Nothing
End If
Else
DoCmd.Close
End If
End Sub

The Update.Enabled is a button made invisiable on the screan. Its properties are orignally set so its Enabled status is false. The OnDirty event will change its enabled status to true, so then when the exit button is clicked, the:
if Me!Update.Enabled
will return true if the form had been updated.

Basically I'm wondering if anyone sees an error in my code that is leading to the "Object doesn't support this property or method" error, or can someone suggest a better way to accomplish what I am trying to do.

Thanks

 

[psemianonymous]

For the form, use "Me.Undo" to undo any changes to the data/dirty record. I believe it throws an error if there is nothing to undo, but you can check that using "If Me.Dirty Then Me.Undo"

 

[BlackKnight]

Hi,

I think the controltype property is incorrect. Revising your code like:

Private Sub ExitButton_Click()
Dim x As Integer
Dim cltC As Control

If Me!Update.Enabled Then
x = MsgBox("Save Changes?", vbYesNoCancel + vbQuestion)
If x = vbYes Then 'Yes Clicked
DoCmd.Close
ElseIf x = vbNo Then 'No Clicked
For Each cltC In Me.Controls
If TypeOf cltC Is TextBox Then
'Restore Old Values
cltC.Text = cltC.OldValue
End If
Next cltC
DoCmd.Close
ElseIf x = vbCancel Then 'Cancel Clicked
'Do Nothing
End If
Else
DoCmd.Close
End If
Set cltC = Nothing
End Sub

Have a good one!
BK

 

[kilando]

Thanks for the help everyone. I've got it working now.