error after "gotorecord" repeats 390 times

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a form that calculates then stores data points. In
the OnCurrent event property of the form, I have visual
basic code determining the data points that I need,
storing the data points in fields, and then going to the
next record once it's done. This loops perfectly, but
only for 390 records. Once it reached 390 records, it
stops, with error 2105, can't go to specified record.
I've tried different things, but can't figure it out. Any
help is appreciated
 
Hi,
It's kind of hard to comment without seeing any code at all.
I assume this a recordset you're looping through?
 
-----Original Message-----
Hi,
It's kind of hard to comment without seeing any code at all.
I assume this a recordset you're looping through?

--
HTH
Dan Artuso, Access MVP





.
Sounds like an EOF error. If you are trying to go to a
Click to expand...
record beyond the end of the recordset, which would happen
if you were using acNext when there is no next record to
go to ... just a guess without seeing code.
 
Thanks for the help, however let me explain further. I'm
working on a recordset of 79,000 records. I expect to run
into an error after 79K records, especially since I've set
the form to "no new records", but I don't know why I'm
running into one after 390 records. The code that I'm
using looks something like this:

ON CURRENT ()
startup:
if a=b then
c=f-a
d=g-a
e=h-a
DOCMD.gotorecord acnext
else
b=a
f=(dlookup(field, table, field=a))
g=(dlookup(field, table, field=a))
h=(dlookup(field, table, field=a))
goto startup
end if
END

I'm using the OnCurrent Event property, with B, F, G, and
H "dim"med into memory. A is the actual record grouping
from the recordset (IE: sales sorted by specific product
lines, each product line as a group), and B is a flag set
in memory to determine what the currently worked group
is. Each time B doesn't match A, it resets F, G, and H
(with the DLookups), then sets B to match A, and returns
to the beginning of the IF..Then Function to create values
for C, D, and E. The values for C, D, and E are stored on
the actual record. The DoCmd.Gotorecord is used to move
to the next record, which again triggers the OnCurrent
Event, and the process starts over.

The error that I'm receiving happens every 390 records
consistently (on record 390, 780, 1170, 1560, 1950, etc).
If I start the process on record 130, it still fails on
record 520, exactly 390 steps later. The only piece in my
code that matches that consistentcy would have to be the
gotorecord statement or the OnCurrent statement, at least
from what I can figure.

The error that I receive is error 2105, saying that I
can't go to the specified record. So what's UP??? Is
there something somewhere that only loads 390 records at a
time, and I'm processing too quickly for it to catch up?
HELP!
 
In case this helps, here is the SQL from the recordsource
query and the VBA code from the form used to trigger the
loop.....

***********************************************

SELECT Roster.Emp_Name, Service_Codes.CommBucket,
Sheet1.Check_In_Date, Sheet1.Accountnbr, Sheet1.Srv_Code,
Plans.table, Roster.Relief, Sheet1.CommissionAmount,
Sheet1.WidgetCount
FROM Plans INNER JOIN ((Sheet1 INNER JOIN Service_Codes ON
Sheet1.Srv_Code = Service_Codes.Srv_Code) INNER JOIN
Roster ON Sheet1.Social_Security_Nbr = Roster.Empl_ID) ON
Plans.Plan = Roster.FT_PT
WHERE (((Service_Codes.CommBucket)>0))
ORDER BY Roster.Emp_Name, Service_Codes.CommBucket,
Sheet1.Check_In_Date, Sheet1.Accountnbr;

***********************************************

Here is also the VBA used in the form's module set:

***********************************************
-----------------------------------
Option Compare Database

Dim Bucket As Byte
Dim RunningSumCount As Single
Dim Thresh_Quota As Single
Dim Goal_Quota As Single
Dim Stretch_Quota As Single
Dim Super_Quota As Single
Dim Thresh_Comm As Currency
Dim Goal_Comm As Currency
Dim Stretch_Comm As Currency
Dim Super_Comm As Currency
Dim Thresh_Pay As Currency
Dim Goal_Pay As Currency
Dim Stretch_Pay As Currency
Dim Super_Pay As Currency
Dim Total_Pay As Currency
-----------------------------------
Private Sub Form_Current()

Startoff:

If [CommBucket] = Bucket Then

RunningSumCount = RunningSumCount + 1

If (RunningSumCount - Thresh_Quota) < 1 And
(RunningSumCount - Thresh_Quota) > 0 Then
Thresh_Pay = (RunningSumCount - Thresh_Quota) *
Thresh_Comm
ElseIf (RunningSumCount - Thresh_Quota) <=
(Goal_Quota - Thresh_Quota) And (RunningSumCount -
Thresh_Quota) >= 1 Then
Thresh_Pay = Thresh_Comm
ElseIf (RunningSumCount - Goal_Quota) < 1 And
(RunningSumCount - Goal_Quota) > 0 Then
Thresh_Pay = (Abs(RunningSumCount - Goal_Quota -
1)) * Thresh_Comm
Else
Thresh_Pay = 0
End If

If (RunningSumCount - Goal_Quota) < 1 And
(RunningSumCount - Goal_Quota) > 0 Then
Goal_Pay = (RunningSumCount - Goal_Quota) *
Goal_Comm
ElseIf (RunningSumCount - Goal_Quota) <=
(Stretch_Quota - Goal_Quota) And (RunningSumCount -
Goal_Quota) >= 1 Then
Goal_Pay = Goal_Comm
ElseIf (RunningSumCount - Stretch_Quota) < 1 And
(RunningSumCount - Stretch_Quota) > 0 Then
Goal_Pay = (Abs(RunningSumCount - Stretch_Quota -
1)) * Goal_Comm
Else
Goal_Pay = 0
End If

If (RunningSumCount - Stretch_Quota) < 1 And
(RunningSumCount - Stretch_Quota) > 0 Then
Stretch_Pay = (RunningSumCount - Stretch_Quota) *
Stretch_Comm
ElseIf (RunningSumCount - Stretch_Quota) <=
(Super_Quota - Stretch_Quota) And (RunningSumCount -
Stretch_Quota) >= 1 Then
Stretch_Pay = Stretch_Comm
ElseIf (RunningSumCount - Super_Quota) < 1 And
(RunningSumCount - Super_Quota) > 0 Then
Stretch_Pay = (Abs(RunningSumCount - Super_Quota -
1)) * Stretch_Comm
Else
Stretch_Pay = 0
End If

If (RunningSumCount - Super_Quota) < 1 And
(RunningSumCount - Super_Quota) > 0 Then
Super_Pay = (RunningSumCount - Super_Quota) *
Super_Comm
ElseIf (RunningSumCount - Super_Quota) > 0.99 Then
Super_Pay = Super_Comm
Else
Super_Pay = 0
End If
Total_Pay = Thresh_Pay + Goal_Pay + Stretch_Pay +
Super_Pay
Else
RunningSumCount = 0

Bucket = CommBucket

Thresh_Quota = Nz(((DLookup("[Thresh_Quota]",
,
& ".id=" & [CommBucket])) * [Relief]) - [Relief])
Goal_Quota = Nz(((DLookup("[Goal_Quota]",
,
& ".id=" & [CommBucket])) * [Relief]) - [Relief])
Stretch_Quota = Nz(((DLookup("[Stretch_Quota]",
,
& ".id=" & [CommBucket])) * [Relief]) -
[Relief])
Super_Quota = Nz(((DLookup("[Super_Quota]",
,
& ".id=" & [CommBucket])) * [Relief]) - [Relief])
Thresh_Comm = Nz((DLookup("[thresh_Commission]",
,
& ".id=" & [CommBucket])))
Goal_Comm = Nz((DLookup("[goal_Commission]",
,
& ".id=" & [CommBucket])))
Stretch_Comm = Nz((DLookup("[stretch_Commission]",
,
& ".id=" & [CommBucket])))
Super_Comm = Nz((DLookup("[super_Commission]",
,
& ".id=" & [CommBucket])))

GoTo Startoff

End If

WidgetCount = RunningSumCount
CommissionAmount = Total_Pay
DoCmd.GoToRecord acDataForm, "Form1", acNext

End Sub
-----------------------------------
Private Sub Form_Open(Cancel As Integer)
Bucket = 0
End Sub

***********************************************
 
Back
Top