Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

VBA How To

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

by  GregSmithAlaska  Posted    (Edited  )
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
' 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
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top