I need to create a unique reference number for each record in my table in a particular format. Can this be automated?

format: W707/01

where W is always W
7 is the Year
07 is the month
/ 01 is automated incremental number starting 01.

At start of new month I need incremental number to reset to 01.

Why? While so-called "smart keys" like that used to be in vogue, they really are a violation of database normalization principles: you're storing multiple values in a single field, which is a no-no.

Presumably you're going to store a transaction date as part of your table. That means you already have the year and month in the table, so your reference number is also storing the same data redundantly.

For the moment, assume that you've got the incremented number (we'll come back to this later). All you need then is to create a query based on your table, add a computed field to that query, and use the query rather than the table.

To add the computed field, you'd type the following into the Field row of a blank column in the query grid:

ReferenceNb: "W" & Left(Format(TransactionDate, "yyyy"), 1) & Format(TransactionDate, "mm") & "/" & IncrementalNb

Now, getting back to the incremental number. Since you want the number to be reset each month, you cannot use an Autonumber field. You'll have to "roll your own". Set IncrementalNb to be an integer (or a Long Integer if there's a chance you'll be generating more than 32,767 entries a month). In the BeforeInsert event of your form, put code to determine the largest number used so far this month, and add 1 to it:



This assumes that you have a text box named txtTransactionDate on your form that contains the appropriate date.

I to have an autonumber question if you dont mind. I have been useing the code below to generate an autonumber for a split DB. This was written when DAO 3.6 was available to me. I now have a clean 2003 version and low and behold no DAO 3.6. From what I understand MS now uses ADO. Can the answer you provided the earlier post work for me. I to ran this this on opening the form in the "on current" event that called the module. Thank you for looking.
I running XP with Access 2003



Public Function newrcnqry()
On Error GoTo newrcnqry_err
Dim mydb As Database
Dim holdrcn As Long
Dim myset As Recordset
Dim mth As Long
Dim yr As Long

mth = DatePart("y", Now)
yr = DatePart("yyyy", Now)
holdrcn = (yr * 100000) + (mth * 100)

Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myset = mydb.OpenRecordset("Rpt Cntl Num Tbl", DB_OPEN_DYNASET)
myset.MoveFirst
If holdrcn > myset!newnum Then
myset.Edit
myset!Year = yr
myset!jday = mth
myset!newnum = holdrcn
myset.Update
myset.Close
Else
holdrcn = myset!newnum + 1
myset.Edit
myset!newnum = holdrcn
myset.Update
myset.Close
End If

mydb.Close

exit_newrcnqry:

To answer you question regard in DAO 3.6, it is alive and well in Access 2003. Just not enabled by default. You have to manually add it as a reference in VBA.

Something sounds not right with your installation. While Microsoft made it sound as though DAO was dead and everyone should be using ADO, they came to their senses and realized that DAO is the most appropriate method when dealing with Jet databases (i.e. MDB or MDE files). Consequently, DAO should be there by default in Access 2003.

Was this a brand new database where you couldn't find DAO, or one converted from a previous version of Access?

If you have a reference to both ADO and DAO, you want to be sure to disambiguate, since Recordset is an object in both models. That means you need

Dim myset As DAO.Recordset

(to guarantee an ADO recordset, you'd use Dim myset As ADODB.Recordset)

(BTW, Access 97 used DAO 3.5x. DAO 3.6 was introduced with Access 2000)

I don't believe that's correct, Walter. While you had to add a reference to DAO 3.6 in Access 2000 and 2002, it should be there by default in Access 2003. (Unfortunately, they didn't have the sense to put it higher in the sequence than the ADO reference).

Your probably right, Doug. I knew it was still there...somewhere. It's always best to disambiguate the code as you have stated.

I looked in the drop down references and find DAO 3.5 and DAO 2.5/3.51 but no 3.6

Are you in Access 2003 or Access 97? It's not in 97.

I am using Access 2003 SP2 and the DB is currently a written in 2000 not converted. I tried converting it using the wizard 2002-2003 to no avail

This DB has been working fine until I had to migrate to a new XP machine. My workmates that didn't get an updated PC are having no problems.

I'm wondering if you need to reregister the DLL...? Does the app compile in VBA?

No the DB will not compile. It highlights the DBEngine and states a error in loading DLL

That's where I would start... either re-register the DLL with RegSvr32 or Reinstall Office 2003. (sometimes reinstalling Office works, sometimes not, but it doesn't hurt to try.)

Thank you for all your help. If I understand everything so DAO 3.6 is still available in 2003. And that I may need to reinstall office. Also how would I re-register the DAO with Regsvr32. Is this another program

It is part of the OS. You can click:

Start> Run

And then type:

regsvr32 YourFullPathToDLL

It should return a message box telling you whether or not it registered it successfully.