Sub form Undo 1

[adizukerman]

Before I state my problem, I want to let you to know that I am not an access programmer so please take that into consideration when writing your replies. I’m volunteering my time to assist a non-profit organization in the creation of a database to track their students, donors and orders. Any help would be appreciated.

The problem that I am running into is that I am trying to create an undo feature on a form with a sub-form. I know how to undo the main form (me.undo) and, thanks to Access 20002 Desktop Developer’s Handbook, I can undo a sub-form. But I can’t have them work perfectly together. The bug appears in the following situation.
1) Edit the main form (form becomes dirty)
2) I click on the sub-form
3) The main form automatically saves the record (now the me.Undo won’t help)
4) I make changes to a record on the sub-form
5) I click on either another sub-form record or the main form causing the sub-form to save changes to the record
5) I click the undo button which reserves the changes to the sub-form
6) The main form does NOT undo its changes <- PROBLEM

I need the main form to save records because the sub-form records use the primary key from the main form in its own records.

Below is the code I’m using based on the Access 20002 Desktop Developer’s Handbook (great book). Can someone please suggest a change to allow the undo button to undo ALL changes to both the form and sub-form records made since the user began editing either the form and/or sub-form?

In the sub-form
Private Sub Form_Dirty(Cancel As Integer)
If isSubForm(Me) Then
Me.Parent.cmdUndo.Enabled = True
End If
End sub

Private Sub Form_Undo(Cancel As Integer)
If isSubForm(Me) Then
Me.Parent.cmdUndo.Enabled = False
End If
End Sub

In the Main form

In form module header:

Private mwks As DAO.workspace
Private mblnInTrans As Boolean
‘ Query to link the main form to the sub-form
Private Const adhcSource As String = &quot;qryMainLink&quot;

Private Sub ResetData()
Dim db As DAO.database
Dim qdf As DAO.querydef
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter
Dim strSource As String

' Note the intentional lack of error handling.
' This one's just an example

' Assume user wants to commit changes in subform,
' if they haven't canceled them
If mblnInTrans then
mwks.CommitTrans
End if

' Create and assign filtered recordset for subform
Set mwks = DBEngine.CreateWorkspace(&quot;mwks&quot;, &quot;admin&quot;, &quot;&quot
Set db = mwks.opendatabase(CurrentDb.Name)
Set qdf = db.queryDefs(adhcSource)

' This will fail if the query has any parameters
' that aren't based on form values that it can resolve
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Me.Painting = False
Set rst = qdf.OpenRecordset
rst.LockEdits = False
Set frmSub.Form.Recordset = rst
Me.Painting = True

'Start a new subform transaction
mwks.BeginTrans
mblnInTrans = True
cmdUndo.Enabled = False

End Sub

Private Sub Form_Current()
Call ResetData
End Sub

Private Sub cmdUndo_Click()
Me.Undo
If mblnInTrans Then
mwks.Rollback
mblnInTrans = False

' shift focus, so button can be disabled
main_text.SetFocus

Call ResetData
End If
End sub

Again, thank you for taking the time to look at this.

- Adi Zukerman
adizukerman@hotmail.com

 

[votegop]

Adi:

I recently researched the same problem. My review of related posts lead me to believe the only option is to have an 'undo' button on the subform as well as the main form. This stinks, I know, but I haven't found a better solution.

Jay

 

[bradles]

I agree with Jay, even after trying to call a public 'Undo' function on the sub form from the parent form.

Very frustrating this one.

Brad

 

[JohnVogel]

You can use a temp table to store the values from the main/sub form. Then if the user cancels, you simply update the table with the stored values. If you need more clarification, let me know.

John Vogel
john@thecompuwizard.com

http://www.thecompuwizard.com

 

[adizukerman]

First of all thanks for the replies. I appreciate everyone taking the time to read my post and reply.

I'm not sure I understand the solution to putting an undo button on the main form. Won't that only undo any changes the user makes to the main form BEFORE they click on the sub-form. I'm trying to make the difference between the main form and sub-form seem transparent to the user so they don't need to decide whether they want to keep the changes to the main before before making a change to the sub-form. Please explain further.