Looping problem...requery issue?

  • Thread starter Thread starter Debralous
  • Start date Start date
D

Debralous

I have a function that is supposed to loop through a table and update
the information in fields based on query results in a data set. I am
stumped as to why it doesn't work. Here is the section of code that
is giving me trouble:

sqlstr2 = "select * from HBPCHRG where BGCTAC = " &
rstClientInfo.Fields("mmacct")
If rstPayorInfo.State = 0 Then
rstPayorInfo.Open sqlstr2, connHNWM, adOpenDynamic,
adLockOptimistic
else
rstPayorInfo.Requery sqlstr2 (I've tried with and without the
sqlstr2 argument)
End If

If Not rstPayorInfo.EOF Then
rstMain.Fields("Payor Code #1") =
rstPayorInfo.Fields("BGCUB1")
rstMain.Fields("Plan Code #1") =
rstPayorInfo.Fields("BGCPL1")
rstMain.Fields("Payor Code #2") =
rstPayorInfo.Fields("BGCUB2")
rstMain.Fields("Plan Code #2") =
rstPayorInfo.Fields("BGCPL2")
rstMain.Fields("Payor Code #3") =
rstPayorInfo.Fields("BGCUB3")
rstMain.Fields("Plan Code #3") =
rstPayorInfo.Fields("BGCPL3")
End If

I'm omitting the extraneous code as the function does work, it just
returns the "wrong" values. "Main" is the table I'm trying to update.
I'm using values from the query of another database to get the values
for the update.

When I try to debug it does appear that I am successfully moving to the
next record; when I mouse over the rstClientInfo.Fields("mmacct") I get
the correct (next) value and when I mouse over sqlstr2 I see the
correct query statement with the "next" value but the
rstPayroInfo.Fields data retains the values from the first pass
through...essentially updating all records in the table with the values
from the first record.

Maybe Requery isn't right for what I'm trying to do? I need to be able
to loop through this and update table without closing and reopening the
recordset every time, which is the way the person who originally wrote
this had it working and it DOES work that way, however, the file is
large and that just takes too long (30 minutes or more).

Any help, suggestions are appreciated!!
 
I have a function that is supposed to loop through a table and update
the information in fields based on query results in a data set. I am
stumped as to why it doesn't work. Here is the section of code that
is giving me trouble:

sqlstr2 = "select * from HBPCHRG where BGCTAC = " &
rstClientInfo.Fields("mmacct")
If rstPayorInfo.State = 0 Then
rstPayorInfo.Open sqlstr2, connHNWM, adOpenDynamic,
adLockOptimistic
else
rstPayorInfo.Requery sqlstr2 (I've tried with and without the
sqlstr2 argument)
End If

If Not rstPayorInfo.EOF Then
rstMain.Fields("Payor Code #1") =
rstPayorInfo.Fields("BGCUB1")
rstMain.Fields("Plan Code #1") =
rstPayorInfo.Fields("BGCPL1")
rstMain.Fields("Payor Code #2") =
rstPayorInfo.Fields("BGCUB2")
rstMain.Fields("Plan Code #2") =
rstPayorInfo.Fields("BGCPL2")
rstMain.Fields("Payor Code #3") =
rstPayorInfo.Fields("BGCUB3")
rstMain.Fields("Plan Code #3") =
rstPayorInfo.Fields("BGCPL3")
End If
Click to expand...

Two observations:

You're never doing a MoveNext on rstPayorInfo. Therefore it's always
on the first record.

Secondly, you're doing something programmatically which can probably
be done in a very simple Update query joining the two tables; no
looping required! Am I misunderstanding?

John W. Vinson[MVP]
 
John said:
You're never doing a MoveNext on rstPayorInfo. Therefore it's always
on the first record.
Click to expand...

There is nothing to MoveNext to...my Select statement retrieves only
one record; the record that matches the account number of the row in
the table I am looping through. That is why I want to re-run the query
with the "next" account number as I loop through the table.

Secondly, you're doing something programmatically which can probably
be done in a very simple Update query joining the two tables; no
looping required! Am I misunderstanding?
Click to expand...

I considered that however because there are actually 4 tables involved
in the update (this is only one piece) and this function was already
written and functioning, albiet not efficiently, I didn't want to scrap
it and start from scratch.

John W. Vinson[MVP]
 
Gerwin said:
Try something like this:

sqlstr2 = "select * from HBPCHRG"

If rstPayorInfo.State = 0 Then
rstPayorInfo.Open sqlstr2, connHNWM, adOpenDynamic, adLockOptimistic
endif

do while not rstPayorInfo.eof
rstPayorInfo.findfirst "[BGCTAC]=" & rstClientInfo.Fields
if rstpayorInfo.nomatch=false then
rstMain.Fields("Payor Code #1") = rstPayorInfo.Fields("BGCUB1")
rstMain.Fields("Plan Code #1") = rstPayorInfo.Fields("BGCPL1")
rstMain.Fields("Payor Code #2") = rstPayorInfo.Fields("BGCUB2")
rstMain.Fields("Plan Code #2") = rstPayorInfo.Fields("BGCPL2")
rstMain.Fields("Payor Code #3") = rstPayorInfo.Fields("BGCUB3")
rstMain.Fields("Plan Code #3") = rstPayorInfo.Fields("BGCPL3")
End If
loop
Click to expand...

Thanks, This makes sense, although i still don't get why the requery
doesn't work...I will give your solution a try. Thanks again!!
 
Back
Top