Auto-numbering for records which can include the date 1

[Gabbie]

Hello,(this is a great site by the way)
I was wondering if anyone could help me. I have spent the last several months developing a project tracking database for my department. What I need (if it's possible)is a macro or an expression that will automatically create a new record number, for each project as it is entered into the database. We currently format our project numbers (which is also the primary key) as follows: 1000-01,1001-01,1003-01 and so on... The last two digits represent the fiscal year in which the project was recieved and the first 4 digits are simply the number of that project in the order that it was recieved. Each new fiscal year starts over at 1000 so projects recieved since Oct. 1st are 1000-02,1001-02,1003-02, etc...
This numbering system was in place before I got here but in retrospect it probably isn't the best in terms of MS Access formatting. Currently, I have to enter the number by hand after searching the project files for the most recent record number. Argh!!
I would appreciate any help with this issue. Thanks.

 

[PantoKing]

Ecobabe,

This code should do what you are trying to do:-


Dim IdNumber As Integer
Dim CurrentYear As String
Dim NewID As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()
Set rst = db.openrecordset("SELECT Max(Left([ID],4)) As MaxID FROM tblIDTest;", dbopendynaset)
IdNumber = rst!MaxID + 1
rst.Close
CurrentYear = Format(Now, "YY&quot
NewID = IdNumber & " - " & CurrentYear


I've used tblIDTest as the table name and ID as the Field name. You will need to change this to the names that you are using.

HTH

Steve

 

[Gabbie]

Thank you much...I'll give it a try.

 

[MichaelRed]

The code looks "O.K." -for a single user- If here are multiple users you can get into difficulty with concrrent access to the field. One of my fablous faqtoids (faq700-184) deals with a similar issue in a safer manner.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[PantoKing]

Michael,

I have used code very similar to this in a system that is accessed by up to 30 users concurrently. To get round the problem of duplicate values the ID field is a primary key (not an autonumber). and I simply trap the error if a duplicate value is produced. Same result, much less code.

Cheers,

Steve.

 

[Gabbie]

Okay, I'm having some difficulty getting this code to work--Mainly because I think I may have put it in the wrong place or something. I created a new field in my table/form (to test this code)and under the properties for that field I put the code in the "On Enter" command by using the Code Builder. At first it was giving me all sorts of syntax error messages but I straightened out the field names etc. and it quit giving me the error message but now it doesn't do anything when I enter that field on the form. I suspect this is something simple but I just don't know enough about this to troubleshoot any further. Any suggestions? Thanks in advance.

 

[PantoKing]

Ecobabe,

I'd suggest to do this on you're data entry form (I'm assuming that you've got one!). On this form have a "New Data Item" button and put the code in the OnClick event. You will need to add another line to the end of the code which will populate the field name. If the field on your form is a text box with the name txtNewID then you would need to add the following.
Me![txtNewID] = NewID.

Hope that you can get this to work. If you still have a problem post again.

Cheers,

Steve.

Make things as simple as possible — but no simpler.

 

[Gabbie]

I got it working--Outstanding! Now here's another question...sigh...I wish I had discovered this site before I started adding records because this code will only work for records added henceforth. I currently have 150 or so records already in my database for which I assigned the project number manually by &quot;In Date&quot; (more or less). I was wondering 1)if there is anyway to apply this numbering system retroactively by using the &quot;In Date&quot; field in my form and 2) If it can assign numbers for the fiscal year which is runs Oct.1-Sept.30 (this is not my idea >:-< ). Also, it really doesn't matter if this requires some of the project numbers to get re-coded to different numbers because nobody but myself knows or uses the project numbers yet. Any thoughts? This forum has been a tremendous help to me already.

 

[PantoKing]

Ecobabe,

If you use the following code it should give you the fiscal year instead of the normal year. I'm assuming that the current fiscal year is 2001 until October 30th next year. Here in the UK it runs from April to March so yours maybe different.

Dim IdNumber As Integer
Dim CurrentDate As String
Dim CurrentYear As String
Dim NewID As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()
Set rst = db.openrecordset(&quot;SELECT Max(Left([ID],4)) As MaxID FROM tblIDTest;&quot;, dbopendynaset)
IdNumber = rst!MaxID + 1
rst.Close
CurrentDate = Format(Now, &quot;MM/YY&quot
If Left(CurrentDate, 2) < 10 Then
CurrentDate = Right(CurrentDate, 2)
CurrentDate = CurrentDate - 1
CurrentYear = Format(CurrentDate, &quot;00&quot
Else
CurrentYear = Right(CurrentDate, 2)
End If

NewID = IdNumber & &quot;-&quot; & CurrentYear

To populate the existing records in the table, clear the values apart from the start ID. and run the following code:-
Dim IdNumber As Integer
Dim CurrentDate As String
Dim CurrentYear As String
Dim NewID As String
Dim rst As DAO.Recordset
Dim rstnew As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String


Set db = CurrentDb()
Set rstnew = db.OpenRecordset(&quot;tblIdTest&quot;, dbOpenDynaset)
rstnew.MoveFirst
Do Until rstnew.EOF
Set rst = db.OpenRecordset(&quot;SELECT Max(Left([ID],4)) As MaxID FROM tblIDTest;&quot;, dbOpenSnapshot)
IdNumber = rst!MaxID + 1
rst.Close
CurrentDate = Format(Now, &quot;MM/YY&quot
If Left(CurrentDate, 2) < 10 Then
CurrentDate = Right(CurrentDate, 2)
CurrentDate = CurrentDate - 1
CurrentYear = Format(CurrentDate, &quot;00&quot
Else
CurrentYear = Right(CurrentDate, 2)
End If
NewID = IdNumber & &quot;-&quot; & CurrentYear
If IsNull(rstnew!id) = True Or rstnew!id = &quot;&quot; Then
rstnew.Edit
rstnew!id = NewID
rstnew.Update
rstnew.MoveNext
Else
rstnew.MoveNext
End If
Loop
rstnew.Close

This should update your tables with IDs. I've assumed the table name and field names are the same as before.

Hope that you can get this to work.

Cheers,

Steve. Make things as simple as possible — but no simpler.

 

[Gabbie]

Thanks,
This should keep me busy for a while.
Gabbie