Recordset error "too many transactions"

[reginaldmerritt]

When opeing and closing a DAO recordset a few times i get the following error message:

Run-time error '3003';
Could not start transaction; too many transactions already nested.

I've been google and forum investigating all day. The only thing i have found is information on Microsofts support site regarding a bug but it seems to only mention ADO recordsets.

The Microsoft support site webpage is here and a workaround is here

However when i use this workaround, which states to close the Recordsets's active connections by using ActiveConnection = Nothing I get the following error:

Run-time error '438';
Object doesn't support this property or methord

A simplised version of the code i'm using is:

Dim ChargeRateRecordSet As DAO.Recordset
Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("Select * FROM ChargeRates ")
ChargeRatesRecordSet.ActiveConnection = Nothing
ChargeRatesRecordSet.Close
 

Is the problem i'm having with "too many transactions already nested" a bug in ms access? If so is the workaround i'm using correct or is there a different syntax for DAO recordsets?

Any help would be great, thanks.

[nico5038]

Looks like Access creates a loop because of the SELECT statement.
Try just:

Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("ChargeRates")
 

as the table will be seen as a single recordset.

Nic;o)

[TheSmileyCoder]

Well when your using a DAO recordset like that you should not set the connection. You allready have a source for your recordset when you specified CurrentDB.

You should however remember when your done with your recordset to set the recordset=nothing.

Set ChargeRatesRecordSet=Nothing

However nothing of the code shown here displays any TRANSACTIONS.

Remember that access VBA does not always point at the correct line of code causing the error. Do you use transactions in any place, and do you remember to commit them?

[reginaldmerritt]

Thanks for the replies,

The code above was a cut down version. My actual select statment is:

Set ChargeRatesRecordSet = CurrentDb.OpenRecordset
("Select Top 1 * 
FROM ChargeRates 
WHERE [DateAsOf] <= #" & DateFromForm & "# 
ORDER BY [DateAsOf] DESC;")
 

thanks for the post nico5038. Is there anything wrong the code above ?

Using Set ChargeRatesRecordSet=Nothing works as the correct syntax, however, i still have the same problem with too many transactions already nested after opening and closing the recordset a few times.

The line of code pointed to when the error occurs is the code above. But as you so rightly say it could be that the error is actually caused somewhere else.

I do have tranasactions in place, i think? transactions are where the recordset is accesed, is that correct? In which case yes i have a few but they are all read the recordset and don't edit or update the recordset in anyway.

An example of some of the recordsets i use is below:

If Me.Controls("AppFrmVolunteer" & LineNumber) = True Then
Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureCOST")
Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureADMIN")
Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureVAT")
Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureTotal")
 

TheSmileyOne what do you mean by do you remember to commit them?

[TheSmileyCoder]

A transaction is something you use to control multi table updates. Since you can only update one table at a time, you may run into situations where you need transactoin. Take this eksample. You Take money from Person A in 1 table, and give them to Person B in another table. What if the computer/connection fails AFTER taking the money but BEFORE giving the money?

To prevent this people use transactions. In very simple terms, you start it with BeginTrans and once you succesfully reach the end of your code, you do a CommitTrans. If code fails/errors you can do a RollBack.

Have you used any of those keywords such as BeginTrans? If you haven't I don't think your using transactions. If that is the case, I would need to see more code, to realise where the error is occuring.

[reginaldmerritt]

Thanks for the clear explination. In that case, no i'm not using transactions.

Here is the full sub routine, there is another 2 subrountines that use recordssets but the error only seems point to this one.

Private Sub CalculateInovice()
Dim DateFromForm
Dim Counter As Integer
 
'get correct to date values from chargerates
If Not IsNull(Me.Controls("AppFrmNum" & LineNumber)) Then
 
    DateFromForm = Format(Me.Controls("AppFrmDate" & LineNumber), "m/d/y")
 
    Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("Select Top 1 * FROM ChargeRates WHERE [DateAsOf] <= #" & DateFromForm & "# ORDER BY [DateAsOf] DESC;")
 
    ' if ISA Reg Only
    If Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmStandard" & LineNumber) = False And Me.Controls("AppFrmEnhanced" & LineNumber) = False Then
        Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyCOST")
        Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyADMIN")
        Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyVAT")
        Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyTotal")
    End If
    ' If ISA Reg and CRB (Enhanced)
    If (Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmStandard" & LineNumber) = True) Or (Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmEnhanced" & LineNumber) = True) Then
        Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegCOST")
        Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegADMIN")
        Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegVAT")
        Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegTotal")
    End If
    'if Standard CRB Only
    If Me.Controls("AppFrmISAReg" & LineNumber) = False And Me.Controls("AppFrmStandard" & LineNumber) = True Then
        Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureCOST")
        Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureADMIN")
        Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureVAT")
        Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureTotal")
    End If
    'if Enhanced CRB Only
    If Me.Controls("AppFrmISAReg" & LineNumber) = False And Me.Controls("AppFrmEnhanced" & LineNumber) = True Then
        Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureCOST")
        Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureADMIN")
        Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureVAT")
        Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureTotal")
    End If
     'if Enhanced CRB with ISAFirst
    If Me.Controls("AppFrmISAFirst" & LineNumber) = True And Me.Controls("AppFrmEnhanced" & LineNumber) = True Then
        Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureCOST")
        Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureADMIN")
        Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureVAT")
        Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureTotal")
    End If
    'if Volunteer with ISA Reg
    If Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmVolunteer" & LineNumber) = True Then
        Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyCOST")
        Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyADMIN")
        Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyVAT")
        Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyTotal")
    End If
    'if Volunteer Only
    If Me.Controls("AppFrmVolunteer" & LineNumber) = True Then
        Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureCOST")
        Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureADMIN")
        Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureVAT")
        Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureTotal")
    End If
    'if NoCharge Only
    If Me.Controls("AppFrmNoCharge" & LineNumber) = True Then
        Me.Controls("AppFrmCost" & LineNumber) = 0
        Me.Controls("AppFrmAdmin" & LineNumber) = 0
        Me.Controls("AppFrmVAT" & LineNumber) = 0
        Me.Controls("AppFrmTotal" & LineNumber) = 0
    End If
 
    ChargeRatesRecordSet.Close
    Set ChargeRatesRecordSet = Nothing
 
End If
 
'Calculate Totals
 
' reset variables
Counter = 0
Me.TotalCost = 0
Me.TotalVAT = 0
Me.AmountDue = 0
 
For Counter = 1 To 10
Me.TotalCost = Me.TotalCost + Nz(Me.Controls("AppFrmCost" & Counter), 0) + Nz(Me.Controls("AppFrmAdmin" & Counter), 0)
Me.TotalVAT = Me.TotalVAT + Nz(Me.Controls("AppFrmVAT" & Counter), 0)
Me.AmountDue = Me.TotalCost + Me.TotalVAT
Next
 
End Sub
 

[TheSmileyCoder]

I dont see anything problematic in this. How many times are you running the procedure? And how many controls do you have on your form? Are you using several "lines" controls to perform something? Most likely you should be using a subform instead, and use a running total to calculate, or a DSum.

[reginaldmerritt]

The form has 10 lines with a combo box to choose a record from tbCRBApplication. This then writes to the form (tbinvoices) some information from tbCRBApplication via a recordset and then writes to the form some information from tbChargeRates. This creates the invoice. The procedure above gets run everytime the user chages a combobox.

I don't see how a subform would be of use here. There are quite a few fields on the form.

Should i be doing this differently?

Therotically the tbinvoices doesn't need to have data from tables written to it as it can be linked via the PK of the other tables, I took this route encase the data needs to be exported in the future; doing it this way would just make that process easier when it comes to it.

[TheSmileyCoder]

Its always hard to tell how much the the guy asking hte question knows, so I sometimes we have to assume that they don't know much (Sorry).

In a subform you would get each item on its line. If you set the subform to Continues you could have as many (or as few) lines as needed. Each time you "use" the last free line, a new line is automically added. You would also not need to fill in the data through your code. While you can do ALOT with code, I think in general its a bad idea to write code to accomplish something that access can allready do for you.

Do they text boxes on your form have a controlsource? (The boxes on the different lines, not the boxes on the top of the form.)

When the form throws an error, do you see any relation between the number of filled in lines on the form, and how often the error occurs?

I count 14 controls per line, times 10 lines, thats 140 controls, which seems alot to me, especially if they have a control source.

Other then that, I dont really know. Maybe your by accident calling your procedure several times in a row? Try putting a debug.print statement in your code, and see how many times it runs when you do a selection in your main form.

Could you show me the code from which you call the procedure?

[reginaldmerritt]

Yes each control is a field in tbinvoices. 148 fields in total.

This is the code i use to call in the procedure

Private Sub AppFrmNum1_AfterUpdate()
LineNumber = 1
MarkAsAdded
CalculateInovice
End Sub
 
Private Sub AppFrmNum2_AfterUpdate()
LineNumber = 2
MarkAsAdded
CalculateInovice
End Sub
 
ETC ETC
 

Incase you want to know this is the procedure MarkAsAdded which is called in just before Calculateinvoice.

Private Sub MarkAsAdded()
 
Set CRBRecordSet = CurrentDb.OpenRecordset
("Select * FROM CRB 
WHERE [FormNumber] = '" & 
Me.Controls("AppFrmNum" & LineNumber) & "'")
 
CRBRecordSet.Edit
CRBRecordSet("AddedToinvoice") = True
CRBRecordSet("InvoiceNumber") = Me.InvoiceNumber
 
Me.Controls("AppFrmDate" & LineNumber) = 
CRBRecordSet("DateCRBSent")
If (CRBRecordSet("Branch")) <> "" Then 
Me.Controls("AppFrmName" & LineNumber) = 
CRBRecordSet("Title") & " " & CRBRecordSet("Fornames") & " " & 
CRBRecordSet("Surname") & " (" & CRBRecordSet("Branch") & " )"
If (CRBRecordSet("Branch")) = "" Then 
Me.Controls("AppFrmName" & LineNumber) = 
CRBRecordSet("Title") & " " & CRBRecordSet("Fornames") & " " & CRBRecordSet("Surname")
Me.Controls("AppFrmISAReg" & LineNumber) = 
CRBRecordSet("ApplyingForISAReg")
Me.Controls("AppFrmISAFirst" & LineNumber) = 
CRBRecordSet("WantsPOVAFIRST")
Me.Controls("AppFrmStandard" & LineNumber) = CRBRecordSet("Standard")
Me.Controls("AppFrmEnhanced" & LineNumber) = CRBRecordSet("Enhanced")
Me.Controls("AppFrmVolunteer" & LineNumber) = CRBRecordSet("Volunteer")
Me.Controls("AppFrmNoCharge" & LineNumber) = CRBRecordSet("NoCharge")
Me.Controls("AppFrmPrepaid" & LineNumber) = CRBRecordSet("PaiedFor")
 
CRBRecordSet.Close
Set CRBRecordSet = Nothing
 
End Sub
 

[reginaldmerritt]

So do you think that the error message i'm getting is from wrtting to a table through a form via data taken from a recordset. As you so rightly said i could use subforms to simply display the data rather than wrtting it to the table though the form. I wouldn't need to use recordsets, however, i am working on the basis that this data will need to be exported at some point, also i want to make the program automatically create the invoice without using this form, in which case i will be back to using recordsets again.

Do you think the problem simply from too many controls on the form?

[TheSmileyCoder]

I can't seem to find any other issues with your form as such. (Of course I could easily be missing something, its alot of code.)

The only other thing I can think of to try is:

Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("Select Top 1 * FROM ChargeRates WHERE [DateAsOf] <= #" & DateFromForm & "# ORDER BY [DateAsOf] DESC;",dbOpenSnapshot,dbOReadOnly) 
 

I don't really know what the limit is on fields, but 100+ fields sound like alot.

Im also not sure why you want to use code to produce your invoice, when a report with a supreport should be able to display quite nicely what (I think) you will need.

[nico5038]

The limit on tablefields is 255, but when you use long fieldnames it becomes less...

I would advise you to create a new empty database and import (with Get External date / Import) all objects. This will exclude the possibility of database corruption, which is always a no 1 suspect when strange things happen in my database....

Nic;o)

[reginaldmerritt]

Thanks The Smiley One i appreciate your help.

The fields in the recordset are from the tbChargeRates, there are only 30 fields in this table and recordset. I took all the controls of the form apart from one row and the same error message came up.

Sorry when i say produce the invoice though code i meant to look at which clients need invoicing, pick the appropriate products and then run a report; all from a push (or rather a click) of a button.

I'll give the ,dbOpenSnapshot,dbOReadOnly) a go and see if works.

Thanks for the tip nico5038, i'll have to give that a go also.

[reginaldmerritt]

SOLVED!!!

Well that was a long wild goose chase. I've been spending my time going though each section of code using the ' (rem statement marker) to block out lines of code.

Eventually i have found where the error actually is. There error occurs in these 3 lines of code.

CRBRecordSet.Edit
CRBRecordSet("AddedToinvoice") = True
CRBRecordSet("InvoiceNumber") = Me.InvoiceNumber
 

This is from the MarkAsAdded procedure as above in one of my previous posts.

The missing part to the puzzle was

CRBRecordSet.Update

Once i've addded that after editing the recordset i get no error messages.

Does this need to be done every time a recordset is edited?

Thanks for all your help guys

[TheSmileyCoder]

Yes, I believe so.

There is also something called a batchupdate, but I can't remember how exactly that works at the moment.

[reginaldmerritt]

Live an learn. I'll make sure i add .update from now on.

Sorry we went all around the houses there.

[TheSmileyCoder]

Not your fault :P Just glad you found a solution to your problem.

[NeoPa]

@reginaldmerritt
Yes.

Both rs.New & rs.Edit put the database into an edit and therefore create a transaction. This is only closed (submitted to the database) when rs.Update is called. Many changes can be made between the two lines, but none is committed (saved) until the rs.Update tells it to.

In the mean-time, Access needs to keep a record of all the proposed changes. You can imagine how this could build up in your process if the rs.Update is never called.

I suggest a quick read of the relevant Help page (click on the .Edit line and press F1) would considerably help your understanding. They explain it better than I can.