replace dlookup with recordset

[SC in Texas]

I am having problems writing the correct code to replace the dlookup in the
following with use of a recordset in the vb to speedup the report.
For cur_ppedate = StPPED To EPPED Step 14

Sickused = Sickused + Nz(DLookup("SickHrsUsed",
"AllStaffVacleavebyPPEQry", "Staff_ID ='" & StaffID & "'And PPED = #" &
cur_ppedate & "#"))
cur_bal = BegBal + countadds * SickEarnRate - Sickused

If cur_bal < max_bal Then
countadds = countadds + 1
End If
Next

Any help would be appreciated. I now my attempts are probly being thrawted
by simple syntax errors but I can't see what they are.

Thanks,
Steve

 

[pietlinden]

How about this:
http://www.mvps.org/access/modules/mdl0012.htm

 

[Dale Fye]

You might also want to look at Allen Browne's ELookup( ) function:

http://www.allenbrowne.com/ser-42.html

HTH
Dale

 

[SC in Texas]

I was thinking I should be able to create a recordset from the
"AllStaffVacLeavebyPPEQry" where "Staff_ID =" StaffID and PPED between
FirstEndDate and LastEndDate"
Then run my loop on this recordset until EOF.
I would think this would be much faster than the DLookup in the middle of my
loop.
I keep getting errors on the recordset such as too few parameters or missing
parameters.

I have it setting the parameter value before the openrecordset command.

What am I missing, or is this not a better way to go?

 

[Dale Fye]

post your code
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.