Microsoft Access 2003: Update Table Information Based on Criteria

[mchapman]

Table has three fields I want to work with: (1) Service Date (2) PM
Number (3) PM Interval

I'm looking for a macro of VBA code that will perform the following
tasks:

For all records where the Service Date is <Date():
1. update the Service Date by adding the corresponding value
from the PM Interval field to the Service Date.
2. update the PM Number by finding the highest PM Number in the
table and incrementing it by 1 (if the highest PM Number is 00005,
then the new PM Number for the effected record should be 00006, and so
on.)

What I've already tried:

creating an update macro where:
1. Service Date is updated to

![Service Date]+

![PM
Interval] based on the criteria that Service Date is <()
2. PM Number is updated to DMax("[PM Number]","table")+1

What I have works great unless I have two or more records where the
Service Date is <Date(). The way I have the macro written, Access is
trying to update all effected records with the same PM Number. I do
not know how to tell Access to loop through the records changing them
one at a time.

*If the effected record has the highest PM Number i.e. 00005, I would
need it to change to 00006.

Any suggestions are appreciated.