Hello everyone, I am writing a payroll program that will check between billing codes and payroll codes. I am running an edit, which is a process that compares the billing data and payroll data, and prints out a report of what errors it has found. The program checks between a table, that has approximately 500 - 600 records, and will open it using an ADODB recordset, and make changes. The program is working fine, but towards the end I get runtime error.
Key Column information is insufficient or incorrect. Too many rows were affected by update.
I have tried changing the cursor type, and still I get the same error. I don't know if there is a maximum amount of records I can update at one time, and I am doing too many or what. Anyone that knows how to fix this please help.
Here is some of the sample code I have wehre the process is taking place. I researched this error, and apparently it is mainly if I have too many recordsets open at once, but I have only one recordset open while this commmand is in effect.
Private Sub CmdRun_Click()
CmdRun.Visible = False
Dim check, RecCountPR, RecCountEM As Double
Dim ProvNum, clinum, Rhrs, Phrs, ecode, BillingCode As String
Dim DateDay As String
Dim DTday As String
Dim DTmon As String
Dim BillingCodeCheck As Boolean
Dim CurRecord As Double
Dim CheckDate As Double
Dim ResetClientHours As Double
Dim ResetProvHours As Double
RecCountPR = PR2000.RecordCount
PrgBar.Max = PR2000.RecordCount
BillingCodeCheck = False
CurRecord = 0
Label2.Caption = "Reseting Payroll Error Codes"
If RecCountPR > 0 Then '---------- 1
Do While PR2000.EOF = False
PrgBar.Visible = True
CurRecord = CurRecord + 1 '<-- Counting Current Record
PrgBar.Value = CurRecord '<-- setting the Current Value to Progress Bar
If IsNull(PR2000.Fields("Codes").Value) Then
PR2000.Fields("Codes").Value = " "
End If '-------------------------- 1
I am also including my ADODB connection string that I have placed as a global function in a module, so that I will not need to type out the entire connection string each time I need to open it.
'Checking for instance of open connection, if open it will close
If PayrollDB.State = adStateOpen Then
PayrollDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=f:databasesPayroll.mdb"
' Temporary Payroll Table for Formatting
If PR2000.State = adStateOpen Then
'setting cursor and open information for recordset table
.ActiveConnection = PayrollDB
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "select * from 2000PR"
Someone please help, thank you.