By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,395 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,395 IT Pros & Developers. It's quick & easy.

Get the number from the previous record

P: 31
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!
Nov 28 '06 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,661
You can't refer back to the previous form value but you might try something like :
Expand|Select|Wrap|Line Numbers
  1. Dim intNextVal As Integer
  2. Dim strNextID as String
  4. strNextID = Left(Forms![MainForm].[ExpenseID],13)
  5. intNextVal = Right(DMax( _
  6.     "[ExpenseID]", _
  7.     "[ExpenseTable]", _
  8.     "[ExpenseID] Like '" & strNextID & "*'"), 3)
  9. strNextID = strNextID & Format(intNextVal+1, "000")
Obviously filling in the correct names for your table and fields.
Nov 28 '06 #2

P: 31
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.

Nov 29 '06 #3

Expert Mod 15k+
P: 31,661
No Problem.
There is a whole family of D... functions called 'Domain Aggregate' functions.
DLookup(); DMin(); DCount(); etc.
Nov 29 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.