Get the number from the previous record

[philelpko]

Morning all,

Hopefully someone can give me a pointer as I am having no luck finding a solution.

Each of my records needs an expense ID (i.e. P235-994-EXP-001).
I have code set up to extract the 1st 3 parts of the ID from the users selections on the main form but I need to lookup the previous forms last integer so I can increment it by one.

i.e. If P235-994-EXP-001 is the last record I need to lookup the "001" and increment it so my next record reads P235-994-EXP-002.

Not finding anything in any of the tutorials I have been using which mentions a method of doing this.

Cheers for any help!

[NeoPa]

You can't refer back to the previous form value but you might try something like :

Dim intNextVal As Integer
Dim strNextID as String
 
strNextID = Left(Forms![MainForm].[ExpenseID],13)
intNextVal = Right(DMax( _
    "[ExpenseID]", _
    "[ExpenseTable]", _
    "[ExpenseID] Like '" & strNextID & "*'"), 3)
strNextID = strNextID & Format(intNextVal+1, "000")

Obviously filling in the correct names for your table and fields.

[philelpko]

And for the 2nd time you have saved me from a larger head ache!

I hadn't come across the DMax function before but you learn something every day.

Cheers!

[NeoPa]

No Problem.
There is a whole family of D... functions called 'Domain Aggregate' functions.
DLookup(); DMin(); DCount(); etc.