Create a Autonumber with a Suffix

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()
Me.Undo
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
Else
txtJob.Value = Me.OpenArgs
sql = "Select * From tblTest Where JobNum = " & _
Me.OpenArgs & " Order By Suffix"
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
rst.MoveLast
txtSuffix.Value = LetterArray(rst.RecordCount)
txtName.Value = rst!Company
End If
Else
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
Else
Call MsgBox("The job number you entered does not exist.")
End If

End Sub

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

End Sub