Auto Increment

[al416]

I have a table with a composite primary key made up of WaiverNumber, Code,
and CurrentYear.
WaiverNumber is a two digit sequential number from 01 to xx (not greater than
99).

Code is a unique code assigned to an individual (Joe is 08 and George is 06,
etc.).

CurrentYear is the current calendar year.

I need to increment WaiverNumber by 1 for each new record for each Code (or
individual) and reset code back to 01 for each new CurrentYear.

Ex: The first waiver for Code 08 (Joe) for the year 2009 needs to be 090801
(Year/Code/WaiverNumber). The next waiver for Code 08 needs to be 090802. The
first waiver for Code 06 (George) for the year 2009 needs to be 090601. In
2010 the WaiverNumber will reset to 01 and the Year wil change to 10 (ex.
100801, 100802, etc.)

I can use the DMax function to get the max waiver number, but how can I
further delineate the max waiver number for a particular individual (Code)
and start all over again in the new year?

 

[Stefan Hoffmann]

hi,

I have a table with a composite primary key made up of WaiverNumber, Code,
and CurrentYear.
WaiverNumber is a two digit sequential number from 01 to xx (not greater than
99).
Code is a unique code assigned to an individual (Joe is 08 and George is 06,
etc.).
CurrentYear is the current calendar year.

Does composite means that you store these values in separate fields or
do you have on field with a composite content?

In the second case you should should apply the first rule of
normalization and store the three parts of your composite field in
separate fields (Integer or Long):

[Year] Integer not null

 Integer not null
[WaiverNumber] Integer not null

Year is a reserved word, so you should consider using a better name.
[QUOTE]
I need to increment WaiverNumber by 1 for each new record for each Code (or
individual) and reset code back to 01 for each new CurrentYear.[/QUOTE]
Then you can easily use DMax() for determining the next [WaiverNumber]:

WaiverNumber:
Nz(
DMax("[WaiverNumber]",
"yourTable",
"[Year] = Year(Date()) And [Code] = 8"),
0) + 1
[QUOTE]
Ex: The first waiver for Code 08 (Joe) for the year 2009 needs to be 090801
(Year/Code/WaiverNumber). The next waiver for Code 08 needs to be 090802. The
first waiver for Code 06 (George) for the year 2009 needs to be 090601. In
2010 the WaiverNumber will reset to 01 and the Year wil change to 10 (ex.
100801, 100802, etc.)

I can use the DMax function to get the max waiver number, but how can I
further delineate the max waiver number for a particular individual (Code)
and start all over again in the new year?
[/QUOTE]




mfG
--> stefan <--

 

[al416]

Each of the values is stored in a seperate field and year is named
CurrentYear in the table.

hi,

I have a table with a composite primary key made up of WaiverNumber, Code,
and CurrentYear.

[quoted text clipped - 3 lines]

etc.).
CurrentYear is the current calendar year.

Does composite means that you store these values in separate fields or
do you have on field with a composite content?

In the second case you should should apply the first rule of
normalization and store the three parts of your composite field in
separate fields (Integer or Long):

[Year] Integer not null

 Integer not null
[WaiverNumber] Integer not null

Year is a reserved word, so you should consider using a better name.
[QUOTE]
I need to increment WaiverNumber by 1 for each new record for each Code (or
individual) and reset code back to 01 for each new CurrentYear.[/QUOTE]
Then you can easily use DMax() for determining the next [WaiverNumber]:

WaiverNumber:
Nz(
DMax("[WaiverNumber]",
"yourTable",
"[Year] = Year(Date()) And [Code] = 8"),
0) + 1
[QUOTE]
Ex: The first waiver for Code 08 (Joe) for the year 2009 needs to be 090801
(Year/Code/WaiverNumber). The next waiver for Code 08 needs to be 090802. The[/QUOTE]
[quoted text clipped - 5 lines][QUOTE]
further delineate the max waiver number for a particular individual (Code)
and start all over again in the new year?[/QUOTE]

mfG
--> stefan <--[/QUOTE]

 

[al416]

So, where do I place this code?

I tried putting it in for the WaiverNumber on the New Waiver form but it
doesn't seem to work (#Error#).

Could I put it in the code that opens the form from the menu? I open the New
Waiver form in Add only mode. I should be able to set the value of the
WaiverNumber when I open the form, right?

Each of the values is stored in a seperate field and year is named
CurrentYear in the table.

hi,

[quoted text clipped - 35 lines]

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

So, where do I place this code?

You may place it as a control source as:

=Nz(
DMax("[WaiverNumber]",
"yourTable",
"[Year] = Year(Date()) And

 = 8"),
0) + 1

Without line breakes of course.
[QUOTE]
Could I put it in the code that opens the form from the menu? I open the New
Waiver form in Add only mode. I should be able to set the value of the
WaiverNumber when I open the form, right?[/QUOTE]
When you like to use it in code:

Private Sub Form_Load()

ctlWaiverNumber.Value = _
Nz( _
DMax("[WaiverNumber]", _
"yourTable", _
"[Year] = Year(Date()) And [Code] = 8"), _
0) + 1

End Sub

You need to replace [Year] with the name of your year field and
yourTable with the name of the table. You also need to replace the
hardcoded 8 for Joe.


mfG
--> stefan <--

 

[Marshall Barton]

So, where do I place this code?

I tried putting it in for the WaiverNumber on the New Waiver form but it
doesn't seem to work (#Error#).

Could I put it in the code that opens the form from the menu? I open the New
Waiver form in Add only mode. I should be able to set the value of the
WaiverNumber when I open the form, right?

The most common place to calculate the next number is in the
form's BeforeUpdate event:

If Me.NewRecord Then
Me.WaiverNumber = Nz(DMax("WaiverNumber", "thetable", _
"Code=" & Me.Code & _
" And CurrentYear =" & Year(Date)), 0) + 1
End If

 

[tina]

just to add a note to Stefan's suggestions: if you put the expression in a
control's ControlSource property on a form, the value with *display* in the
control in Form view, but will *not be saved* to a field in the underlying
table unless you write code to do so.

also, you can run the VBA code in the form's Load event if you choose; but,
if you add more than one record in the form before closing it, any records
you enter after the first one will not be assigned a new number. if that's
an issue, then instead of running the code in the form's Load event, suggest
you run it in the form's BeforeInsert event or BeforeUpdate event. also note
that if you ever use the code in the BeforeUpdate event of a form that
allows adding *and* editing records, you'll need to test for NewRecord
before running the code, otherwise you'll overwrite the waiver number of
existing records. something like

If Me.NewRecord Then
<waiver generation code here>
End If

hth

hi,

So, where do I place this code?

You may place it as a control source as:

=Nz(
DMax("[WaiverNumber]",
"yourTable",
"[Year] = Year(Date()) And

 = 8"),
0) + 1

Without line breakes of course.
[QUOTE]
Could I put it in the code that opens the form from the menu? I open the New
Waiver form in Add only mode. I should be able to set the value of the
WaiverNumber when I open the form, right?[/QUOTE]
When you like to use it in code:

Private Sub Form_Load()

ctlWaiverNumber.Value = _
Nz( _
DMax("[WaiverNumber]", _
"yourTable", _
"[Year] = Year(Date()) And [Code] = 8"), _
0) + 1

End Sub

You need to replace [Year] with the name of your year field and
yourTable with the name of the table. You also need to replace the
hardcoded 8 for Joe.


mfG
--> stefan <--[/QUOTE]

 

[al416 via AccessMonster.com]

I set this up as recommended and it worked great for the first new record
only. All subsequent attempts yield a "Data type mismatch error in criteria
expression" error.

Any ideas??

So, where do I place this code?

[quoted text clipped - 4 lines]

Waiver form in Add only mode. I should be able to set the value of the
WaiverNumber when I open the form, right?

The most common place to calculate the next number is in the
form's BeforeUpdate event:

If Me.NewRecord Then
Me.WaiverNumber = Nz(DMax("WaiverNumber", "thetable", _
"Code=" & Me.Code & _
" And CurrentYear =" & Year(Date)), 0) + 1
End If

 

[Marshall Barton]

The type mismatch error is because the Code and/or
CurrentYear fields is a Text or Date/Time field in their
table. Since I didn't see where you explained the fields'
data type, I assumed they were both number type fields. For
a Text field, the value needs to be encosed in quotes. For
example, if the Code field is a Text field:

. . .
"Code=""" & Me.Code & _
""" And CurrentYear =" & Year(Date)), 0) + 1

Note: when we get this working, you will not see the new
number until the record is saved. This is because
calculating the new number any earlier, opens the door to
many scenarios where another user will get the same number.