A
Adi Zukerman
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-for
5) I click on either another sub-form record or the main form causing the sub-form to save changes to the recor
5) I click the undo button which reserves the changes to the sub-for
6) The main form does NOT undo its changes <- PROBLE
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-for
Private Sub Form_Dirty(Cancel As Integer
If isSubForm(Me) The
Me.Parent.cmdUndo.Enabled = Tru
End I
End su
Private Sub Form_Undo(Cancel As Integer
If isSubForm(Me) The
Me.Parent.cmdUndo.Enabled = Fals
End I
End Su
In the Main for
In form module header
Private mwks As DAO.workspac
Private mblnInTrans As Boolea
‘ Query to link the main form to the sub-for
Private Const adhcSource As String = "qryMainLink"
Private Sub ResetData(
Dim db As DAO.databas
Dim qdf As DAO.queryde
Dim rst As DAO.Recordse
Dim prm As DAO.Paramete
Dim strSource As Strin
' Note the intentional lack of error handling
' This one's just an exampl
' Assume user wants to commit changes in subform
' if they haven't canceled the
If mblnInTrans then
mwks.CommitTran
End if
' Create and assign filtered recordset for subfor
Set mwks = DBEngine.CreateWorkspace("mwks", "admin", ""
Set db = mwks.opendatabase(CurrentDb.Name
Set qdf = db.queryDefs(adhcSource
' This will fail if the query has any parameter
' that aren't based on form values that it can resolv
For Each prm In qdf.Parameter
prm.Value = Eval(prm.Name
Next pr
Me.Painting = Fals
Set rst = qdf.OpenRecordse
rst.LockEdits = Fals
Set frmSub.Form.Recordset = rs
Me.Painting = Tru
'Start a new subform transactio
mwks.BeginTran
mblnInTrans = Tru
cmdUndo.Enabled = Fals
End Su
Private Sub Form_Current(
Call ResetDat
End Su
Private Sub cmdUndo_Click(
Me.Und
If mblnInTrans The
mwks.Rollbac
mblnInTrans = Fals
' shift focus, so button can be disable
main_text.SetFocu
Call ResetDat
End I
End su
Again, thank you for taking the time to look at this
- Adi Zukerma
(e-mail address removed)
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-for
5) I click on either another sub-form record or the main form causing the sub-form to save changes to the recor
5) I click the undo button which reserves the changes to the sub-for
6) The main form does NOT undo its changes <- PROBLE
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-for
Private Sub Form_Dirty(Cancel As Integer
If isSubForm(Me) The
Me.Parent.cmdUndo.Enabled = Tru
End I
End su
Private Sub Form_Undo(Cancel As Integer
If isSubForm(Me) The
Me.Parent.cmdUndo.Enabled = Fals
End I
End Su
In the Main for
In form module header
Private mwks As DAO.workspac
Private mblnInTrans As Boolea
‘ Query to link the main form to the sub-for
Private Const adhcSource As String = "qryMainLink"
Private Sub ResetData(
Dim db As DAO.databas
Dim qdf As DAO.queryde
Dim rst As DAO.Recordse
Dim prm As DAO.Paramete
Dim strSource As Strin
' Note the intentional lack of error handling
' This one's just an exampl
' Assume user wants to commit changes in subform
' if they haven't canceled the
If mblnInTrans then
mwks.CommitTran
End if
' Create and assign filtered recordset for subfor
Set mwks = DBEngine.CreateWorkspace("mwks", "admin", ""
Set db = mwks.opendatabase(CurrentDb.Name
Set qdf = db.queryDefs(adhcSource
' This will fail if the query has any parameter
' that aren't based on form values that it can resolv
For Each prm In qdf.Parameter
prm.Value = Eval(prm.Name
Next pr
Me.Painting = Fals
Set rst = qdf.OpenRecordse
rst.LockEdits = Fals
Set frmSub.Form.Recordset = rs
Me.Painting = Tru
'Start a new subform transactio
mwks.BeginTran
mblnInTrans = Tru
cmdUndo.Enabled = Fals
End Su
Private Sub Form_Current(
Call ResetDat
End Su
Private Sub cmdUndo_Click(
Me.Und
If mblnInTrans The
mwks.Rollbac
mblnInTrans = Fals
' shift focus, so button can be disable
main_text.SetFocu
Call ResetDat
End I
End su
Again, thank you for taking the time to look at this
- Adi Zukerma
(e-mail address removed)