INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

Join Us!

*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

Tek-Tips Posting Policies

Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) > FAQs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Increment text, such as G-0001, to the next number-like text in Access by GregSmithAlaska

faq707-7571

Posted: 14 Jul 12 (Edited 23 Jul 12)

On my Access form, I added a button named cmdAddNewRecord with caption "Add new record." As you can see from the documentation, my base code came from tek-tips.com. I was so happy to find such a simple answer on tek-tips.com that I decided to share what I learned. Since I don't know how to thank that person or find that FAQ, this is my attempt to say thanks.

For the button's On Click Event, I added:

CODE

Private Sub cmdAddNewRecord_Click()
' Created 2012-07-13 by combining a good tip with formatting from another tip.
' This works fine as long as there is at least one record to increment.
' Our series is for General Correspondence letters, G-0000 format.
' Currently, we are at G-0295 and the boss wanted auto increment for our text field.
' Most of this came from www.tek-tips.com's FAQ705-6727: Sequentially Increment a Text Field: Sequentially Increment a Text Field
' Website page was http://www.tek-tips.com/faqs.cfm?fid=6727
' Their code is more flexible but lacked formatting for leading zeros, which is included.
' I have removed that no-record flexibility since I will start with at least one record.

DoCmd.GoToRecord , , acNewRec

Dim varResult As Variant
' in DMax(), use the FieldName and the TableName, respectively
varResult = DMax("GenOutSerialNum", "tblGenCorrOut")
' This next part separates (parses) the text from the number part,
' adds 1; then puts the parts back together with leading zeros.
Me.[GenOutSerialNum] = Left(varResult, 2) & _
Format(Val(Right(varResult, 4)) + 1, "0000")

End Sub

Since we have only done 295 formal letters in two years, G-0000 format gives us about 10000 letters. If you are doing Purchase Orders, you may want to use PO-00000 format, making some of the code look like Left(varResult, 3) &_
Format(Val(Right(varResult, 5)) + 1, "00000") much like the guy suggested in
www.tek-tips.com's FAQ705-6727: Sequentially Increment a Text Field

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

My FAQ Archive