Hi,

I've just migrated data from Excel to Access.

I've created a basic form to add new records and each record
needs to follow the previous formatting for it's unique number.

I'm wondering if it's possible to automatically generate this
number so it doesn't have to be entered manually.

The format I want to follow is ddmmyy-x

This is basically the date followed by a number that increments,
starting from 1 each day.

I'm pretty new to Access and have minimal knowelege of any VBA.
I'm guessing that it'll be an event procedure on Before Insert.

I hope this makes sense and someone can help me out.

You can find lots of posts here to help you. However, I suggest very strongly that you use two separate fields. One to hold the date and the second to hold the sequence number. You can concatenate the fields for printing if that's what you want to do.

Ok. I've searched through a lot of posts already and haven't
found what I'm after.

Are there any posts you could point me to?

Thanks

Here's a few. Make sure you read them all.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=53409&highlight=DMax+and+autonumber
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=50389&highlight=DMax+and+autonumber
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=42374&highlight=DMax+and+autonumber

Thanks Pat.

You must be getting well annoyed with people like us that have
to make meaningful autonumbers :rolleyes:

But they're so many people that just can't comprehend the
implications of this, they can't even enter dates into the form
correctly :(

Thanks for your help and sorry to be a pain,

Craig

Feel free to comment on how I've done this but this is what I came up with.

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.

The first part formats the date how I want it and the second part
searches through the records for the largest number today and adds one, hence getting the following numbers;

270903-1, 270903-2 etc.

I'm aware that this won't work for more than one digit on the end
but I won't need it too. If anyone else can see any problems that
my beginners eyes can't see, let me know.

Thanks again for your help.

1. Don't use the .text property except under very specific circumstances. Use .value or omit the property entirely since .value is the default. Read the help entries to understand the difference.

2. Use Me.NewRecord to determine if you are on a new record.
If Me.NewRecord = True Then
....

3. Since date clearly has some significance for you, store it separately as a date. Do the concatination only on output. You will find working with a date field quite easy. If you want to produce a report sorted by year/month you'll be able to. Try that with your mushed field.

4. I'm aware that this won't work for more than one digit on the end but I won't need it too (SIC) - famous last words.

5. Make sure that Number is not a reserved word. If it is, use something like SeqNumber instead.

6. Date IS a reserved word. Change the name of the column. Look up "reserved words" in help and in the archives here. I have posted links to the kb articles that list reserved words.

Originally posted by Pat Hartman
3. Since date clearly has some significance for you, store it separately as a date. Do the concatination only on output. You will find working with a date field quite easy. If you want to produce a report sorted by year/month you'll be able to. Try that with your mushed field.


To do that though, I'll have to go through about 900 old records
and split the field into 2, which is why I'm trying to keep it all
together. The problem with these guys is that while they were
using excel no-one could format their entries correctly. Some of
them are accessing the table directly as well and if they see
two separate fields their minds will boggle and they won't
comprehend what's happening.

The reports I'm producing are based on a different date field.


6. Date IS a reserved word. Change the name of the column. Look up "reserved words" in help and in the archives here. I have posted links to the kb articles that list reserved words.

Originally I typed Date(), but access automatically removed the ()
as it is a reserved word and the parenthesis aren't needed.

I'll have to check out Number though,

Thanks for your other pointers,

All goes towards better coding practice.