Help with VBA Code

[access.newby]

Hi, this is my first post so I hope you can help. The crux of what I need to do in VBA is loop through all the records in a table (tblDetail), when a control break is reached (ie Client and Day change) then reset a counter to 1 otherwise add 1. The other complication is that tblHeader has a selection criteria I need to use. I have very minimal exposure to DAO coding so I may need a lot of help. Here are my table relationships:

tblHeader (660 records)
RunID autonumber (PK)
RunCode text*10 (Index with dups
Roster_Code text*25(lookup)
Week_Day integer(lookup)
Staff_No integer(lookup)
RunStartTime date
IncludeInRoster yes/no

tblDetail (1170 records)
ID autonumber(PK)
RunID number(lookup)
Client_No integer(lookup)
Roster_Type text*1(N,C,R)
FundingSource text*4(lookup)
Duration date(time)
Start_Time date(time)
End_Time date(time)
Travel_Time date(time)
VisitNo integer <<<This is the important value, 1=First visit, 2=2nd visit etc) for a Client on a given day

The metacode would be something like:

Open the tables (tblDetail for updating)
Set tmpVisitNo=0
Select only records From tblHeader where IncludeInRoster = yes
Sort the records in tblDetail by Client_no, then Week_Day(from tblHeader)
Loop through the records
when the Client/Day change reset tmpVisitNo to 1
otherwise add 1 tmpVisitNo, update VisitNo with tmpVisitNo



I know this is a big ask but I am desperate here

[Max D]

There are several ways to do this, but its obvious you don't need such a VisitNo really.
Once you need some sort of data, you can get it from database with queries.

Try to describe the situation when you are going to use that VisitNo and I'm sure there is a nice query to do the thing.