Dirty property not available with unbound controls?

[Me!User]

1. All controls on a form are unbound text boxes.
2. The form has two command buttons: Save and Close.
3. The Close button tests Me.Dirty to determine if any changes remain unsaved.
4. Me.Dirty always returns False. (?!?)

Does the Dirty property only work with forms featuring bound controls?

Any guidance would be appreciated.

 

[ghudson]

Dirty is testing if the value of the field has been modified. An unbound field has nothing to test against. I am not sure how you could test for this since you can not use the .OldValue property to test against either.

 

[charityg]

A recommendation (not saying it's right or wrong)

Store the original values in an array, create an IsDirty function that compares the present value of the controls to the values for the controls stored in the array. You might want to utilize the tag property of the control to keep track of the array index.

 

[Me!User]

How do you rollback changes with bound controls?

Hmm. Sounds like a fair bit of programming in order to duplicate the functionality of the Dirty property.

I chose to use unbound controls in order to be able to commit (or discard) all changes in one operation. Nothing gets written to the table until the user says so. However, I didn't anticipate having to forego the use of the Dirty property!

If one uses bound controls on a form, commits are implicit and Undo is the only way to reverse the changes. Or is it? Is there another way to control commit/rollback with bound controls?

 

[ghudson]

Nothing gets written to the table until the user says so. However, I didn't anticipate having to forego the use of the Dirty property!

Check out how I test if it is Dirty and force the user to click my custom Save or Undo button. The user is not able to leave the record or close the form if the current record is Dirty. Here is the link to my sample db...
http://www.access-programmers.co.uk/forums/showthread.php?t=38364

 

[Me!User]

ghudson:

Thank you. I reviewed Mouse Trap previously and found it to be a very clever and effective solution. I did not emulate it because I was hell-bent on using an unbound form to achieve my goal of total control over commit/rollback.

Now that I've discovered unbound forms forego some of the built-in features (like the Dirty property) I decided to revisit bound forms.

I found the following article to be extremely illuminating:
"ACC2000: How to Control Bound Form Transactions in Access Databases."
http://support.microsoft.com/default.aspx?scid=kb;en-us;248011

I completed the example and was pleasantly surprised by how well it worked. Using the newly built form, I made changes to several records and deleted one as well. Upon closing the form I was prompted to 'commit the data' and I answered No. Presto! All changes, including the deleted record, were rolled back.

Now that I'm aware of the BeginTrans, CommitTrans, and Rollback methods, I'll switch to using bound forms.

 

[Me!User]

Distilled version of Microsoft's example.

For the curious, here is a commented and pared-down version of Microsoft's example of using BeginTran, Commit and Rollback.

-------------------------------------------------------------------
This example refers to a table called Customers.
-------------------------------------------------------------------
Option Compare Database
Option Explicit

' Create two flags to indicate when a form is dirty or when data has been saved.
Private fDirty As Boolean
Private fSaved As Boolean


Private Sub Form_AfterDelConfirm(Status As Integer)
' A record has been deleted.

' Indicate data has been saved if the deletion process was successful.
If Not (fSaved) Then fSaved = (Status = acDeleteOK)

End Sub


Private Sub Form_Dirty(Cancel As Integer)
' The user has started modifying the data in a control.
' If this is the first modification, begin the transaction.
If Not (fDirty) Then DBEngine.BeginTrans

' Indicate the form is dirty.
fDirty = True

End Sub


Private Sub Form_AfterUpdate()
' The user has modified data in a control and has moved to another control or record.

' Indicate data has been saved.
fSaved = True

End Sub


Private Sub Form_Delete(Cancel As Integer)
' The user has started the process of deleting a record.

' If this is the first modification, begin the transaction.
If Not (fDirty) Then DBEngine.BeginTrans

' Indicate the form is dirty.
fDirty = True

End Sub


Private Sub Form_Open(Cancel As Integer)
' The user has opened the form but no records have been retrieved yet.

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Customers", dbOpenDynaset)
Set Me.Recordset = rs

End Sub


Private Sub Form_Unload(Cancel As Integer)
' User has requested to close the form.

Dim msg As Integer

' If data has been saved, request confirmation.

If fSaved Then
msg = MsgBox("Do you want to commit all changes?", vbYesNoCancel)

Select Case msg
Case vbYes ' Confirmed. Commit the data.
DBEngine.CommitTrans

Case vbNo ' Denied. Rollback all modifications.
DBEngine.Rollback

Case vbCancel
Cancel = True ' Do nothing.

End Select
Else ' Nothing has been saved but check if the form is dirty.
If fDirty Then DBEngine.Rollback
End If

End Sub