.MoveNext not working

L

laavista

I have two tables-- t_credits and t_CreditsUsed. I’m checking to see if
any credits were entered in the table t_credits, and if so, I’m adding a
record in t_CreditsUsed with the appropriate info. My “movenext†is not
working. It goes through the first record fine, but does not move to the 2nd
record in the t_credits table.

Help?!

The code:


Set db = CurrentDb
Set rstTCredits = db.OpenRecordset("t_Credits", dbOpenDynaset)
Set rstCreditsUsed = db.OpenRecordset("t_CreditsUsed", dbOpenDynaset)

longNumRecords = DCount("*", "t_credits")

If longNumRecords < 1 Then
' ' there's no credits in credits table
GoTo Resumeit
End If

rstTCredits.MoveFirst

intTempCreditsApplied = rstTCredits!TEMP_Credits_Applied
longintCreditsID = rstTCredits!CreditsID

intCounter = 1

For intCounter = 1 To longNumRecords

If intTempCreditsApplied < 1 Then
GoTo GetNextRecord
End If

rstCreditsUsed.AddNew

rstCreditsUsed!CreditsID = longintCreditsID
rstCreditsUsed!CreditsUsed = intTempCreditsApplied
rstCreditsUsed!TripsReservID = longintTripsReservid

rstCreditsUsed.Update
GetNextRecord:

rstTCredits.MoveNext

Next

…
THANKS for your help.
 
J

Jeff Boyce

One way to trouble-shoot this code is to add a breakpoint, so you'll be able
to step through it, line by line, to see what values are being used.

If I had to guess, I'd guess that your loop counter is set to 1.

By the way, there's no need to count the number of records in the recordset.
You could use a Do...While statement instead for For... Next.

By the way, #2, you can find out how many records are in the recordset by
starting at the beginning, using .movelast and .recordcount, then moving
back to the beginning.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
L

laavista

I have been using breakpoints and I go through line-by-line, but everything
looks OK. My loop counter is incrementing correctly. The movenext is just
not moving to the next record in t_credits table. Could it be because I've
just updated a different table (t_creditsused)?

Help!
 
L

laavista

I tried looping a different way. Same result. IF I take out the code to
add a new record to the 2nd recordset (rstCreditsUsed), the movenext works in
the 1st records (rstTCredits). After I add a record and update it in
rstCreditsUsed, then the rstTCredits.movenext does not work.

Any suggestions??

(Thanks in advance for your time).
 
D

David W. Fenton

By the way, #2, you can find out how many records are in the
recordset by starting at the beginning, using .movelast and
.recordcount, then moving back to the beginning.
Click to expand...

Actually, in the code example, it doesn't look to me as though an
accurate count of the number of records is needed -- all that's
needed is to know if any records are returned at all. In DAO, a
recordset's .RecordCount will never return 0 if there are any
records at all. It might return 1 when there are actual 1 million
records, but it will always return at least 1 when there are any
records. So all you have to check is whether or not .RecordCount is
0.
 
J

Jeff Boyce

Sorry, nothing else occurs to me ...

Hopefully one of the other newsgroup readers has run across this before.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

David W. Fenton

in
I would rewrite your code this:

Set db = CurrentDb
Set rstTCredits = db.OpenRecordset("t_Credits", dbOpenDynaset)
Set rstCreditsUsed = db.OpenRecordset("t_CreditsUsed",
dbOpenDynaset)

If rstTCredits.RecordCount <> 0 Then
rstTCredits.MoveFirst
intTempCreditsApplied = rstTCredits!TEMP_Credits_Applied
longintCreditsID = rstTCredits!CreditsID
Do Until rstCreditsUsed.EOF
If intTempCreditsApplied > 0 Then
rstCreditsUsed.AddNew
rstCreditsUsed!CreditsID = longintCreditsID
rstCreditsUsed!CreditsUsed = intTempCreditsApplied
rstCreditsUsed!TripsReservID = longintTripsReservid
rstCreditsUsed.Update
End If
rstTCredits.MoveNext
Loop
End If

There is an apparent logic problem, though. It seems to me that,
since you're moving to a new record in rstTCredits after you append
each record to rstCreditsUsed that you need to re-assign the values
of intTempCreditsApplied and longintCreditsID after the
rstTCredits.MoveNext (or before the rstCreditsUsed.AddNew. Thus, it
could be rewritten thus:

If rstTCredits.RecordCount <> 0 Then
rstTCredits.MoveFirst
Do Until rstCreditsUsed.EOF
intTempCreditsApplied = rstTCredits!TEMP_Credits_Applied
longintCreditsID = rstTCredits!CreditsID
If intTempCreditsApplied > 0 Then
rstCreditsUsed.AddNew
rstCreditsUsed!CreditsID = longintCreditsID
rstCreditsUsed!CreditsUsed = intTempCreditsApplied
rstCreditsUsed!TripsReservID = longintTripsReservid
rstCreditsUsed.Update
End If
rstTCredits.MoveNext
Loop
End If

I'd skip the variables myself and just do this:

If rstTCredits.RecordCount <> 0 Then
rstTCredits.MoveFirst
Do Until rstCreditsUsed.EOF
intTempCreditsApplied = rstTCredits!TEMP_Credits_Applied
longintCreditsID = rstTCredits!CreditsID
If intTempCreditsApplied > 0 Then
rstCreditsUsed.AddNew
rstCreditsUsed!CreditsID = rstTCredits!CreditsID
rstCreditsUsed!CreditsUsed =
rstTCredits!TEMP_Credits_Applied
rstCreditsUsed!TripsReservID = rstTCredits!TripsReservid
rstCreditsUsed.Update
End If
rstTCredits.MoveNext
Loop
End If

I'm also noting that your code as posted does not assign a value to
longintTripsReservid, so I'm guessing it's a field in rstTCredits,
and have changed the code to reflect that guess.

And, all that said, I'm not certain why you can't just execute this
APPEND query:

INSERT INTO t_CreditsUsed ( CreditsID , CreditsUsed , TripsReservID
) SELECT t_Credits.CreditsID , t_Credits.CreditsUsed ,
t_Credits.TripsReservID FROM t_Credits;

Assuming that TripsReservedID is a field in t_Credits, that's all
you're doing, but you're doing it the hard, inefficient way. It's
much easier, requires much less code and will complete much, much
more quickly than walking through the t_Credits recordset and
appending one record at a time to t_CreditsUsed.

But, your code may be incomplete, and so your approach may be doing
things that the append does not do.
 
L

laavista

I am so impressed!

THANK YOU for taking the time to post this. WOW. This REALLY helped.
May I be able to return the favor some day when someone needs help.
Thanks again.
 
Top