Run-time error 3003 - Storing Values from Form to Table

[teambau]

I'm encountering an error in MS Access 2007 when I try to store values from a form to a table. Here's a snippet of the VB:

Private Sub Submit_Update_Click()
Dim DB As Database
Dim new_comment As Recordset
Set DB = CurrentDb()
 
Set new_comment = DB.OpenRecordset("tbl_History", dbOpenDynaset)
new_comment.AddNew
 
new_comment("AWT") = AWT.Value
new_comment("Issue_Criticality") = Issue_Criticality.Value
 
new_comment.Update
new_comment.Close
 
Done1:
AWT.Value = Null
Issue_Criticality.Value = Null
 
Done:
DB.Close
 
End Sub
 

I do have more than two fields being stored, but it didn't seem necessary to list them all - 12 total. The idea is that the user will click the 'Submit' button (the above code being the event) and thereafter, the values get stored to the table and the form clears the entered values for a subsequent entry.

The run-time error '3003' says "Could not start transaction; too many transactions already nested". Does that mean I'm trying to store too many input values?

[Jim Doherty]

Try fully qualifying your object library and closing all of your object variables down at the end of your code sequences and setting them to nothing. I suspect your are leaving all sorts of references,recordsets and other stuff open in memory with your workspace as you are testing/building. (I can see you have closed in this example and db). One way of testing if this is the cause is by exiting out of access and restarting

Just my two cents observation here....have you considered just returning one row recordset if all you are doing is adding a new one? why not just call...

"Select top 1 * from tbl_History"

or

"Select * from tbl_History where 1=2"

This will save memory and better over a network as you are not dragging anything up (network... split backend etc) and will be faster as your database grows in size.

[teambau]

Jim, thanks for the tips. I will look into this.