Generate account numbers with Auto number

[Jacqueline]

Access 2003 question:
Does anyone know how to set up an automatic account number generation in
Access? I suspect that it can be done with the Auto number, but not sure how
to do this to allow for a never ending amount of accounts.

Is there an add-in or secondary add-in that can do this? I would like to
incorporate the date into the account number if possible.

I am construction a new database that formally did not have account numbers
attached, the client was using the address ast the PK....
Any help will be greatly appreciated.
Thanks

 

[Ken Snell \(MVP\)]

While an AutoNumber field can be used to auto-create incrementing numbers,
it's not a good approach if you don't want gaps between the numbers, and if
you don't want negative numbers as the result.

It's best to write your own procedure to auto-increment values for this
purpose. An easy one is to find the current, maximum value for the field in
the table, and then increment that value by one, and then assign that value
as the new number.

 

[Jacqueline]

Ken,
Thanks this makes sense. Now, can you tell me where I could look for code as
examples? I am a strong Access user, but not so good with Visual Basic. If I
have code to look at and play with I usally can figure out what I need.
Thanks again,

 

[Graham Mandeno]

Hi Jacqueline

If your account number is a simple numeric value (long integer), you can
generate the next one by using the DMax function and incrementing the value
returned. In case there is no record already there, you can throw in an Nz
function to give you a starting value:

NewAccNum = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1

If you have a busy multi-user environment, it is possible using this
technique to have the same number allocated twice, if two users are adding
an account at the same time and the second one starts before the first has
saved the new record. In this case you must either (a) handle the problem
when it occurs by generating a new number and alerting the user that it has
changed or (b) by having a special "NextAccount" table which you open for
exclusive access, retrieve the value, increment the stored value, and write
it back to the table.

In practice, these precautions are rarely necessary, so post back if you are
interested in further details.

 

[Tim]

Does anyone know how to set up an automatic account number

generation in Access? I suspect that it can be done with the
Auto number, but not sure how to do this to allow for a never
ending amount of accounts.

Jacqueline,

I use something along these lines for any place I need a generated number
next in the sequence. The table tblCounter has all the current counts for
any field I track.

I pulled this out of my sub-form that creates the order lines for a purchase
order. I of course want to have a new line item for every item purchased;
and this code does that.

Very interesting is how I had to go and change the code sometime a few
months back from an Integer to a Long. Again one of those things you
may not think of when you design a database; especially if it is your first
Relational Access type database.

There may be other ways, and maybe even cleaner ways, but the following is
what I use and it seems to work for me.

Tim


If Me.NewRecord Then
'
' Dim intTestCount As Integer
' Dim intNextCount As Integer
Dim intTestCount As Long
Dim intNextCount As Long
Dim strCounterName As String
'
strCounterName = "ordLineCount"
intTestCount = Nz(DLookup("[nxtCountInteger]", "tblCounter",
"[nxtCountName] = '" & strCounterName & "'"))
intNextCount = intTestCount + 1
[ordlineId] = intNextCount
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger = " &
intNextCount & " WHERE [nxtCountName] = '" & strCounterName & "'"
'
End If

 

[Jacqueline]

Graham,
This sounds like what I need. The client was previosly useing the mailing
address as the PK with all kinds of bad results. It is a small office I would
not anticipate the user colision you described but will caution the users.

Now, though I am traking on the practically of what you gave me, how do I
apply that to a table? I need this account number to be my PK, and follow the
account through the other tables. I did not think you could have a calculate
filed in a table, so I thinking I have to do this in VB an area I am weak
in....
Thanks for your help on this.
Jacqueline
--
Jacqueline

Hi Jacqueline

If your account number is a simple numeric value (long integer), you can
generate the next one by using the DMax function and incrementing the value
returned. In case there is no record already there, you can throw in an Nz
function to give you a starting value:

NewAccNum = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1

If you have a busy multi-user environment, it is possible using this
technique to have the same number allocated twice, if two users are adding
an account at the same time and the second one starts before the first has
saved the new record. In this case you must either (a) handle the problem
when it occurs by generating a new number and alerting the user that it has
changed or (b) by having a special "NextAccount" table which you open for
exclusive access, retrieve the value, increment the stored value, and write
it back to the table.

In practice, these precautions are rarely necessary, so post back if you are
interested in further details.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Jacqueline

Never fear! It's not as hard as it might seem

You are probably adding records to your table using a form.

If you want the new number to be visible to the user as they are typing in
the other details (name, address, etc) then use the form's BeforeInsert
event, which fires the moment data entry commences in a new record:

Private Sub Form_BeforeInsert()
Me![AccountNum] = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1
End Sub

If you don't need the new number to be visible immediately, then use the
form's BeforeUpdate event. This will delay the lookup until the moment the
new record is being saved, which reduces the window of opportunity for a
collision with another user:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![AccountNum]) Then
Me![AccountNum] = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1
End If
End Sub

Note that BeforeUpdate gets fired for every updated record, not just the new
ones, so you need to allocate a new number only if the record doesn't
already have one [If IsNull(Me![AccountNum])...]
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
This sounds like what I need. The client was previosly useing the mailing
address as the PK with all kinds of bad results. It is a small office I
would
not anticipate the user colision you described but will caution the users.

Now, though I am traking on the practically of what you gave me, how do I
apply that to a table? I need this account number to be my PK, and follow
the
account through the other tables. I did not think you could have a
calculate
filed in a table, so I thinking I have to do this in VB an area I am weak
in....
Thanks for your help on this.
Jacqueline
--
Jacqueline

Hi Jacqueline

If your account number is a simple numeric value (long integer), you can
generate the next one by using the DMax function and incrementing the
value
returned. In case there is no record already there, you can throw in an
Nz
function to give you a starting value:

NewAccNum = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1

If you have a busy multi-user environment, it is possible using this
technique to have the same number allocated twice, if two users are
adding
an account at the same time and the second one starts before the first
has
saved the new record. In this case you must either (a) handle the
problem
when it occurs by generating a new number and alerting the user that it
has
changed or (b) by having a special "NextAccount" table which you open for
exclusive access, retrieve the value, increment the stored value, and
write
it back to the table.

In practice, these precautions are rarely necessary, so post back if you
are
interested in further details.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ken,
Thanks this makes sense. Now, can you tell me where I could look for
code
as
examples? I am a strong Access user, but not so good with Visual Basic.
If
I
have code to look at and play with I usally can figure out what I need.
Thanks again,
--
Jacqueline Staley


:

While an AutoNumber field can be used to auto-create incrementing
numbers,
it's not a good approach if you don't want gaps between the numbers,
and
if
you don't want negative numbers as the result.

It's best to write your own procedure to auto-increment values for
this
purpose. An easy one is to find the current, maximum value for the
field
in
the table, and then increment that value by one, and then assign that
value
as the new number.

--

Ken Snell
<MS ACCESS MVP>


Access 2003 question:
Does anyone know how to set up an automatic account number
generation
in
Access? I suspect that it can be done with the Auto number, but not
sure
how
to do this to allow for a never ending amount of accounts.

Is there an add-in or secondary add-in that can do this? I would
like
to
incorporate the date into the account number if possible.

I am construction a new database that formally did not have account
numbers
attached, the client was using the address ast the PK....
Any help will be greatly appreciated.
Thanks

 

[Jacqueline]

Graham, thanks so much I will let you know how it goes.
--
Jacqueline

Hi Jacqueline

Never fear! It's not as hard as it might seem

You are probably adding records to your table using a form.

If you want the new number to be visible to the user as they are typing in
the other details (name, address, etc) then use the form's BeforeInsert
event, which fires the moment data entry commences in a new record:

Private Sub Form_BeforeInsert()
Me![AccountNum] = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1
End Sub

If you don't need the new number to be visible immediately, then use the
form's BeforeUpdate event. This will delay the lookup until the moment the
new record is being saved, which reduces the window of opportunity for a
collision with another user:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![AccountNum]) Then
Me![AccountNum] = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1
End If
End Sub

Note that BeforeUpdate gets fired for every updated record, not just the new
ones, so you need to allocate a new number only if the record doesn't
already have one [If IsNull(Me![AccountNum])...]
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
This sounds like what I need. The client was previosly useing the mailing
address as the PK with all kinds of bad results. It is a small office I
would
not anticipate the user colision you described but will caution the users.

Now, though I am traking on the practically of what you gave me, how do I
apply that to a table? I need this account number to be my PK, and follow
the
account through the other tables. I did not think you could have a
calculate
filed in a table, so I thinking I have to do this in VB an area I am weak
in....
Thanks for your help on this.
Jacqueline
--
Jacqueline

Hi Jacqueline

If your account number is a simple numeric value (long integer), you can
generate the next one by using the DMax function and incrementing the
value
returned. In case there is no record already there, you can throw in an
Nz
function to give you a starting value:

NewAccNum = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1

If you have a busy multi-user environment, it is possible using this
technique to have the same number allocated twice, if two users are
adding
an account at the same time and the second one starts before the first
has
saved the new record. In this case you must either (a) handle the
problem
when it occurs by generating a new number and alerting the user that it
has
changed or (b) by having a special "NextAccount" table which you open for
exclusive access, retrieve the value, increment the stored value, and
write
it back to the table.

In practice, these precautions are rarely necessary, so post back if you
are
interested in further details.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ken,
Thanks this makes sense. Now, can you tell me where I could look for
code
as
examples? I am a strong Access user, but not so good with Visual Basic.
If
I
have code to look at and play with I usally can figure out what I need.
Thanks again,
--
Jacqueline Staley


:

While an AutoNumber field can be used to auto-create incrementing
numbers,
it's not a good approach if you don't want gaps between the numbers,
and
if
you don't want negative numbers as the result.

It's best to write your own procedure to auto-increment values for
this
purpose. An easy one is to find the current, maximum value for the
field
in
the table, and then increment that value by one, and then assign that
value
as the new number.

--

Ken Snell
<MS ACCESS MVP>


Access 2003 question:
Does anyone know how to set up an automatic account number
generation
in
Access? I suspect that it can be done with the Auto number, but not
sure
how
to do this to allow for a never ending amount of accounts.

Is there an add-in or secondary add-in that can do this? I would
like
to
incorporate the date into the account number if possible.

I am construction a new database that formally did not have account
numbers
attached, the client was using the address ast the PK....
Any help will be greatly appreciated.
Thanks

 

[Jacqueline]

This is interesting and I do reconize some of th code from my VB class that I
did not pay enough attention to...

Do you use this code in an input form?
--
Jacqueline

Does anyone know how to set up an automatic account number
generation in Access? I suspect that it can be done with the
Auto number, but not sure how to do this to allow for a never
ending amount of accounts.

Jacqueline,

I use something along these lines for any place I need a generated number
next in the sequence. The table tblCounter has all the current counts for
any field I track.

I pulled this out of my sub-form that creates the order lines for a purchase
order. I of course want to have a new line item for every item purchased;
and this code does that.

Very interesting is how I had to go and change the code sometime a few
months back from an Integer to a Long. Again one of those things you
may not think of when you design a database; especially if it is your first
Relational Access type database.

There may be other ways, and maybe even cleaner ways, but the following is
what I use and it seems to work for me.

Tim


If Me.NewRecord Then
'
' Dim intTestCount As Integer
' Dim intNextCount As Integer
Dim intTestCount As Long
Dim intNextCount As Long
Dim strCounterName As String
'
strCounterName = "ordLineCount"
intTestCount = Nz(DLookup("[nxtCountInteger]", "tblCounter",
"[nxtCountName] = '" & strCounterName & "'"))
intNextCount = intTestCount + 1
[ordlineId] = intNextCount
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger = " &
intNextCount & " WHERE [nxtCountName] = '" & strCounterName & "'"
'
End If

 

[Tim]

Does anyone know how to set up an automatic account number

Do you use this code in an input form?

Yes, the code I posted in an earlier message was from the input form that
accepts new line items on a purchase order. That same method used there is
also used in many other forms I have. I have the database generate new part
numbers, purchase order numbers, line items, employee id numbers (sequenced
by 3's), vendor part numbers, etc.....

When I went looking for a solution to self generate the numbers, this was
something I could look at and understand it, and it worked. Most of my
questions when I designed the database was answered right here in this
newsgroup. Before that, I had never made a relational database before. Our
database has operated for 3 years thus far and only now am I going to do any
major modifications to it's format.

Tim

 

[vasco]

Access 2003 question:
Does anyone know how to set up an automatic account number generation in
Access? I suspect that it can be done with the Auto number, but not sure how
to do this to allow for a never ending amount of accounts.

Is there an add-in or secondary add-in that can do this? I would like to
incorporate the date into the account number if possible.

I am construction a new database that formally did not have account numbers
attached, the client was using the address ast the PK....
Any help will be greatly appreciated.
Thanks

I see that some years ago:

Private sub Form_BeforeInsert(Cancer As Integer)
If DCount("MyField" , "MyTable") = 0 Then
me.MyField = 1
Else
me.MyField = DMax("MyField" , "MyTable") + 1
End If
End Sub

 

[Jacqueline]

Hi Graham,
Sorry to bug you again, but having problmes geting my code to work. In your
respoce you indicated that I could have the new account number generated by
using code in the BeforInsert event. I can't find it?? I have built the form,
and now tringing to code the field, I can see the BeforeUpdate but not the
BeforInsert, what am I missing... not holding my mouth right I guess...
Thanks again
Jacqueline Staley

Hi Jacqueline

If your account number is a simple numeric value (long integer), you can
generate the next one by using the DMax function and incrementing the value
returned. In case there is no record already there, you can throw in an Nz
function to give you a starting value:

NewAccNum = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1

If you have a busy multi-user environment, it is possible using this
technique to have the same number allocated twice, if two users are adding
an account at the same time and the second one starts before the first has
saved the new record. In this case you must either (a) handle the problem
when it occurs by generating a new number and alerting the user that it has
changed or (b) by having a special "NextAccount" table which you open for
exclusive access, retrieve the value, increment the stored value, and write
it back to the table.

In practice, these precautions are rarely necessary, so post back if you are
interested in further details.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Jacqueline]

Graham,
Disregard my last email, I panicked before looking further. I was in the
form field instead of the form, so of course Before insert was not availale
until I moved to form properties.

I input your code and it works great. I hand entered the first account to
start the number where I wanted and it is now generating great. Thanks so
much for your help.
--
Jacqueline Staley

Hi Jacqueline

If your account number is a simple numeric value (long integer), you can
generate the next one by using the DMax function and incrementing the value
returned. In case there is no record already there, you can throw in an Nz
function to give you a starting value:

NewAccNum = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1

If you have a busy multi-user environment, it is possible using this
technique to have the same number allocated twice, if two users are adding
an account at the same time and the second one starts before the first has
saved the new record. In this case you must either (a) handle the problem
when it occurs by generating a new number and alerting the user that it has
changed or (b) by having a special "NextAccount" table which you open for
exclusive access, retrieve the value, increment the stored value, and write
it back to the table.

In practice, these precautions are rarely necessary, so post back if you are
interested in further details.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Jacqueline

Glad you got it working. Thanks for the feedback.

Incidentally, if you want to start at a particular number, say 10001, just
replace the 0 in the Nz function with 10000 (first number less one).
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Disregard my last email, I panicked before looking further. I was in the
form field instead of the form, so of course Before insert was not
availale
until I moved to form properties.

I input your code and it works great. I hand entered the first account to
start the number where I wanted and it is now generating great. Thanks so
much for your help.
--
Jacqueline Staley

Hi Jacqueline

If your account number is a simple numeric value (long integer), you can
generate the next one by using the DMax function and incrementing the
value
returned. In case there is no record already there, you can throw in an
Nz
function to give you a starting value:

NewAccNum = Nz( DMax( "[AccountNum]", "YourTable" ), 0 ) + 1

If you have a busy multi-user environment, it is possible using this
technique to have the same number allocated twice, if two users are
adding
an account at the same time and the second one starts before the first
has
saved the new record. In this case you must either (a) handle the
problem
when it occurs by generating a new number and alerting the user that it
has
changed or (b) by having a special "NextAccount" table which you open for
exclusive access, retrieve the value, increment the stored value, and
write
it back to the table.

In practice, these precautions are rarely necessary, so post back if you
are
interested in further details.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ken,
Thanks this makes sense. Now, can you tell me where I could look for
code
as
examples? I am a strong Access user, but not so good with Visual Basic.
If
I
have code to look at and play with I usally can figure out what I need.
Thanks again,
--
Jacqueline Staley


:

While an AutoNumber field can be used to auto-create incrementing
numbers,
it's not a good approach if you don't want gaps between the numbers,
and
if
you don't want negative numbers as the result.

It's best to write your own procedure to auto-increment values for
this
purpose. An easy one is to find the current, maximum value for the
field
in
the table, and then increment that value by one, and then assign that
value
as the new number.

--

Ken Snell
<MS ACCESS MVP>


Access 2003 question:
Does anyone know how to set up an automatic account number
generation
in
Access? I suspect that it can be done with the Auto number, but not
sure
how
to do this to allow for a never ending amount of accounts.

Is there an add-in or secondary add-in that can do this? I would
like
to
incorporate the date into the account number if possible.

I am construction a new database that formally did not have account
numbers
attached, the client was using the address ast the PK....
Any help will be greatly appreciated.
Thanks