Declaring workspace in one form causes other form to lose track of its recordset?

[grzzlo]

I'm been struggling with a mysterious problem for days now and I finally found the cause. Now I wonder if anyone knows how to fix it...

Here's the problem: I open frmCashAccounts, which is bound to a query that draws from tblAccounts. The form has a standard "Go to record" combo box with automatically generated code behind it (there's other code behind the combo as well, but this is the part that gives the error):

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AcctID] = " & Str(Nz(Me![cboGoTo], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

frmCashAccounts has a subform which displays transactions based on the main form's AcctID. Clicking "Edit" on a record in the subform calls up a separate form (frmTransaction) to edit that transaction. After making changes to the transaction, the user clicks "OK" and then frmTransaction makes changes to tblTransactions and tblSplits using DAO and then closes, returning focus to frmCashAccounts. Here's some of the code:

    Dim wks As Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
...
    Set wks = DBEngine(0)
    Set db = wks.Databases(0)
    wks.BeginTrans
' set recordset using db.OpenRecordset and make various changes
...
    wks.CommitTrans dbForceOSFlush
...
PROC_EXIT:    
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    wks.Close
    Set wks = Nothing
    
    Exit Function
    
PROC_ERR:
    wks.Rollback

Then when I return to frmCashAccounts and try to change the current record using the "Go to record" combo box, I get run-time error 3420:

Run-time error '3420': Object invalid or no longer set.

The debugger highlights the following code:

Set rs = Me.Recordset.Clone

If I edit frmTransaction and remove all code related to wks (setting db to CurrentDb instead) the problem goes away.

So it seems that declaring a Workspace and setting it to DBEngine(0) and then closing it causes frmCashAccounts to lose track of its recordset. Is this expected behavior? Is there any way around it?

I'd really like to be able to rollback on error, and using a Workspace is the only way I know how to do it. Anyone have any suggestions?

Thanks in advance!

 

[MarkK]

I don't know, but I'd try not closing the workspace. When you use DBEngine(0) you get the default workspace which contains the database returned by CurrentDb. It might be that when you close that workspace, any recordsets created from the CurrentDb that lives in that workspace will get killed too. Of course Access will recreate that workspace, since it is the default, but maybe not until a request, say for CurrentDb, is made.
But anyway, try and not close the workspace and see if that changes anything. Or try something like....

On Error GoTo PROC_ERR
    Dim rst As DAO.Recordset

[COLOR="Green"]    'start transaction on the default workspace[/COLOR]
    DBEngine(0).BeginTrans
[COLOR="Green"]    'open a recordset on CurrentDb, which lives in the default workspace
    'and is therefore transaction-enabled[/COLOR]
    set rst = CurrentDb.OpenRecordset
[COLOR="Green"]    'work with recordset here, and close it here
    '...
    'note that any other action, system-wide, against CurrentDb now occurs inside this transaction[/COLOR]
[COLOR="Green"]    'commit[/COLOR]
    DBEngine(0).CommitTrans dbForceOSFlush

PROC_EXIT:    
    Exit Function
    
PROC_ERR:
    DBEngine(0).Rollback

Cheers,
Mark

 

[grzzlo]

Why didn't I think of that?

Works brilliantly.

Thank you!

 

[grzzlo]

Or maybe not? I tried both of the suggested fixes this morning on my machine and both seemed to work perfectly. In one case, I removed wks.Close and in the other I used DBEngine(0) instead of wks. I tested both and everything looked good.

Then I went and tried it on one of my user's computers. On her machine, I tested the version using the code quoted in my first post but with wks.Close commented out and everything seemed OK at first. I could open frmTransaction, make changes, close frmTransactions, go back to frmCashAccounts, and change the record using the "Go to" combo box without getting an error. But then I saw that the changes I had made in frmTransaction never got committed. I tried several times, and it was always the same. Then I tried quickly editing the code on her machine to switch all instances of wks to DBEngine(0). I'm pretty sure I did it correctly (I did a search for "wks" to be sure). Then I got run-time error 3420 again. What the? Did I not test well this morning?

So I just got back and tested again on my machine. Everything works great. So I'm confused.

Both machines have Access 2010. The user's machine has Vista and mine has Windows 7. Could Vista be the reason it didn't work?

Anyone know anything about workspaces working differently in Vista and Windows 7?