×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Modules (VBA Coding) > FAQs

Microsoft: Access Modules (VBA Coding) FAQ

How To

Sequentially Increment a Text Field by tviman
faq705-6727
Posted: 31 Aug 07

If you use a form for data entry, here's an easy way to sequentially increment text fields such as invoice numbers (INV-12345) and purchase order numbers (PO-12345) without relying on an autoincrement field.

Insert the following code in an appropriate event (Onload, OnCurrent, OnClick, etc.) and change the field and table names to match your own.

' Use this line if creating a new record by clicking a button
DoCmd.GoToRecord , , acNewRec

' Me.DataEntry = True   (If you want to enter data as soon as your form opens, use this instead of the above)
  
If Me.newRecord Then
   Dim varResult As Variant
   varResult = DMax("field-name", "table-name")
   If IsNull(varResult) Then         'just in case the table is empty
       Me.[field-name] = "PO-4000"   'Use your own format here
   Else
       Me.[field-name] = Left(varResult, 3) & Val(Right(varResult, 4)) + 1
   End If
Else
   MsgBox "No new record created!"
End If

Note: This bit of code Left(varResult, 3) & Val(Right(varResult, 4)) + 1 will vary depending on your needs.

I can't take credit for this - I found it somewhere on the 'Net a few months ago and have had great success with it.

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close