Iterating through recordsets in code

[Jake Frankham]

Forgive me for having accidentally posted this query in the wrong newsgroup,
I believe it should be in this one !!
____________________________
Hi again

I have called a procedure from within a query in order to return a date

In the VBA, I wish to iterate through each instance of a value in one field
ie empNo

122 ... ... ...
122 ... ... ...
436 ... ... ...
436 ... ... ...
436 ... ... ...
788 ... ... ...

I wish to iterate through each empNo in some kind of loop to determine which
record I wish to display in the query, then move on to the next set of
empNo's

PLEASE can someone tell me how this is done? - I know you can use things
like DO UNTIL RST.EOF but how do I do DO FOR EACH EMPNO?

Thanks Very Much

Jake

 

[Jake Frankham]

FYI

I am calling my function from within a query which holds numerous CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID] = """ &
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake

 

[John Spencer (MVP)]

No, it doesn't. You need to step through each of the records in the recordset

While Not rst.EOF
...
rst.MoveNext
Wend

If you mean you need to step through the rst and do something each time the
value of ClientProgID changes then you could test that in the while loop, you
would need to have an order by clause in your strSQL

strSQL = "SELECT * FROM [sqry_midpointFee]" & _
" WHERE [ClientProgID] = """ & CProgID & """" & _
" ORDER BY ClientProgID"

CProgId = vbnullstring

While Not rst.EOF
If cProgId <> rst("ClientProgID) Then
'Do something here
end if
cProgID = rst("ClientProgID")
rst.MoveNext
Wend

FYI

I am calling my function from within a query which holds numerous CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID] = """ &
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake

Forgive me for having accidentally posted this query in the wrong newsgroup,
I believe it should be in this one !!
____________________________
Hi again

I have called a procedure from within a query in order to return a date

In the VBA, I wish to iterate through each instance of a value in one field
ie empNo

122 ... ... ...
122 ... ... ...
436 ... ... ...
436 ... ... ...
436 ... ... ...
788 ... ... ...

I wish to iterate through each empNo in some kind of loop to determine which
record I wish to display in the query, then move on to the next set of
empNo's

PLEASE can someone tell me how this is done? - I know you can use things
like DO UNTIL RST.EOF but how do I do DO FOR EACH EMPNO?

Thanks Very Much

Jake