auto numbering without auto number field

[firecop1]

Hi All,
Using Access 2007
I need a field I will call "CASE_NO" that must be autogenerated with each
new record. The number must be consecutive with the first record being
07-001 the next 07-002 etc.... As I am already using the AutoNumber field in
my table, I cannot use another so how would this be done.

Now here is where it gets sticky (although to you guys its probably a no
brainer). At midnight on January 1 of every year the first two digits must
change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3
digits will have to start back at 001. I am only using 3 digits as I dont
feel that I will have over 1000 records in a given year but if that were to
change, if I could incorporate that into the equation it would be better.

As always, I cant thank you all for the help.

Andrew J. Brofman
Chief Fire Marshal
Lake Grove, NY

 

[kingston via AccessMonster.com]

Pieces that might help:

1) Determine the current year: Right(Year(Date()),2)
2) Parse existing data into two parts (in a query): Left([Field],2) and
Right([Field],3)
3) Find the maximum dash number (and add 1): DMax("[DashComponent]","[Query]
","[YearComponent]='07'")
Instead of '07', use part 1 or something else that returns the year you want.
4) Padding a numerical dash component: Right("000" & MaxDashNumber, 3)

 

[firecop1]

Ok its official, you lost me

I understand some of the concepts but being an amateur in every sense of the
word here I am not sure where and how to set this up.

Do I need to add new fields to a table? Do I do this as an expression, as
code or as a macro?

Sorry for the required expanded explainations but I am not that good yet.

Andy

Pieces that might help:

1) Determine the current year: Right(Year(Date()),2)
2) Parse existing data into two parts (in a query): Left([Field],2) and
Right([Field],3)
3) Find the maximum dash number (and add 1):
DMax("[DashComponent]","[Query]
","[YearComponent]='07'")
Instead of '07', use part 1 or something else that returns the year you
want.
4) Padding a numerical dash component: Right("000" & MaxDashNumber, 3)

Hi All,
Using Access 2007
I need a field I will call "CASE_NO" that must be autogenerated with each
new record. The number must be consecutive with the first record being
07-001 the next 07-002 etc.... As I am already using the AutoNumber field
in
my table, I cannot use another so how would this be done.

Now here is where it gets sticky (although to you guys its probably a no
brainer). At midnight on January 1 of every year the first two digits must
change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3
digits will have to start back at 001. I am only using 3 digits as I dont
feel that I will have over 1000 records in a given year but if that were
to
change, if I could incorporate that into the equation it would be better.

As always, I cant thank you all for the help.

Andrew J. Brofman
Chief Fire Marshal
Lake Grove, NY

 

[kingston via AccessMonster.com]

Here's what I recommend, but there are a lot of other ways to accomplish this.
Create a query based on your existing table (don't add fields to your table)
with two calculated fields.
YearComponent: Left([Field],2)
DashComponent: Right([Field],3)

At this point, run the query to see what the functions Left() and Right() do.
In addition, you can use Right(Year(Date()),2) or data from a form as the
criteria for the calculated field YearComponent. After that, you can change
the order of DashComponent to descending and the properties of the query to
return Top 1 to find the maximum value. The DMax() function does pretty much
the same thing but returns a value in code that you can work with.

You'll have to restrict creation of a new record so that it only happens with
an action that you control, e.g. a command button. This action will create a
new record with the data you calculated using the pieces I presented before.
hth

Ok its official, you lost me

I understand some of the concepts but being an amateur in every sense of the
word here I am not sure where and how to set this up.

Do I need to add new fields to a table? Do I do this as an expression, as
code or as a macro?

Sorry for the required expanded explainations but I am not that good yet.

Andy

Pieces that might help:

[quoted text clipped - 29 lines]

 

[John W. Vinson]

Hi All,
Using Access 2007
I need a field I will call "CASE_NO" that must be autogenerated with each
new record. The number must be consecutive with the first record being
07-001 the next 07-002 etc.... As I am already using the AutoNumber field in
my table, I cannot use another so how would this be done.

Are you ABSOLUTELY CERTAIN that you'll never have 1000 cases in a year? (Hint:
if you have over 500 in 2006 the answer is No).

Now here is where it gets sticky (although to you guys its probably a no
brainer). At midnight on January 1 of every year the first two digits must
change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3
digits will have to start back at 001. I am only using 3 digits as I dont
feel that I will have over 1000 records in a given year but if that were to
change, if I could incorporate that into the equation it would be better.

You can use some VBA code in some appropriate form event (the Beforeinsert
event is handy unless you have many people inserting records concurrently).
Use code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYr As String
Dim vID As Variant
strYr = Format(Date, "yy") ' get the two digit year
vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
If IsNull(vID) Then
Me!CASE_NO = strYr & "-001"
Else
Me!CASE_NO = strYr & "-" & Format(Val(Mid(vID, 3)) + 1, "000")
End If
End Sub

John W. Vinson [MVP]

 

[firecop1]

John,
I am laughing as the jurisdiction that this is for only has 300 homes and 3
commercial establishments If every place is inspected once and there are the
usual 40 incidents annually I am at <350. I am pretty confident that I will
be ok although I guess if it were close I could just change the "000" to
"0000" in your code?

In your experience do you think it would be better to do it the way you
describe below or would I be better off with a button on my form that when
"pressed" it autogenerates this number and shows it in a popup field and
then I manually enter it where necessary?

If that is a better option, how is it done and what if any fields would be
necessary (a field to show the result or would I need a table to store the
results so the databse knows what the next number to generate would be)/.

Andy

Hi All,
Using Access 2007
I need a field I will call "CASE_NO" that must be autogenerated with each
new record. The number must be consecutive with the first record being
07-001 the next 07-002 etc.... As I am already using the AutoNumber field
in
my table, I cannot use another so how would this be done.

Are you ABSOLUTELY CERTAIN that you'll never have 1000 cases in a year?
(Hint:
if you have over 500 in 2006 the answer is No).

Now here is where it gets sticky (although to you guys its probably a no
brainer). At midnight on January 1 of every year the first two digits must
change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3
digits will have to start back at 001. I am only using 3 digits as I dont
feel that I will have over 1000 records in a given year but if that were
to
change, if I could incorporate that into the equation it would be better.

You can use some VBA code in some appropriate form event (the Beforeinsert
event is handy unless you have many people inserting records
concurrently).
Use code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYr As String
Dim vID As Variant
strYr = Format(Date, "yy") ' get the two digit year
vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
If IsNull(vID) Then
Me!CASE_NO = strYr & "-001"
Else
Me!CASE_NO = strYr & "-" & Format(Val(Mid(vID, 3)) + 1, "000")
End If
End Sub

John W. Vinson [MVP]

 

[John W. Vinson]

John,
I am laughing as the jurisdiction that this is for only has 300 homes and 3
commercial establishments If every place is inspected once and there are the
usual 40 incidents annually I am at <350. I am pretty confident that I will
be ok although I guess if it were close I could just change the "000" to
"0000" in your code?

<chuckle> Well, I live in a small town too but it's not THAT small. You're
fine for the next few years and your suggested change is right on the mark.

In your experience do you think it would be better to do it the way you
describe below or would I be better off with a button on my form that when
"pressed" it autogenerates this number and shows it in a popup field and
then I manually enter it where necessary?

That's pretty much your call. If you go with a button you don't need to have
the user retype it - just explicitly set the control to the calculated value.

If that is a better option, how is it done and what if any fields would be
necessary (a field to show the result or would I need a table to store the
results so the databse knows what the next number to generate would be)/.

Such a table is indeed useful if there may be multiple users updating the
database simultaneously; the BeforeInsert event is somewhat vulnerable in that
case, as user Joe might start a new record; get a CASE_NO assigned; ten
seconds later (while Joe is still editing the other data in that record) Jane
starts a new record and (since Joe's CASE_NO has not been saved) gets a
duplicate value. I use code from the Access Developer's Handbook which locks
the next-number table, gets a value, increments it, stores it back in the
table, and then unlocks it - preventing duplicates. It's even got some clever
wait code so the second user to try to get an increment doesn't get impacted
(much) - it just pauses their form for a random interval and tries again.

John W. Vinson [MVP]

 

[firecop1]

Ok John,
I entered the code as you wrote it to no avail. My "CASE_NO" field is a text
field although I am unsure if that matters. I tried with the field on a form
and then on a subform but creating new records does not populate the field
in either place. (Was not using the field in two places simultaneously
though).

vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
I also changed the [tablename] to "[activities]" as that is where the field
is.

I am sure it is something I am doing wrong however I am not sure what.
Unfortunately I dont understand what every part of the code does so I am not
picking up on anything.

Andy

 

[John W. Vinson]

Ok John,
I entered the code as you wrote it to no avail. My "CASE_NO" field is a text
field although I am unsure if that matters. I tried with the field on a form
and then on a subform but creating new records does not populate the field
in either place. (Was not using the field in two places simultaneously
though).

vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
I also changed the [tablename] to "[activities]" as that is where the field
is.

I am sure it is something I am doing wrong however I am not sure what.
Unfortunately I dont understand what every part of the code does so I am not
picking up on anything.

Text does matter, and you need some quotemark delimiters. Try

vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE '" & strYr & "-*'")

For clarity, that's


LIKE ' " & strYr & "-*' "

so that the criterion will be

[CASE_NO] LIKE '07-*'

once the pieces are concatenated.

John W. Vinson [MVP]