Custom AutoNumber in Access 2000

When you are about to write a new row, search for the highest number containing today's date in that column of the table ; construct the number for the new row by incrementing; if no row is found with today's date, set x to zero.

Autonumber won't do it for you. I am looking at something similar - yet different - which I did a couple of years ago; I have a button to click for the creation of a new row, and deep within that button's VBA code I loop through all the relevant numbers (just in case there is a gap in my numbering) using DCount("[NumberWithDate]","The Table","NumberWithDate='" & TodaysDateFormatted & IDCounter & "'")>0; IDCounter is the loop variable and should come out with the value you want in your new NumberWithDate. My code follows, but obviously yours will be different. Indeed, I think I myself would do it differently today.
Private Sub NewProgram_Click()
If Me.DataEntry _
Then
If IsNull(Me![name]) Or Me![name] = "" _
Then
Me.DataEntry = False
Else
number_the_new_Program
End If
not_in_Add_mode
neither_Adding_nor_Editing
Else
Start_Add
End If
End Sub
Private Sub number_the_new_Program()
Dim IDCounter As Integer
Dim navCounter As Long
IDCounter = 1
Do While DCount("[ProgramID]", "ProgramGroup", "ProgramID = '" & IDCounter & "'") > 0 _
And IDCounter < 100000
IDCounter = IDCounter + 1
Loop
If IDCounter = 100000 _
Then
MsgBox "There is no more room in the table of Program Groups - please tell I.S."
Else
Me.programID = IDCounter
Me.UmbrellaID = 0
lastUpdated = Date
MsgBox Me![name] & " will have Program ID " & Me.programID
Me.DataEntry = False
navCounter = DCount("[ProgramID]", "ProgramGroup", "ProgramID <= '" & IDCounter & "'")
DoCmd.GoToRecord , , acGoTo, navCounter
End If
End Sub

Date() is not formatted differently: it is not formatted at all, but appears formatted according to the format set in your control panel when you look at it in a table or display it. It is unwise simply to count and then choose the next highest number, because you may sometimes, even on the day of creation, delete a row (so you would have {0,1,2,4}, giving you another 4 for the next). If you are sure you will not delete, DCount("DatePart","The Table","DatePart=DateValue('" & Date() & "')" will yield the number you want. It may be best to put the date part and the counter part in separate columns and use the columns together as key. When you display the date you can format it using the function "format". I advise against putting a date string in the table: use the plain date and format it as desired elsewhere.

Hello,

I am not sure if this will solve your issue: You could use an ID field which is auto-number and another field called Timestamp or something similar with a default value of Format(Date(),"mmddyy"). The combination is going to be unique, for example,
ID_________Timestamp________Value
1 092503 Acme Corporation
2 092503 Acme Industrial
.............
100 092603 Roadrunner

The only caveat is that the ID does not recycle every day. it just keeps incrementing.

If your need is rigid, then we need to write code that is a little involved. Please let me know if you would like me to give more details.

regards,
Ravi (ananth_manabala@yahoo.com)

When you say a record is created, how is the record created? Are you importing from a file or are you entering in the table directly or are you using a form to enter data? If it is the last one, we can use a button and a simple code snippet to accomplish the your requirement. Please let me know and I will send you the code.
Thanks
Ravi(ananth_manabala@yahoo.com)

Sorry,

I've just created a basic form and I create new
records simply by using the transport down the
bottom.

I do not think you will get what you want by simple entry. You need a screeen without the navigation buttons and with DataEntry and AllowEdits both set to true (I have a button to change these things). If the key as you want it is already in the Excel file you can do at least the conversion by linking to the Excel file and doing an SQL INSERT, either with a Query or with DoCmd.RunSQL in VBA. Even if you are sure there will be no deletions on the same day, you need to know how many you already have for the current day, and the DCount invocation I have shown you is the way to get that.

Thanks all for your help,

I've got the problem figured. I'm sure it's far from elegant but I've got this small bit of code running when the text box comes into focus.
If Text1.Text = "" Checks to see if it's a new record or not.

If Text1.Text = "" Then
Text1.Text = Format(Date, "ddmmyy") & "-" & CInt(Right(DMax("[Number]", "tblTest", Left([Number], 6) = Format(Date, "ddmmyy")), 1)) + 1)

This seems to work exactly how I want it without using two columns or anything.

This question was closed by the author