If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Order Number Unique Number Auto Incrementing
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 |
#2
|
|||
|
|||
Order Number Unique Number Auto Incrementing
"Louise54" wrote in message ... 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. |
#3
|
|||
|
|||
Order Number Unique Number Auto Incrementing
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" wrote: "Louise54" wrote in message ... 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. |
#4
|
|||
|
|||
Order Number Unique Number Auto Incrementing
You're welcome ;-)
"Louise54" wrote in message ... 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" wrote: "Louise54" wrote in message ... 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. |
#5
|
|||
|
|||
Order Number Unique Number Auto Incrementing
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. -- Dave Hargis, Microsoft Access MVP "Louise54" wrote: 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" wrote: "Louise54" wrote in message ... 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. |
#6
|
|||
|
|||
Order Number Unique Number Auto Incrementing
Be ready to deal with gaps in your invoice numbers.
-- Dave Hargis, Microsoft Access MVP "Louise54" wrote: 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" wrote: "Louise54" wrote in message ... 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. |
#7
|
|||
|
|||
Order Number Unique Number Auto Incrementing
"Klatuu" wrote in message ... 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. |
#8
|
|||
|
|||
Order Number Unique Number Auto Incrementing
"Klatuu" wrote in message ... 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. |
#9
|
|||
|
|||
Order Number Unique Number Auto Incrementing
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 "Amy Blankenship" wrote: "Klatuu" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|