Want To Create New Record Based On Job Number and Add 1 To Revision Number

[johndavidross]

I inherited an Access database and I'm trying to add on a method of creating a revision level field that can count up as needed.

I have 2 fields: Job Number and Revision Number. I want to create a button that creates a new record based on an old job number and then do a validation which ups the revision number by one.

For example: I already have job number 1234, Rev 1. I would like to have a drop down to select job 1234 and then the system looks to see what the highest revision level is for 1234 and then assigns it Rev 2.

Thank you.

[NeoPa]

If you have a [Job No] selected and you want to create a new [Revision No] for a new record which is one more than any that currently exists then you need to find the maximum [Revision No] used so far.

In VBA that can be done using DMax(), a Domain Aggregate function. Assuming the selected [Job No] is currently held in a variable called strJobNo then you want something similar to :

lngRevisionNo = Nz(DMax(Expr:="[Revision No]" _
                      , Domain:="[YourTable]" _
                      , Criteria:="([Job No]=" & strJobNo & ")"), 0)

NB. I've assumed both are numeric fields due to the lack of relevant information in the question. If either field isn't then you need to handle the quotes (See Quotes (') and Double-Quotes (") - Where and When to use them).