I had a similar question for a Kitchen Installation Management application.
This probably isn't the answer you want at the moment but you might find it
helpful.

I needed a way to generate a customerID that was Alphanumeric and in a
particular format. Albert Kallal (another MVP)came up with some code that
allows me to define a starting number and prefix in a 'MyCompanyInformation'
file and then each time you create a record int eh main client data table,
it enters an increasing customerID for you - this is in ADDITION to the
autonum ID. Code is poosted below.

The other point is that you shouldn't really delete records unless you are
doing something like a 'month end' or 'year end' cleardown routine. Probably
the best way is to have a field in the table called 'deleted' and switch the
field on when you want to mark that record as deleted (& maybe then put data
into a field called something like 'reason for deletion'.

I do a similar thing for jobs which are completed. This allows me to run a
query and only have active jobs showing in my database but to be able to
look up historical jobs should I ever need to. Alternatively (or including )
you could have an 'archive flag' that would allow you to move the record to
a subsidiary table on a month end/year end cleardown. This keeps the main
table clean and with the smallest possible number of active records.

Albert Kallal's code

Put this in Module Global Code

Public Function NewCustNum() As String
  Dim myrecs As DAO.Recordset
  Set myrecs = CurrentDb.OpenRecordset("tblMyCompanyInformation")
  NewCustNum = myrecs!PrefixforClientID & Format(myrecs!StartingClientID,
"0000")
  myrecs.Edit
  myrecs!StartingClientID = myrecs!StartingClientID + 1
  myrecs.Update
  myrecs.Close
  Set myrecs = Nothing

End Function

And have 2 fields in the MyCompanyInformation table (a single record table
with all YOUR company details). PrefixforClientID (in my case a 2 letter
alpha code representing the branch office) and StartingClientID (which is
where I put the starting number).

In the form you use for entering data you then put the following lines in
the BeforeInsertEvent procedure:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CustomerID = NewCustNum()
End Sub

The moment you put any data into a new record, the CustomerID field on the
form gets populated with the next record in the series.