INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
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!
- Students Click Here
*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
|
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
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:
CODEPrivate 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
|
|
|
|
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:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
