Transactions using a form

  • Thread starter Thread starter bcc
  • Start date Start date
B

bcc

Hello,

I've set up a form in Access 2000 that initiates a transaction on open, and
then either commits or rolls back any changes on close. The form functions
exactly the way I expect it to.

My problem is that I am unable to sort the recordset while I'm in a
transaction. I've tried setting the OrderBy/OrderByOn properties in code,
refreshing the form, and requerying the recordset (rsBases). All of these
actions result the error message:
Run-time error '2074': This operation is not supported within transactions.

Does anyone have any other suggestions or methods to try to resort a
recordset while in the middle of a transaction?

Defined at form module level:
Dim blnCancel As Boolean, wsBases As Workspace, dbBases As DAO.Database,
qdf As DAO.QueryDef
Dim rsBases As DAO.Recordset

In my Form_Open event, I have this code:
Set cdb = CurrentDb
Set wsBases = DBEngine.CreateWorkspace("wsBases", "Admin", "")
Set dbBases = wsBases.OpenDatabase(cdb.Name)
Set qdf = dbBases.CreateQueryDef("qryTmpNewBasesEntry", "SELECT * FROM
tblARTFundNewBases ORDER BY OrderNo")
Set rsBases = qdf.OpenRecordset
rsBases.LockEdits = False
Set Me.Recordset = rsBases
wsBases.BeginTrans

In my Form_Close event, I have this code:
If blnCancel Then
wsBases.Rollback
dbBases.QueryDefs.Delete "qryTmpNewBasesEntry"
Set wsBases = Nothing
Exit Sub
Else
wsBases.CommitTrans
dbBases.QueryDefs.Delete "qryTmpNewBasesEntry"
Set wsBases = Nothing
End If

Thanks in advance for any help/ideas!
Chris Chandler
 
I'm just guessing here, but I suspect the reason it can't be done is because of what a
Transaction does. Everything is held in a pending state until you commit the transaction,
therefore there is nothing new to sort, refresh, etc.
 
Back
Top