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.


Access Howto:

Create a Autonumber with a Suffix by bobbster2000
Posted: 16 Oct 01

I had had a problem where I had a field called "workorder number". I needed it to be auto incremented. It also needed a suffix added to it in some cases. The access autonumber doesn't allow any alpha characters so I needed a way to go about this without using autonumber (by the way, I've heard that auto number is a terrible thing to use when it comes to record sourcing). Jebry from Access general discussion form provided me with the following code. Its a charm.

Option Compare Database

Private Sub cmdAddAnother_Click()
    DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

Private Sub cmdReturn_Click()
    DoCmd.Close acForm, "frmAddEdit"
End Sub

Private Sub cmdUndo_Click()
    DoCmd.Close acForm, "frmAddEdit"
End Sub

Private Sub Form_Current()
    'This routine will determine how the form was opened and
    'will add information to the job # and suffix if necessary
    'Local Variables
    Dim rst As DAO.Recordset
    Dim sql As String
    Dim LetterArray(1 To 26) As String
    'Set letter array
    LetterArray(1) = "a"
    LetterArray(2) = "b"
    LetterArray(3) = "c"
    LetterArray(4) = "d"
    LetterArray(5) = "e"
    LetterArray(6) = "f"
    LetterArray(7) = "g"
    LetterArray(8) = "h"
    LetterArray(9) = "i"
    LetterArray(10) = "j"
    LetterArray(11) = "k"
    LetterArray(12) = "l"
    LetterArray(13) = "m"
    LetterArray(14) = "n"
    LetterArray(15) = "o"
    LetterArray(16) = "p"
    LetterArray(17) = "q"
    LetterArray(18) = "r"
    LetterArray(19) = "s"
    LetterArray(20) = "t"
    LetterArray(21) = "u"
    LetterArray(22) = "v"
    LetterArray(23) = "w"
    LetterArray(24) = "x"
    LetterArray(25) = "y"
    LetterArray(26) = "z"
    'Test for edit or add
    If Len(Me.OpenArgs) <> 0 Then
        Me.NavigationButtons = False
        If Me.OpenArgs = "Add" Then
            txtJob.Value = DMax("JobNum", "tblTest") + 1
            txtJob.Value = Me.OpenArgs
            sql = "Select * From tblTest Where JobNum = " & _
                    Me.OpenArgs & " Order By Suffix"
            Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
            txtSuffix.Value = LetterArray(rst.RecordCount)
            txtName.Value = rst!Company
        End If
        Me.AllowAdditions = False
        cmdUndo.Visible = False
        cmdAddAnother.Visible = False
    End If
End Sub

Option Compare Database

Private Sub cmdAddRecord_Click()
    DoCmd.OpenForm "frmAddEdit", , , , acFormAdd, , "Add"
End Sub

Private Sub cmdAddWithData_Click()
    Dim JobNumber As String
    Dim rst As DAO.Recordset
    JobNumber = InputBox("Please type in a job number :  ")
    'Check for existance of job
    Set rst = CurrentDb.OpenRecordset("tblTest", dbOpenDynaset)
    rst.FindFirst "JobNum = " & JobNumber
    If rst.NoMatch = False Then
        DoCmd.OpenForm "frmAddEdit", , , , acFormAdd, , JobNumber
        Call MsgBox("The job number you entered does not exist.")
    End If
End Sub

Private Sub cmdEdit_Click()
    DoCmd.OpenForm "frmAddEdit", , , , acFormEdit
End Sub

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics 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