Access error 2074 - transactions

[John_G]

Hi All -

I have a application in which I am transferring data from MS Word tables to MS Access, using cut-and-paste. The nature of the data is such that while there are 17 fields in the MS Access records, only one of them - a Memo text field - is copied form the Word tables.

To do this, I have a main form with a sub-form using datasheet view. Controls on the main form are used to add records to the sub-form's underlying table, and to determine which records are currently displayed by resetting the subform's recordsource according to selections made in two combo boxes on the main form

So, say I want to add 15 new records to match certain criteria, with one field coming from the Word table. I set the criteria on the main form (the sub form now shows no records), set a counter to "15", and click a button to add 15 records, one at a time in a loop, using VBA. 15 records are shown, with all fields are filled in, except the one which which is coming from the MS Word table. This data I copy to MS Access using cut-and-paste from the table column in Word to the datasheet column in Access.

All this works fine, but after two or three iterations of setting criteria, adding records, and pasting data, Access will give me error 2074 - "This operation is not supported within transactions"

The error is raised when I try to reset the subform's recordsource with new criteria:

sfrmStatement.Form.RecordSource = " SELECT * FROM Edit_Job_Statement_Query WHERE Statement_Category = " & cboFilterStatement & _
" and [Duty_id] = " & SelectDuty

cboFilterStatement and SelectDuty are the two combo boxes.



I have no idea what the problem here is, but there is a clue: I do trap the error, and if I close the main form and reopen it, I notice that the paste operation PRIOR to the error did not work, even though on the screen it looks as if it did.

It's as if there is an update that is still "pending" somewhere. I have put dirty = false in as many places as I could think of, but no luck.

Does anyone have any ideas as to what is happening?

Thanks

John

[orange]

The error mentions transactions, are you actually using BeginTrans and CommitTrans.
Can you show us some code?
Is there some restriction on the use of memo fields in transactions?? I don't know and haven't found anything.
Does your info from Word have any special characters that could be causing an issue?
Your cut and paste is a manual intervention, is that true?

Just saw this
http://www.tek-tips.com/viewthread.cfm?qid=1150927
and there are a few issues with 2074 without using transactions.

Try google and see if you get anything meaningful.

[John_G]

No, I don't use transactions - that's why the error is so strange. There is nothing unusual about the MS Word text, and yes the cut-and-paste is a manual operation.

Here is my code:
' giAdded_Duty is a global var, set by another form
'
If giAdded_Duty <> 0 Then
sfrmStatement.Form.Dirty = False
SelectDuty = giAdded_Duty
'
' The line below triggers the error
'
sfrmStatement.Form.RecordSource = " SELECT * FROM Edit_Job_Statement_Query WHERE Statement_Category = " & cboFilterStatement & _
" and [Duty_id] = " & SelectDuty
sfrmStatement.Form.Requery
End If

My impression is that there is some sort of "pending update" to the data in the sub-form, and what leads me to think so is that when I paste data into the sub-forms datasheet, it looks fine, but if the errors occurs on the next thing change to the combo box, then that paste operation has failed (closing and reopening the form shows the data is not there).

I use subformcontrol.form.dirty = false everywhere I can, but nothing seems to work.

Any of our gurus have any ideas?

Thanks

[orange]

In the link I gave in my last post (tektips) that poster found the error to be with a Windows Hotfix. That post showed the error message to be a little misleading as well.

Have you tried a Debug.print to see what is in the ...recordsource?

Debug.print sfrmStatement.Form.RecordSource

Have you tried using Me!sfrmStatement.Form.RecordSource ? Just a guess

see http://access.mvps.org/access/forms/frm0031.htm

[John_G]

The mystery of this deepens. I changed my code to this:

If giAdded_Duty <> 0 Then
sfrmStatement.Form.Dirty = False
SourceObject = sfrmStatement.SourceObject
sfrmStatement.SourceObject = SourceObject
SelectDuty = giAdded_Duty
sfrmStatement.Form.RecordSource = " SELECT * FROM Edit_Job_Statement_Query WHERE Statement_Category = " & cboFilterStatement & _
" and [Duty_id] = " & SelectDuty
sfrmStatement.Form.Requery
End If

with new lines in bold.

This got rid of the 2074 error.

I found that those two bold lines cause the On Close event of the subform to fire. So, I put me.dirty = False as the On Close procedure. Still no luck - only the first paste operation is retained.

Then , just to make sure the On Close event really was firing, I put Msgbox "On Close" before the me.dirty = False in the subform On Close - and everything works properly!

I glad it works - but I'm at a loss as to why. Either something really odd is happening, or there is some sort of timing issue.

Any ideas?

Thanks

John