You cannot format the autonumber in the table. Don't confuse what is stored
in tables with how we humans view the data. Using the two fields (Autonumber
for the sequencing and Date/Time for the date) is the best way to handle
this. Remember, you can set your primary key to be both of these fields.
Just be sure to put the date/time field before the autonumber field. Now
your table will sort by date and sequence number.
Now, there is another issue. A true Autonumber field will not restart at 0
for each change in date. So if the last entry you had for July 8, 2006 has
an autonumber of 10, the first entry for July 9, 2006 would not be 1, it
would be 11. If you want to start with 1 for each day, then an auto number
field will not work for you. It would need to be a Long data type. You
would then have to handle the sequencing programmatically, but that is no big
issue. Here is how you would do that:
NextNum = Nz(DMax("[SeqNum]", "SomeTable", "[TheDate] = #" & _
Me.MyDateField & "#"), 0) + 1
What the above does is find the highest current Sequence Number for the date
in the date control on the form and add 1 to it. If the date is not in the
table, DMax will return Null. So in the formula, Null will be converted to 0
so when you add 1 to it, it will return 1.