Multiple AddNew on Recordset

[Guest]

Greetings.

I'm writing an application to register 2-person relay teams for a 10 mile run.
But whenever I run my code to add both people to the table of runners,
VBA returns the following error:

"Number of rows with pending changes exceeded the limit"

Here's the code that I wrote to add both runners to the table:

With rst
.Open "runners", CurrentProject.Connection, adOpenDynamic,
adLockBatchOptimistic

.AddNew
.Fields("Rank") = Me.Rank1
.Fields("Last Name") = Me.[Last Name1]
.Fields("First Name") = Me.[First Name1]
.Fields("Sex") = Me.Sex1
.Fields("Age") = Me.Age1
.Fields("Unit") = Me.Unit
.Fields("Team Name") = Me.Team

.AddNew
.Fields("Rank") = Me.Rank2
.Fields("Last Name") = Me.[Last Name2]
.Fields("First Name") = Me.[First Name2]
.Fields("Sex") = Me.Sex2
.Fields("Age") = Me.Age2
.Fields("Unit") = Me.Unit
.Fields("Team Name") = Me.Team

.UpdateBatch
.Close
End With

What am I dong wrong here? What must I change to add
both runners to my runners table?

Regards,

Charles

 

[Guest]

Hi

I don't know a lot about this but, I believe the recordset's CursorLocation
setting should be set to adUseClient to enable it to be opened in batch mode?

So try adding the .CursorLocationb as follows:

With rst
.CursorLocation = adUseClient
.Open "runners", CurrentProject.Connection, adOpenDynamic,
adLockBatchOptimistic
................... etc.

 

[Guest]

Thanks. That worked.

Hi

I don't know a lot about this but, I believe the recordset's CursorLocation
setting should be set to adUseClient to enable it to be opened in batch mode?

So try adding the .CursorLocationb as follows:

With rst
.CursorLocation = adUseClient
.Open "runners", CurrentProject.Connection, adOpenDynamic,
adLockBatchOptimistic
.................. etc.

Greetings.

I'm writing an application to register 2-person relay teams for a 10 mile run.
But whenever I run my code to add both people to the table of runners,
VBA returns the following error:

"Number of rows with pending changes exceeded the limit"

Here's the code that I wrote to add both runners to the table:

With rst
.Open "runners", CurrentProject.Connection, adOpenDynamic,
adLockBatchOptimistic

.AddNew
.Fields("Rank") = Me.Rank1
.Fields("Last Name") = Me.[Last Name1]
.Fields("First Name") = Me.[First Name1]
.Fields("Sex") = Me.Sex1
.Fields("Age") = Me.Age1
.Fields("Unit") = Me.Unit
.Fields("Team Name") = Me.Team

.AddNew
.Fields("Rank") = Me.Rank2
.Fields("Last Name") = Me.[Last Name2]
.Fields("First Name") = Me.[First Name2]
.Fields("Sex") = Me.Sex2
.Fields("Age") = Me.Age2
.Fields("Unit") = Me.Unit
.Fields("Team Name") = Me.Team

.UpdateBatch
.Close
End With

What am I dong wrong here? What must I change to add
both runners to my runners table?

Regards,

Charles