transactions and too many db's

[formerTexan]

Hi,

I've been tinkering with lumping a main form and several subforms into a transaction to allow rolling back data changes and it works satisfactorily with a couple of subforms in the admittingly rough coding format posted below.

However, increasing the number of forms involved almost invariably raises a "can't open anymore databases" error in the midst of the procedure. The one exception was "can't open any more tables".

I'm at loss for why, since the flag at the beginning of the procedure precludes opening more than one workspace and db.

The usual insightful thoughts will be appreciated.

Thanks
Bill

' called from main form's OnCurrent event and when the Rollback button is clicked
'workspace and recordsets declared module level
'cleanup on form's unload
Private Sub ResetData()
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strfrmMain As String
strfrmMain = "frmContactMainEntry"
If mfInTrans Then
mwsp.CommitTrans
End If

If Not mblnwkspOpen Then ' only want to do this once
' open new workspace and database if not already open

Set mwsp = DBEngine.CreateWorkspace("mwsp", "Admin", "")
Set mdb = mwsp.OpenDatabase(CurrentDb.Name)
mblnwkspOpen = True 'flag so that only one workspace is open
End If

Me.Painting = False

Set qdf = mdb.QueryDefs(CSOURCEa)
For Each prm In qdf.Parameters 'get query parameters
prm.Value = Eval(prm.Name)
Next prm
Set mrsAddr = qdf.OpenRecordset 'retrieve subform's recordset
Set Forms(strfrmMain)(CCHILDa).Form.Recordset = mrsAddr
mrsAddr.LockEdits = False
qdf.Close


Set qdf = mdb.QueryDefs(CSOURCEb)
For Each prm In qdf.Parameters 'get query parameters
prm.Value = Eval(prm.Name)
Next prm
Set mrsPhone = qdf.OpenRecordset
Set Forms(strfrmMain)(CCHILDb).Form.Recordset = mrsPhone
mrsPhone.LockEdits = False
qdf.Close

'start the new form transaction
mwsp.BeginTrans
mfInTrans = True
Me.btnRollback.Enabled = False
Me.Painting = True

Set qdf = Nothing
End Sub