Help creating a loop update

[DanHayden]

Hi guys,

I am creating a transactional database for a payroll department. The database logs requests that come into the department from managers. As such I have a master log table which contains information such as when the request was recieved, who sent it etc. and a sub log table which records the details (each master request contains many pay related transactions for different employees).
Since the payroll department has periodic cut off times I'd like the database to be able to mark on each of the sub transactions if the request is retrospective (ie occured in a prior period). This is so we can measure the real extent of employee's not being paid correctly due to poor submission of requests.

I've set up the subform which contains the smaller transactions as a datasheet (so spreadsheet information can be pasted in). When the log has been completed I have an OK button which I'd like to attach a for...next function which evaluates the transaction start date (eg. 123 should have recieved a payrise 5 months ago) against the current period start date and mark a boolean field as true if it is retrospective.

The problem I am experiencing with this is referencing just the transaction numbers relating to that specific request. I can code it so it loops though the entire database but obviously it changes data from other transactions (making reporting impossible).

I think i need something like "for all log number x, if sublog number date < current period start date, mark as retrospective, next record until last" which is triggered by closing the log.

Any pointers in how to get my function working would be (very) gratefully appreciated. I'm sure I could accomplish the same using update queries but i'd like to keep the database as tidy as possible (with preference to code).

Thanks,

Dan

[DanHayden]

Just an update - I've done it (at last! :D) so no need to reply.

Thanks,

Dan

[NeoPa]

Well done Dan.

Sometimes just getting your thoughts together to put them down is enough to trigger an answer.

Welcome to Bytes!