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 of the script it just over rights the same record each time and does not start a new. I'm thinking there is a missing piece or I'm using the wrong command, but I'm new to VBA.
I'm also trying to get the script to auto fill the date and time fields of the record as well as have drop downs that auto fill specific update specific fields. I think I figured out the drop downs to fill the fields, but none of this matters if it just over writes the same record each time.
If someone can point me in the right direction it would be helpful.
Thank you in advance.
- 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.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
