Struggling with Recordsets and .eof

[Derek Wittman]

Good morning
I've got a small problem (well, it's probably small). I keep getting an error message: Runtime 3420 Object Invalid or No Longer Set... I cannot see the cause in the code. Can someone please take a gander at it and offer some pointers

Thank you

Sub CigLogic(

Dim dbs As DAO.Database, rstCumArm1 As DAO.Recordse
Dim curInter, curToCase, cur6s, cur2s, cur1s As Doubl
Dim previnter, curCumArm, nextCumArm As Intege

DoCmd.RunSQL ("CREATE Table CumArm1(Cumarm double null, Inter double null, ToCase double null, 6s double null, 2s double null, 1s double null)"

'Following line creates an empty arm to hold things at the beginning of a shif
DoCmd.RunSQL ("INSERT INTO CumArm1 (Cumarm, Inter, ToCase, 6s, 2s, 1s) Values (0,0,0,0,0,0)"

'Following line copies my independant field (all records) into the table created above
'Since I'll be working on aggregate functions, I'd rather work in a table than keep hitting my query
DoCmd.RunSQL ("INSERT INTO CumArm1 ( Cumarm ) SELECT qryCumArm.CumArm FROM qryCumArm"

'Setting up the recordse
Set dbs = CurrentD
Set rstCumArm1 = dbs.OpenRecordset("CumArm1", dbOpenTable
With rstCumArm
.MoveLas
.MoveFirst 'my record of 0's. I'm not working calculations on this one
While Not .EOF 'starting my loop (supposedly...
.MoveNext ' going to my first 'real' data value (Note: Record X is current
curInter = 0 'reset curInte
.MovePrevious
previnter = !inter 'put (X-1) value of 'inter' field to previnte
.MoveNex
.MoveNex
nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumAr
.MovePreviou
If previnter + !CumArm + nextCumArm > 12 Then 'start making decisions - if TRUE then start putting from arm into cas
curInter =
curToCase = previnter + !CumAr
cur6s = Int(curToCase / 6
cur2s = Int((curToCase - cur6s * 6) / 2
cur1s = curToCase - 6 * cur6s - 2 * cur2
Else: curInter = previnter + !CumArm 'since it's not TRUE then continue to accummulate into ar
curToCase =
cur6s =
cur2s =
cur1s =
End I

.Edit 'time to start updating the recordset to the held variable value
!inter = curInte
!tocase = curToCas
![6s] = cur6
![2s] = cur2
![1s] = cur1
.Update 'time to move the values from the recordset to the datatabl
.Clos
Wend 'supposedly to go back to the WHILE NOT star
End Wit
Set dbs = Nothin
End Su

I'm dizzy. I really appreciate any help offered, even links to informative sites. The odd thing is that I have checked out is ng and it seems (to me, anyway) that I'm following the demonstrated examples

Thank you in advance
Derek

 

[Derek Wittman]

Duh... I figured it out. Seems that the .close statement needs to be AFTER the loop. Not IN the loop..

The other thing I learned was that if there was a problem with the .movenext line that took the recordset beyond the .eof marker from WITHIN the loop, I needed an out from there as well.

So, I replaced
nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumAr

with
If .EOF The
nextCumArm =
Else: nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumAr
End I

and the code worked like a charm. Thanks again to Tom Ellison and Cheryl Fischer, both MVPs, for giving me enough information to be dangerous and enough encouragement to be safe. Of course, I still need to work on my code commenting motivation

Thank you all again
Dere

----- Derek Wittman wrote: ----

Good morning
I've got a small problem (well, it's probably small). I keep getting an error message: Runtime 3420 Object Invalid or No Longer Set... I cannot see the cause in the code. Can someone please take a gander at it and offer some pointers

Thank you

Sub CigLogic(

Dim dbs As DAO.Database, rstCumArm1 As DAO.Recordse
Dim curInter, curToCase, cur6s, cur2s, cur1s As Doubl
Dim previnter, curCumArm, nextCumArm As Intege

DoCmd.RunSQL ("CREATE Table CumArm1(Cumarm double null, Inter double null, ToCase double null, 6s double null, 2s double null, 1s double null)"

'Following line creates an empty arm to hold things at the beginning of a shif
DoCmd.RunSQL ("INSERT INTO CumArm1 (Cumarm, Inter, ToCase, 6s, 2s, 1s) Values (0,0,0,0,0,0)"

'Following line copies my independant field (all records) into the table created above
'Since I'll be working on aggregate functions, I'd rather work in a table than keep hitting my query
DoCmd.RunSQL ("INSERT INTO CumArm1 ( Cumarm ) SELECT qryCumArm.CumArm FROM qryCumArm"

'Setting up the recordse
Set dbs = CurrentD
Set rstCumArm1 = dbs.OpenRecordset("CumArm1", dbOpenTable
With rstCumArm
.MoveLas
.MoveFirst 'my record of 0's. I'm not working calculations on this one
While Not .EOF 'starting my loop (supposedly...
.MoveNext ' going to my first 'real' data value (Note: Record X is current
curInter = 0 'reset curInte
.MovePrevious
previnter = !inter 'put (X-1) value of 'inter' field to previnte
.MoveNex
.MoveNex
nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumAr
.MovePreviou
If previnter + !CumArm + nextCumArm > 12 Then 'start making decisions - if TRUE then start putting from arm into cas
curInter =
curToCase = previnter + !CumAr
cur6s = Int(curToCase / 6
cur2s = Int((curToCase - cur6s * 6) / 2
cur1s = curToCase - 6 * cur6s - 2 * cur2
Else: curInter = previnter + !CumArm 'since it's not TRUE then continue to accummulate into ar
curToCase =
cur6s =
cur2s =
cur1s =
End I

.Edit 'time to start updating the recordset to the held variable value
!inter = curInte
!tocase = curToCas
![6s] = cur6
![2s] = cur2
![1s] = cur1
.Update 'time to move the values from the recordset to the datatabl
.Clos
Wend 'supposedly to go back to the WHILE NOT star
End Wit
Set dbs = Nothin
End Su


I'm dizzy. I really appreciate any help offered, even links to informative sites. The odd thing is that I have checked out is ng and it seems (to me, anyway) that I'm following the demonstrated examples

Thank you in advance
Derek

 

[Cheryl Fischer]

Derek,

Thanks for your kind comments and good for you for having figured out the
problem.

Let me offer a suggestion "for future reference" which has to do with how
you are declaring your variables. You used the following in your code:

Dim curInter, curToCase, cur6s, cur2s, cur1s As Double
Dim previnter, curCumArm, nextCumArm As Integer

Are you aware that in the first line only "cur1s" will be double and in the
second line only "nextCumArm" will be integer? All of the other variables
will be variants. A better coding practice would be:

Dim curInter as Double, curToCase as Double, cur6s as Double, cur2s as
Double, cur1s As Double
Dim previnter as Integer, curCumArm as Integer, nextCumArm As Integer


hth,


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Duh... I figured it out. Seems that the .close statement needs to be

AFTER the loop. Not IN the loop...

The other thing I learned was that if there was a problem with the

..movenext line that took the recordset beyond the .eof marker from WITHIN
the loop, I needed an out from there as well.

So, I replaced:
nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumArm

with:
If .EOF Then
nextCumArm = 0
Else: nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumArm
End If

and the code worked like a charm. Thanks again to Tom Ellison and Cheryl

Fischer, both MVPs, for giving me enough information to be dangerous and
enough encouragement to be safe. Of course, I still need to work on my code
commenting motivation.

Thank you all again!
Derek


----- Derek Wittman wrote: -----

Good morning,
I've got a small problem (well, it's probably small). I keep getting

an error message: Runtime 3420 Object Invalid or No Longer Set... I cannot
see the cause in the code. Can someone please take a gander at it and offer
some pointers?

Thank you!

Sub CigLogic()

Dim dbs As DAO.Database, rstCumArm1 As DAO.Recordset
Dim curInter, curToCase, cur6s, cur2s, cur1s As Double
Dim previnter, curCumArm, nextCumArm As Integer

DoCmd.RunSQL ("CREATE Table CumArm1(Cumarm double null, Inter double

null, ToCase double null, 6s double null, 2s double null, 1s double null)")

'Following line creates an empty arm to hold things at the beginning of a shift
DoCmd.RunSQL ("INSERT INTO CumArm1 (Cumarm, Inter, ToCase, 6s, 2s, 1s) Values (0,0,0,0,0,0)")

'Following line copies my independant field (all records) into the table created above.
'Since I'll be working on aggregate functions, I'd rather work in a

table than keep hitting my query.

DoCmd.RunSQL ("INSERT INTO CumArm1 ( Cumarm ) SELECT qryCumArm.CumArm FROM qryCumArm")

'Setting up the recordset
Set dbs = CurrentDb
Set rstCumArm1 = dbs.OpenRecordset("CumArm1", dbOpenTable)
With rstCumArm1
.MoveLast
.MoveFirst 'my record of 0's. I'm not working calculations on this one.
While Not .EOF 'starting my loop (supposedly...)
.MoveNext ' going to my first 'real' data value (Note: Record X is current)
curInter = 0 'reset curInter
.MovePrevious
previnter = !inter 'put (X-1) value of 'inter' field to previnter
.MoveNext
.MoveNext
nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumArm
.MovePrevious
If previnter + !CumArm + nextCumArm > 12 Then 'start making

decisions - if TRUE then start putting from arm into case

curInter = 0
curToCase = previnter + !CumArm
cur6s = Int(curToCase / 6)
cur2s = Int((curToCase - cur6s * 6) / 2)
cur1s = curToCase - 6 * cur6s - 2 * cur2s
Else: curInter = previnter + !CumArm 'since it's not TRUE

then continue to accummulate into arm

curToCase = 0
cur6s = 0
cur2s = 0
cur1s = 0
End If

.Edit 'time to start updating the recordset to the held variable values
!inter = curInter
!tocase = curToCase
![6s] = cur6s
![2s] = cur2s
![1s] = cur1s
.Update 'time to move the values from the recordset to the datatable
.Close
Wend 'supposedly to go back to the WHILE NOT start
End With
Set dbs = Nothing
End Sub


I'm dizzy. I really appreciate any help offered, even links to

informative sites. The odd thing is that I have checked out is ng and it
seems (to me, anyway) that I'm following the demonstrated examples.

 

[Derek Wittman]

Thanks again for your help. I did not know that these would be variants. Another thing I learned for the day. My goal is to learn something new every day from each of several areas - I'm about 3 weeks ahead (mostly small stuff) on the technical, and even about a week ahead on the legal (I'm working on negotiation for an equipment contract), so I think I get to go home now. (I wish).

I will certainly do a better job of delaring my variables. I hadn't been declaring at all, but when I ran the performance analyzer, the suggestion was "Option Explicit"... So, I'm working to improve

The nice thing about overcoming the runtime errors and learning it on my own, as you already probably know, is that I'm gaining self-suffiency. Of course, to ANY Microsoft tool, I'm still nowhere near the capacity of functionality that's offered..

Have a great weekend - and yes, I may be back by the end of the day
Dere

Thanks again, Cheryl
Dere

----- Cheryl Fischer wrote: ----

Derek

Thanks for your kind comments and good for you for having figured out th
problem

Let me offer a suggestion "for future reference" which has to do with ho
you are declaring your variables. You used the following in your code

Dim curInter, curToCase, cur6s, cur2s, cur1s As Doubl
Dim previnter, curCumArm, nextCumArm As Intege

Are you aware that in the first line only "cur1s" will be double and in th
second line only "nextCumArm" will be integer? All of the other variable
will be variants. A better coding practice would be

Dim curInter as Double, curToCase as Double, cur6s as Double, cur2s a
Double, cur1s As Doubl
Dim previnter as Integer, curCumArm as Integer, nextCumArm As Intege


hth


-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

Duh... I figured it out. Seems that the .close statement needs to b

AFTER the loop. Not IN the loop....movenext line that took the recordset beyond the .eof marker from WITHI
the loop, I needed an out from there as well

nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumAr
If .EOF The
nextCumArm =
Else: nextCumArm = !CumArm 'put (X+1) value of CumArm t nextCumAr
End I

Fischer, both MVPs, for giving me enough information to be dangerous an
enough encouragement to be safe. Of course, I still need to work on my cod
commenting motivation

I've got a small problem (well, it's probably small). I keep gettin

an error message: Runtime 3420 Object Invalid or No Longer Set... I canno
see the cause in the code. Can someone please take a gander at it and offe
some pointers

Dim curInter, curToCase, cur6s, cur2s, cur1s As Doubl
Dim previnter, curCumArm, nextCumArm As Intege of a shif
DoCmd.RunSQL ("INSERT INTO CumArm1 (Cumarm, Inter, ToCase, 6s, 2s 1s) Values (0,0,0,0,0,0)" table created above
'Since I'll be working on aggregate functions, I'd rather work in

table than keep hitting my query.

DoCmd.RunSQL ("INSERT INTO CumArm1 ( Cumarm ) SELECT qryCumArm.CumArm FROM qryCumArm")
Set dbs = CurrentDb
Set rstCumArm1 = dbs.OpenRecordset("CumArm1", dbOpenTable)
With rstCumArm1
.MoveLast
.MoveFirst 'my record of 0's. I'm not working calculations on this one.
While Not .EOF 'starting my loop (supposedly...)
.MoveNext ' going to my first 'real' data value (Note: Record X is current)
curInter = 0 'reset curInter
.MovePrevious
previnter = !inter 'put (X-1) value of 'inter' field to previnter
.MoveNext
.MoveNext
nextCumArm = !CumArm 'put (X+1) value of CumArm to nextCumArm
.MovePrevious
If previnter + !CumArm + nextCumArm > 12 Then 'start making

decisions - if TRUE then start putting from arm into case

curInter = 0
curToCase = previnter + !CumArm
cur6s = Int(curToCase / 6)
cur2s = Int((curToCase - cur6s * 6) / 2)
cur1s = curToCase - 6 * cur6s - 2 * cur2s
Else: curInter = previnter + !CumArm 'since it's not TRUE

then continue to accummulate into arm

curToCase = 0
cur6s = 0
cur2s = 0
cur1s = 0
End If

.Edit 'time to start updating the recordset to the held

variable values
!inter = curInter
!tocase = curToCase
![6s] = cur6s
![2s] = cur2s
![1s] = cur1s
.Update 'time to move the values from the recordset to the datatable
.Close
Wend 'supposedly to go back to the WHILE NOT start
End With
Set dbs = Nothing
End Sub

informative sites. The odd thing is that I have checked out is ng and it
seems (to me, anyway) that I'm following the demonstrated examples.