Auto Numbering Using VBA in Microsoft Access

[MRWallace49]

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.

[#2]

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.

[#3]

@arnel_gp 

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.

[#4]

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

[#5]

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.

[#6]

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.

[#7]

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.