Looping problem...requery issue?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!!

.

Relevant Pages

  • RE: Looping problem...requery issue?
    ... Between end if and loop insert te following line: ... "Gerwin Berentschot" wrote: ... rstPayorInfo.Open sqlstr2, connHNWM, adOpenDynamic, adLockOptimistic ... I'm using values from the query of another database to get the values ...
    (microsoft.public.access.formscoding)
  • Re: Looping problem...requery issue?
    ... the information in fields based on query results in a data set. ... rstPayorInfo.Requery sqlstr2 (I've tried with and without the ... on the first record. ...
    (microsoft.public.access.formscoding)
  • RE: Looping problem...requery issue?
    ... rstPayorInfo.Open sqlstr2, connHNWM, adOpenDynamic, adLockOptimistic ... I'm using values from the query of another database to get the values ... to loop through this and update table without closing and reopening the ...
    (microsoft.public.access.formscoding)
  • Re: Sequential Time and Cumulative Counts
    ... with the current SQL Server editions. ... On a large data set all set based ... I ended up doing a while loop in place of your query from ...
    (comp.databases.ms-sqlserver)
  • Re: LOOP WITHIN A LOOP
    ... Assuming you can define a set of forbidden values, a Query is probably the easiest way to go. ... But your criteria could be trickier than just an easily listed set. ... Public Function DataScore(_ ... The reason I asked the loop within a loop question was so that I can loop through all of the fields for each record anh have a running score. ...
    (microsoft.public.access.tablesdbdesign)