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
Private Sub cmdReturn_Click() DoCmd.Close acForm, "frmAddEdit"
Private Sub cmdUndo_Click() Me.Undo DoCmd.Close acForm, "frmAddEdit"
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
'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
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
Private Sub cmdEdit_Click() DoCmd.OpenForm "frmAddEdit", , , , acFormEdit