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...
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.Combo1
DoCmd.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
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.
- I had to create a new table with a primary key, run the script and then delete the row that had the primary key and now it works.
Very confusing.- so you don't have a PK?
you always add one and an autonumber.
the later is very useful on identifying for each record's uniqueness.