Order Number Unique Number Auto Incrementing

[Louise54]

I know there's a bunch of info on this here, but can't seem to find my exact
siuation. I can't write code, so have to rely on Macros, etc.

We use an order number system that goes like this: 20025358 and increments
by 1 for each new order. They must be unique, and multiple users will be
entering orders.

If I use an autonumber field for this number, will I be able to modify it to
start with a number such as above, easily enough? Or am I better off doing it
a different way?
Thanks so much for helping a very novice user.
Louise

 

[Amy Blankenship]

I know there's a bunch of info on this here, but can't seem to find my
exact
siuation. I can't write code, so have to rely on Macros, etc.

We use an order number system that goes like this: 20025358 and increments
by 1 for each new order. They must be unique, and multiple users will be
entering orders.

If I use an autonumber field for this number, will I be able to modify it
to
start with a number such as above, easily enough? Or am I better off doing
it
a different way?

If you want to have absolute control, write your own code to look up the max
index and add one. That has its dangers, though, if something gets deleted.
You can start the table where you want by using an INSERT query for the
first record and providing your own starting index. However, you can get a
similar effect by starting it at 1 and adding, for instance, 100000000 to
the actual autonumber value for display.

 

[Louise54]

So, if I understand you right, I would use the autonumber field and let it
start at 1, and then when ready to start using the application (for example,
we will start with order # 20020098) I would use a simple calculation to add
20020097 to the autonumber to arrive at the "finished" number? That sounds
pretty simple!
Thanks so much.

 

[Amy Blankenship]

You're welcome ;-)

So, if I understand you right, I would use the autonumber field and let it
start at 1, and then when ready to start using the application (for
example,
we will start with order # 20020098) I would use a simple calculation to
add
20020097 to the autonumber to arrive at the "finished" number? That sounds
pretty simple!
Thanks so much.

 

[Klatuu]

Do not use an autonumber for this. Autonumbers should only be used for
artificial primary keys to relate tables.
One issue you have here is that you can't control the numbers. And, if a
user starts an invoice but doesn't complete it, that number will be lost.
Then your auditor will be wanting to know what happened to invoice 20020145.
Try and explain that.

If you want to find the highest invoice number and add 1 to it:

Me.txtInvNo = Nz(DMax("[INV_NO]", "tblInvHeader"),0) + 1

Now, that doesn't directly address the multi user issue because two users
(or more) could get the same number to work with and you don't want to save
that number to the table immediately because you end up with the same missing
invoice number problem you would have with an autonumber.

The solution to that is to use the form's Before Update event to be sure the
invoice number hasn't been saved by someone else. If it has, warn the user
and increment the invoice number.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[INV_NO]", "tblInvHeader", "[INV_NO] = " &
Me.txtInvNo)) Then
Cancel = True
MsgBox "Invoice " & Me.txtInvNo & " Is In use" & vbNewLine &
"Assigning Next Available Number"
Me.txtInvNo = Nz(DMax("[INV_NO]", "tblInvHeader"),0) + 1
End If
End Sub

Sorry, but Macros are not smart enough to do this sort of thing. You can
only avoid VBA so long if you need to develop an really useful application.

We would be happy to walk you through getting this accomplished, if you like.

 

[Klatuu]

Be ready to deal with gaps in your invoice numbers.

 

[Amy Blankenship]

Do not use an autonumber for this. Autonumbers should only be used for
artificial primary keys to relate tables.
One issue you have here is that you can't control the numbers. And, if a
user starts an invoice but doesn't complete it, that number will be lost.
Then your auditor will be wanting to know what happened to invoice
20020145.
Try and explain that.

If you want to find the highest invoice number and add 1 to it:

Me.txtInvNo = Nz(DMax("[INV_NO]", "tblInvHeader"),0) + 1

Now, that doesn't directly address the multi user issue because two users
(or more) could get the same number to work with and you don't want to
save
that number to the table immediately because you end up with the same
missing
invoice number problem you would have with an autonumber.

The solution to that is to use the form's Before Update event to be sure
the
invoice number hasn't been saved by someone else. If it has, warn the
user
and increment the invoice number.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[INV_NO]", "tblInvHeader", "[INV_NO] = " &
Me.txtInvNo)) Then
Cancel = True
MsgBox "Invoice " & Me.txtInvNo & " Is In use" & vbNewLine &
"Assigning Next Available Number"
Me.txtInvNo = Nz(DMax("[INV_NO]", "tblInvHeader"),0) + 1
End If
End Sub

Sorry, but Macros are not smart enough to do this sort of thing. You can
only avoid VBA so long if you need to develop an really useful
application.

We would be happy to walk you through getting this accomplished, if you
like.

Of course, if someone deletes the top number after adding detail records to
it, you could wind up with a problem.

 

[Amy Blankenship]

Be ready to deal with gaps in your invoice numbers.

Note that I told her if she needed absolute control over the number ,she
should code it. She seems more interested at just having it start at a high
number.

 

[Klatuu]

True, but it would be a business rule issue rather than a technical issue.
The usual practice would be to not allow a delete of the Order Header
record, but use some method to identify it as "Deleted" or "Void"

--
Dave Hargis, Microsoft Access MVP

Do not use an autonumber for this. Autonumbers should only be used for
artificial primary keys to relate tables.
One issue you have here is that you can't control the numbers. And, if a
user starts an invoice but doesn't complete it, that number will be lost.
Then your auditor will be wanting to know what happened to invoice
20020145.
Try and explain that.

If you want to find the highest invoice number and add 1 to it:

Me.txtInvNo = Nz(DMax("[INV_NO]", "tblInvHeader"),0) + 1

Now, that doesn't directly address the multi user issue because two users
(or more) could get the same number to work with and you don't want to
save
that number to the table immediately because you end up with the same
missing
invoice number problem you would have with an autonumber.

The solution to that is to use the form's Before Update event to be sure
the
invoice number hasn't been saved by someone else. If it has, warn the
user
and increment the invoice number.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[INV_NO]", "tblInvHeader", "[INV_NO] = " &
Me.txtInvNo)) Then
Cancel = True
MsgBox "Invoice " & Me.txtInvNo & " Is In use" & vbNewLine &
"Assigning Next Available Number"
Me.txtInvNo = Nz(DMax("[INV_NO]", "tblInvHeader"),0) + 1
End If
End Sub

Sorry, but Macros are not smart enough to do this sort of thing. You can
only avoid VBA so long if you need to develop an really useful
application.

We would be happy to walk you through getting this accomplished, if you
like.

Of course, if someone deletes the top number after adding detail records to
it, you could wind up with a problem.