how to create suffix for auto increment

[Fredo Vincentis]

I am trying to get one of my columns in Access database to have a default
value of an auto-incrementing number as well as the current year. So
something like this:

200401 (being the suffix 2004 and the increment number 01)
200402
200403
etc

I have got no clue how to achieve this. I managed to set the default value
to display the current year as well as adding numbers manually like this:

=Format(Date(),"yyyy") & "01"

but I obviously want the "01" to increment automatically.

Does anybody have an idea on how I could achieve this?

Thanks heaps!

 

[John Vinson]

I am trying to get one of my columns in Access database to have a default
value of an auto-incrementing number as well as the current year. So
something like this:

200401 (being the suffix 2004 and the increment number 01)
200402
200403
etc

I have got no clue how to achieve this. I managed to set the default value
to display the current year as well as adding numbers manually like this:

=Format(Date(),"yyyy") & "01"

but I obviously want the "01" to increment automatically.

Does anybody have an idea on how I could achieve this?

This is called an "Intelligent Key" and is somewhat frowned upon in
the best circles. Storing two pieces of data in the same field (a date
and a sequence) violates the principle that table fields should be
"atomic"; in addition, someday you'll have 99 records in a year and be
stuck adding another one!

If you want this number for compatibility with an existing paper
system, I'd suggest having two fields - an IDYear field with a default
property of Year(Date()), and an IDSeq field, short integer. You can
select these two fields and click the Key icon to make them a joint
two-field Primary Key. To populate the field you must use VBA code in
the Form (table datasheets don't have any usable events); select the
Form's BeforeInsert event, invoke the Code Builder, and put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[IDSeq]", "[your-table-name]", _
"[IDYear] = " & Me!IDYear)) + 1
If iNext > 99 Then
MsgBox "Go home, no more work can be entered this year", vbOkOnly
Cancel = True
Else
Me!IDSeq = iNext
End If
End Sub

 

[Fredo Vincentis]

This is called an "Intelligent Key" and is somewhat frowned upon in
the best circles. Storing two pieces of data in the same field (a date
and a sequence) violates the principle that table fields should be
"atomic"; in addition, someday you'll have 99 records in a year and be
stuck adding another one!

I see your point and agree. The year-based number is not a good idea and
should be dropped. I could simply do that by continuing on with
auto-incrementing numbers from the id we have reached so far. The question I
have then is: do you know a way of making my auto-inc number start at a
certain value?

Let's say we have reached ID 200428 and are now moving to this completely
new database, but wish to continue on with the ID from that point. How could
I tell my very first record in the database not to start with ID 1, but with
ID 200429? I could in theory create 200428 empty records and delete all of
them, but that would take me a while, I think. There must be an easier way?

Thanks for the help!

 

[John Vinson]

There must be an easier way?

Two of them.

Run an Append query and append a single record to the table, with one
less than the desired starting number for your table. This will "seed"
the autonumber, and it will continue from there.

Better, don't use Autonumbers at all: they will ALWAYS have gaps, and
aren't really designed as human readable values, only as unique keys.
Use a Long Integer field instead, and ensure that all data entry
occurs using a Form. In that Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "yourtable"), 200428)
End Sub

(or whatever starting point you want).

 

[Fredo Vincentis]

On Fri, 16 Apr 2004 07:48:07 +1000, "Fredo Vincentis"

Better, don't use Autonumbers at all: they will ALWAYS have gaps, and
aren't really designed as human readable values, only as unique keys.
Use a Long Integer field instead, and ensure that all data entry
occurs using a Form. In that Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "yourtable"), 200428)
End Sub

Sorry, I am not really familiar with building Code in Access. I tried doing
it, but to no result. Let's see whether I got it right:

I went to the design view of my form (called "Invoices"). I right-clicked
the form and went to Build Event > Code Builder. I entered following code:

Private Sub Invoices_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

with "Invoices" being the name of my form and tbl_Invoices being the name of
the table that holds the column "ID" (Number).

Is that correct? When I run the form in Form View, my ID still remains 0.
Any idea where I go wrong? Thanks heaps for your help!

 

[John Vinson]

Private Sub Invoices_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

with "Invoices" being the name of my form and tbl_Invoices being the name of
the table that holds the column "ID" (Number).

Close but not quite. You can get to the code builder from the Events
tab on the form Properties - doubleclick in the space and it will turn
to [Event Procedure], then click the ... icon to enter the code
builder.

Or - just open the VBA editor using the code button and change the
first line to

Private Sub Form_BeforeInsert(Cancel as Integer)

which is where Access expects to find it.

Note that this does assume that you have a Textbox named txtID, bound
to the ID field in the table.

 

[Fredo Vincentis]

Private Sub Invoices_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

with "Invoices" being the name of my form and tbl_Invoices being the name of
the table that holds the column "ID" (Number).

Close but not quite. You can get to the code builder from the Events
tab on the form Properties - doubleclick in the space and it will turn
to [Event Procedure], then click the ... icon to enter the code
builder.

Or - just open the VBA editor using the code button and change the
first line to

Private Sub Form_BeforeInsert(Cancel as Integer)

which is where Access expects to find it.

Note that this does assume that you have a Textbox named txtID, bound
to the ID field in the table.

Ah, it still doesn't like me! Here's the code I have now on the event of the
form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

I right-clicked the textbox that is meant to display the ID and gave it a
Name of "txtID" in the "Other" tab.

I tried that and it didn't work. Returned 0.

The control source of the textfield is "ID". Sorry, man. I really don't know
what I'm doing wrong.

 

[John Vinson]

Ah, it still doesn't like me! Here's the code I have now on the event of the
form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

I right-clicked the textbox that is meant to display the ID and gave it a
Name of "txtID" in the "Other" tab.

I tried that and it didn't work. Returned 0.

The control source of the textfield is "ID". Sorry, man. I really don't know
what I'm doing wrong.

Ok... validate the following assumptions:

- You have a table named tbl_Invoices
- it has a Primary Key field, Number Long Integer datatype, no Default
value (Access will put a 0 there automatically) named ID
- Your Form has a textbox named txtID with a Control Source of ID,
named txtID
- You're navigating to the new record and starting to insert data in
some field other than txtID

If any of these conditions does not hold it probably won't work.

 

[Fredo Vincentis]

Ah, it still doesn't like me! Here's the code I have now on the event of the
form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

I right-clicked the textbox that is meant to display the ID and gave it a
Name of "txtID" in the "Other" tab.

I tried that and it didn't work. Returned 0.

The control source of the textfield is "ID". Sorry, man. I really don't know
what I'm doing wrong.

Ok... validate the following assumptions:

- You have a table named tbl_Invoices
- it has a Primary Key field, Number Long Integer datatype, no Default
value (Access will put a 0 there automatically) named ID
- Your Form has a textbox named txtID with a Control Source of ID,
named txtID
- You're navigating to the new record and starting to insert data in
some field other than txtID

All of the above: yes. Except: you say "Your form has a textbox named txtID
with a Control Source of ID, named txtID". Can I name the Control Source
"txtID" as well as the textbox? Currently I have only named the textbox
"txtID".

I normally don't like suggesting this, but could I email the file to you to
have a quick look at?

 

[Fredo Vincentis]

of

the

form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

I right-clicked the textbox that is meant to display the ID and gave it a
Name of "txtID" in the "Other" tab.

I tried that and it didn't work. Returned 0.

The control source of the textfield is "ID". Sorry, man. I really don't know
what I'm doing wrong.

Ok... validate the following assumptions:

- You have a table named tbl_Invoices
- it has a Primary Key field, Number Long Integer datatype, no Default
value (Access will put a 0 there automatically) named ID
- Your Form has a textbox named txtID with a Control Source of ID,
named txtID
- You're navigating to the new record and starting to insert data in
some field other than txtID

All of the above: yes. Except: you say "Your form has a textbox named txtID
with a Control Source of ID, named txtID". Can I name the Control Source
"txtID" as well as the textbox? Currently I have only named the textbox
"txtID".

I normally don't like suggesting this, but could I email the file to you to
have a quick look at?

If you don't have time to look at the file, that's okay. I just tried your
second option (append an entry with set ID to an autonumber) and that pretty
much does what I needed.

At some stage I will have to have a look at VB in combination with Access a
bit more and get my head around the problem to understand why the other
solution didn't work. I never worked with Access forms before - I normally
write Web Interfaces that do the job for me. But thanks for the help so far!
Really appreciate it.

 

[Fredo Vincentis]

On Sat, 17 Apr 2004 14:59:54 +1000, "Fredo Vincentis"

Ah, it still doesn't like me! Here's the code I have now on the event

of

the
form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("[ID]", "tbl_Invoices"), 200428)
End Sub

I right-clicked the textbox that is meant to display the ID and gave

it

a don't
know

All of the above: yes. Except: you say "Your form has a textbox named txtID
with a Control Source of ID, named txtID". Can I name the Control Source
"txtID" as well as the textbox? Currently I have only named the textbox
"txtID".

I normally don't like suggesting this, but could I email the file to you to
have a quick look at?

If you don't have time to look at the file, that's okay. I just tried your
second option (append an entry with set ID to an autonumber) and that pretty
much does what I needed.

At some stage I will have to have a look at VB in combination with Access a
bit more and get my head around the problem to understand why the other
solution didn't work. I never worked with Access forms before - I normally
write Web Interfaces that do the job for me. But thanks for the help so far!
Really appreciate it.

Interesting. After I changed the format of ID to autonum and went back to
the form, it came up with an error that saying "you can't assign a value to
this object". I do understand why this error comes up now and that I should
take the code out, but on the other hand it shows that something was
happening all along. The code seems to be executing and it seems to be
trying to add to the correct textbox. It just didn't show up any results...
curious.

 

[Fredo Vincentis]

Got it working! I changed the format back to number. I then had another look
at the code you gave me and got rid of the [] around the column name. It
then inserted the number 200428, but it did it again and again. I suddenly
understood what the code is doing: it takes the highest value out of the
column ID in the table tbl_invoices, right? The only thing that was missing
was the increment. So my code looks like this now:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("ID", "tbl_Invoices"), 200428) + 1
End Sub

And that works. But I am not quite sure whether this is really better than
an auto-incrementing number. Let's assume I create an invoice with the
number 200430. I print the invoice with its number on it and send it to a
client. For whatever reason I then decide to delete that record! That would
mean that the next invoice created would have the same number again: 200430.
Isn't that opening for errors?

 

[John Vinson]

Got it working! I changed the format back to number. I then had another look
at the code you gave me and got rid of the [] around the column name. It
then inserted the number 200428, but it did it again and again. I suddenly
understood what the code is doing: it takes the highest value out of the
column ID in the table tbl_invoices, right? The only thing that was missing
was the increment. So my code looks like this now:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("ID", "tbl_Invoices"), 200428) + 1
End Sub

You will note that in my original response I *did* have the increment:

iNext = NZ(DMax("[IDSeq]", "[your-table-name]", _
"[IDYear] = " & Me!IDYear)) + 1

And that works. But I am not quite sure whether this is really better than
an auto-incrementing number. Let's assume I create an invoice with the
number 200430. I print the invoice with its number on it and send it to a
client. For whatever reason I then decide to delete that record! That would
mean that the next invoice created would have the same number again: 200430.
Isn't that opening for errors?

It opens for errors, but it avoids gaps in the numbering sequence.
Some accountants (and some auditors) get freaked out if they find
Invoice 3122, 3123, and 3125 and no trace of 3124 - and Autonumbers
will do this not only if you delete 3124 (whether for impeccable
reasons or to cover up that payment to the Aphrodite Massage Parlour)
but also if you just hit <Esc> three keystrokes into an invoice. In
addition, if you want the year as part of the number, you can't use
Autonumber at all.

If you don't want deleted ID's reused - don't delete records at all.
Maybe put a Yes/No field Deleted into the table and set it to True for
"logically" deleted records.

 

[Fredo Vincentis]

Got it working! I changed the format back to number. I then had another look
at the code you gave me and got rid of the [] around the column name. It
then inserted the number 200428, but it did it again and again. I suddenly
understood what the code is doing: it takes the highest value out of the
column ID in the table tbl_invoices, right? The only thing that was missing
was the increment. So my code looks like this now:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("ID", "tbl_Invoices"), 200428) + 1
End Sub

You will note that in my original response I *did* have the increment:

iNext = NZ(DMax("[IDSeq]", "[your-table-name]", _
"[IDYear] = " & Me!IDYear)) + 1

And that works. But I am not quite sure whether this is really better than
an auto-incrementing number. Let's assume I create an invoice with the
number 200430. I print the invoice with its number on it and send it to a
client. For whatever reason I then decide to delete that record! That would
mean that the next invoice created would have the same number again: 200430.
Isn't that opening for errors?

It opens for errors, but it avoids gaps in the numbering sequence.
Some accountants (and some auditors) get freaked out if they find
Invoice 3122, 3123, and 3125 and no trace of 3124 - and Autonumbers
will do this not only if you delete 3124 (whether for impeccable
reasons or to cover up that payment to the Aphrodite Massage Parlour)
but also if you just hit <Esc> three keystrokes into an invoice. In
addition, if you want the year as part of the number, you can't use
Autonumber at all.

If you don't want deleted ID's reused - don't delete records at all.
Maybe put a Yes/No field Deleted into the table and set it to True for
"logically" deleted records.

Sounds fair to me. Again, thanks heaps for your help!

 

[Julian]

Got it working! I changed the format back to number. I then had another look
at the code you gave me and got rid of the [] around the column name. It
then inserted the number 200428, but it did it again and again. I suddenly
understood what the code is doing: it takes the highest value out of the
column ID in the table tbl_invoices, right? The only thing that was missing
was the increment. So my code looks like this now:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("ID", "tbl_Invoices"), 200428) + 1
End Sub

You will note that in my original response I *did* have the increment:

iNext = NZ(DMax("[IDSeq]", "[your-table-name]", _
"[IDYear] = " & Me!IDYear)) + 1

And that works. But I am not quite sure whether this is really better than
an auto-incrementing number. Let's assume I create an invoice with the
number 200430. I print the invoice with its number on it and send it to a
client. For whatever reason I then decide to delete that record! That would
mean that the next invoice created would have the same number again: 200430.
Isn't that opening for errors?

It opens for errors, but it avoids gaps in the numbering sequence.
Some accountants (and some auditors) get freaked out if they find
Invoice 3122, 3123, and 3125 and no trace of 3124 - and Autonumbers
will do this not only if you delete 3124 (whether for impeccable
reasons or to cover up that payment to the Aphrodite Massage Parlour)
but also if you just hit <Esc> three keystrokes into an invoice. In
addition, if you want the year as part of the number, you can't use
Autonumber at all.

If you don't want deleted ID's reused - don't delete records at all.
Maybe put a Yes/No field Deleted into the table and set it to True for
"logically" deleted records.

 

[Julian]

Got it working! I changed the format back to number. I then had another look
at the code you gave me and got rid of the [] around the column name. It
then inserted the number 200428, but it did it again and again. I suddenly
understood what the code is doing: it takes the highest value out of the
column ID in the table tbl_invoices, right? The only thing that was missing
was the increment. So my code looks like this now:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("ID", "tbl_Invoices"), 200428) + 1
End Sub

You will note that in my original response I *did* have the increment:

iNext = NZ(DMax("[IDSeq]", "[your-table-name]", _
"[IDYear] = " & Me!IDYear)) + 1

And that works. But I am not quite sure whether this is really better than
an auto-incrementing number. Let's assume I create an invoice with the
number 200430. I print the invoice with its number on it and send it to a
client. For whatever reason I then decide to delete that record! That would
mean that the next invoice created would have the same number again: 200430.
Isn't that opening for errors?

It opens for errors, but it avoids gaps in the numbering sequence.
Some accountants (and some auditors) get freaked out if they find
Invoice 3122, 3123, and 3125 and no trace of 3124 - and Autonumbers
will do this not only if you delete 3124 (whether for impeccable
reasons or to cover up that payment to the Aphrodite Massage Parlour)
but also if you just hit <Esc> three keystrokes into an invoice. In
addition, if you want the year as part of the number, you can't use
Autonumber at all.

If you don't want deleted ID's reused - don't delete records at all.
Maybe put a Yes/No field Deleted into the table and set it to True for
"logically" deleted records.

 

[Julian]

Got it working! I changed the format back to number. I then had another look
at the code you gave me and got rid of the [] around the column name. It
then inserted the number 200428, but it did it again and again. I suddenly
understood what the code is doing: it takes the highest value out of the
column ID in the table tbl_invoices, right? The only thing that was missing
was the increment. So my code looks like this now:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtID = Nz(DMax("ID", "tbl_Invoices"), 200428) + 1
End Sub

You will note that in my original response I *did* have the increment:

iNext = NZ(DMax("[IDSeq]", "[your-table-name]", _
"[IDYear] = " & Me!IDYear)) + 1

And that works. But I am not quite sure whether this is really better than
an auto-incrementing number. Let's assume I create an invoice with the
number 200430. I print the invoice with its number on it and send it to a
client. For whatever reason I then decide to delete that record! That would
mean that the next invoice created would have the same number again: 200430.
Isn't that opening for errors?

It opens for errors, but it avoids gaps in the numbering sequence.
Some accountants (and some auditors) get freaked out if they find
Invoice 3122, 3123, and 3125 and no trace of 3124 - and Autonumbers
will do this not only if you delete 3124 (whether for impeccable
reasons or to cover up that payment to the Aphrodite Massage Parlour)
but also if you just hit <Esc> three keystrokes into an invoice. In
addition, if you want the year as part of the number, you can't use
Autonumber at all.

If you don't want deleted ID's reused - don't delete records at all.
Maybe put a Yes/No field Deleted into the table and set it to True for
"logically" deleted records.