error 2105, can't "go" using Gotorecord, fails on loop 390

[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

 

[Scott]

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

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

 

[Albert D. Kallal]

I would have to say that using a form to "loop" and trying do data
processing by having a form go to the next record is not going to work at
all.

How does the loop know when to stop? What happens if a user bumps a key, or
clicks on a field while this is happening. What record will the mouse click
go to ?

You can not reliability run a loop to force a form to move to the next
record and expect anything in controlled way to happen.

I would suggest that you move out that code out to a module, and start
build-up a series of sql statements to make your update. You could also use
reocrdset, and a loop to process the data.

You will never get away using the acNext command in a loop. Heck, how do you
even expect the code to stop?

The basic code loop to process a reocrdset is:

dim rstRecords as dao.recordset
dim strSql as string

strSql = "select * from tblCustomers with city = 'NY'"

set rstRecs = currentdb.OpenRecordSet(strsql)

do while rstRecs.EOF = false

rstRec.Edit
rstRec!City = "New York"
rstRec.Update
rstRec.MoveNext

loop

rstRec.Close
set rstRec = nothing

The above course would change all city fields from NY to New York. However,
a single sql statement is even better, and less code. You could use:

currentdb.Execute "update tblCustomers set City = 'New York'" & _
"where City = 'NY'"

So, use one of the approaches in a code module, and forget the loop/form
thing.

You do NOT want to try and do some record processing by tricking a form into
going to the next record, it just don't work that way.

 

[Scott]

As much as I appreciate and am open to the constructive
criticism, I am still in some need for help at this
point. Perhaps if I explain the barriers, you can provide
me a solution. Here goes....

First, yes, I am sure at least MOST of the parameters that
I am trying to encompass can be dictated through SQL,
however, there's one parameter that is beyond my grasp for
SQL. I need a running sum. I need a running count of
fields in my table, grouped by three elements (codes
sorted by people, by date, and by category). Each
grouping would restart the running sum count from 1.

(Susie sells 300 services, 30 each of 10 different
categories. The services are sorted and numbered 1-30, 1-
30, 1-30, etc)

Without this number, I cannot calculate the calculation
that I am after. Originally, the required information was
calculated over the group of records, which didn't need
this. It now needs to be calculated PER RECORD. What
code can I write that can produce and store this running
sum?

Secondly, as far as using a form and the GotoRecord, I am
still confused? Yes, stopping, starting, clicking, etc
are all of issue, and I agree it's not the smartest idea
in the shed, but that doesn't answer why the error is
being generated in the first place? Obviously the idea
sucks, otherwise it wouldn't work!

I set up a test, taking 79,000 records in a table,
creating a form, and setting up only the OnCurrent event
with "Docmd.GoToRecord , , acNext". Like clockwork, every
424 records created an error, stating "can't go to
specified record".... So, more records than 390, but
still the same results.... WHY?? What's happening?


I will be applying your loop functionality. Thank you
very much for that. Hopefully you have some enlightenment
that I am not seeing with the running sum. Please let me
know!


sw

 

[Albert D. Kallal]

Likely what is happening is that you have the code being called in the on
current.

So, when you execute a acNext, the code actually starts running AGAIN from
the top. However, the existing copy is still running! On current means that
the code will be called from the START (top) each time you move to a next
record.

However, in addition to the acnext, you also have a goto start. So, your
existing copy of the code has not exited yet! You are actually STILL running
that code from the first record. When the acNext executes, you move to the
next record, and on current calls the code again (but you have not yet
exited the first instance). So the code is re-entered from the top, and this
process is continued EACH TIME. So, you then do a acNext in that instance,
and again that will cause another copy to be loaded. I think that code is
still running from the FIRST INSTANCE of the record. What this means is you
likely have 390 copies of the code running before the routine can even exit.
You are likely running out of stack memory. On current means that the code
is called each time, but you also have a goto the top of that code, and so
the code is NOT being exited.

How it looks right now is that movement to each new record will start a new
copy from the top, but never exits.