Auto Generate Record Number based on date & Autonumber

[haavy]

So.. I've been trying to create a form that auto-advances a record number.

To do it, I simply put a formula of:
=Year(Date()) & "-" & [ID]
..where [ID] is simply an autonumber for the table I'm using.

The expected result would be: 2009-1, 2009-2, 2009-3, etc.

My issue is that it will populate the field properly on the form, but it won't pass that value to the actual field for use later.

I've tried to make this a default value in the field, but it doesn't populate the field properly - I will end up with simply "2009-" because (I've deduced) at the time the field is auto-populated, the value of [ID] is blank. It doesn't fill that value in until I start to enter data on that particular form.

I'm not a complete MS Access Noob... but not far from it, either.

Is there a simple code to have it pass the string to the field?

Thanks!

 

[ajetrumpet]

maybe with the oncurrent event of the form, if it is data entry, put something like:

me.field = year(date()) & "-" & dmax("ID", "source table") + 1

the field will have to be a NUMBER though, not an autonumber.

 

[The_Doc_Man]

Sorry to disagree, but there are two issues to consider here.

First, a field containing a dash between two digit strings is a text field, not a number.

Second, combining an autonumber with anything else immediately invalidates either the autonumber or its usefulness.

If you want a key that restarts numbering every year, what you do is something like use either DCount or DMax of the field where your criterion for the Domain Aggregate is "like """ & year-number-only & "-*""" (assuming you want a format of yyyy-nnnnn).

If an autonumber exists, use it as-is, don't combine it with anything else. That won't reset every year, though. If it absolutely MUST include the year, then you don't want an autonumber because you are imposing a meaning on a field type that normally has no meaning except uniqueness across the entire table. I.e. if you add the year as part of the field, suddenly the field has meaning and autonumber isn't smart enough to support that meaning.

Search this forum for the topic of "meaningful" and "meaningless" keys. That search will include threads on the subject of "modified" autonumbers.

 

[ajetrumpet]

thanks doc. made a mistake.

 

[haavy]

Okay...

So I guess I took the wrong approach, then?

I'm not stuck on the idea, but it would have been more practical if I could devise a system that incorporates the year of occurrence (or entry to be more specific).

Is there no practical way to do this?

Sorry, I apologize for my ignorance, in advance.

 

[ajetrumpet]

i'm not being arrogant heavy, but use my last post, and change the field to TEXT. wouldn't that be fine?

 

[haavy]

Oh!

Okay, sorry - I just presumed that the Doc Man was saying it wouldn't work.

I'll try it now!

Thanks!

 

[WayneRyan]

Haavy,

I'd add two fields to control this:

1) dtmTheDate - The date the record was entered, default value =Date(), don't let the user see or touch it.

2) intTheSequence - The sequence number you will generate. Use your form's BeforeInsert event to:

Me.intTheSequence = Nz(DMax("[intTheSequence]", "YourTable", "DatePart("yyyy", [dtmTheDate]) = DatePart("yyyy", Date)"), 0) + 1

The Nz function is needed because the DMax will return a Null for the first entry of each year.

To display --> DatePart("yyyy", [dtmTheDate]) & "-" & CStr(intTheSequence)

One line of code to create it.
One line of code to display it.


hth,
Wayne

 

[haavy]

Thanks very much, guys!

The first one from ajetrumpet works great.

Sorry to ask, WayneRyan, but I'm getting a compile error from your code - expected: list separator or ) for the first "yyyy" - if you wouldn't mind helping me, again.

Thanks, though, I really appreciate your help!

- Grant.