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
Talk To Other Members