Forum Discussion
Auto Numbering Using VBA in Microsoft Access
I'm trying to do a custom Auto Numbering to create work order numbers based in Date, Time and Tech initials. I've figured out how to do this using a VBA script, but if I use DoCmd.Close at the end o...
are you using Form for your data entry.
you need to call your custom autonumber function on the BeforeInsert event of your form and
without seeing much of your form/code there is not much to say.
you need to call your custom autonumber function on the BeforeInsert event of your form and
without seeing much of your form/code there is not much to say.
Yes, The form has the VBA script embedded into a button.
The button closes the form and overwrites the record but does not create a new one. I'd like it to keep the form open so we can add more tickets.
what I have so far is this:
Option Compare Database
Private Sub Command3_Click()
Me.Work_Order = Format(Date, "yyyymmdd") + Format(Time, "hhmmss") + Me.Combo1DoCmd.Close
End Sub
Apparently this is the wrong ending command.
- you need "additional" codes before closing the form, you need to actually "add" a record then assing the work_order
Private Sub Command3_Click()
'goto new record
DoCmd.GoToRecord,,acNewRec
Me.Work_Order = Format(Date, "yyyymmdd") + Format(Time, "hhmmss") + Me.Combo1
' explicitly save the record
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
End Sub- I'm not sure if there is a problem with Access. Or the script. I tried the above script and it worked once and then erased every record after that. I've also found that now the Access database will not create more than one record. I deleted it, recreated it and it still will only create one record, with or without the script. I've never in all the years of working with access had this happen.
