By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,149 Members | 885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,149 IT Pros & Developers. It's quick & easy.

Automatically assign (sort of) incremental numbers

P: 55
Hi - I'm working in Access 2000. I've got a strange numbering system for work orders, and I'm wondering if, nonetheless, there's a way to get Access to assign the next incremental number for a new order. I've been reading the thread: http://www.thescripts.com/forum/thre...gn+number.html , which is helpful, but here's the system I'm working with: Start at 30100, and count up to 30999. Then jump to 40100, and count to 40999. Now jump to 50100. And so on.

I do not ask why, I only write code. But for this, I need some help!

Angi
Feb 13 '08 #1
Share this Question
Share on Google+
17 Replies


ADezii
Expert 5K+
P: 8,638
Hi - I'm working in Access 2000. I've got a strange numbering system for work orders, and I'm wondering if, nonetheless, there's a way to get Access to assign the next incremental number for a new order. I've been reading the thread: http://www.thescripts.com/forum/thre...gn+number.html , which is helpful, but here's the system I'm working with: Start at 30100, and count up to 30999. Then jump to 40100, and count to 40999. Now jump to 50100. And so on.

I do not ask why, I only write code. But for this, I need some help!

Angi
Assuming a Table Name of tblOrders, and a Field Name of [OrderID] {LONG}, the following Public Functioin should point you in the right direction, just fill in the missing pieces and let me know how you make out:
Expand|Select|Wrap|Line Numbers
  1. Public Function fIncrementOrderNumber()
  2. Dim lngLastOrderNum As Long
  3.  
  4. lngLastOrderNum = DLast("[OrderNo]", "tblOrders")
  5.  
  6. If lngLastOrderNum >= 90100 And lngLastOrderNum <= 90998 Then
  7.   fIncrementOrderNumber = lngLastOrderNum + 1
  8. ElseIf lngLastOrderNum = 90999 Then
  9.   fIncrementOrderNumber = 100100
  10. ElseIf lngLastOrderNum >= 80100 And lngLastOrderNum <= 80998 Then
  11.   fIncrementOrderNumber = lngLastOrderNum + 1
  12. ElseIf lngLastOrderNum = 80999 Then
  13.   fIncrementOrderNumber = 90100
  14. ElseIf lngLastOrderNum >= 70100 And lngLastOrderNum <= 70998 Then
  15.   fIncrementOrderNumber = lngLastOrderNum + 1
  16. ElseIf lngLastOrderNum = 70999 Then
  17.   fIncrementOrderNumber = 80100
  18. ElseIf lngLastOrderNum >= 60100 And lngLastOrderNum <= 60998 Then
  19.   fIncrementOrderNumber = lngLastOrderNum + 1
  20. ElseIf lngLastOrderNum = 60999 Then
  21.   fIncrementOrderNumber = 70100
  22. ElseIf lngLastOrderNum >= 50100 And lngLastOrderNum <= 50998 Then
  23.   fIncrementOrderNumber = lngLastOrderNum + 1
  24. ElseIf lngLastOrderNum = 50999 Then
  25.   fIncrementOrderNumber = 60100
  26. 'fill in the missing blocks
  27. '...
  28. '...
  29. '...
  30. Else
  31.   fIncrementOrderNumber = lngLastOrderNum + 1
  32. End If
  33. End Function
Feb 13 '08 #2

P: 55
Assuming a Table Name of tblOrders, and a Field Name of [OrderID] {LONG}, the following Public Functioin should point you in the right direction, just fill in the missing pieces and let me know how you make out:
Expand|Select|Wrap|Line Numbers
  1. Public Function fIncrementOrderNumber()
  2. Dim lngLastOrderNum As Long
  3.  
  4. lngLastOrderNum = DLast("[OrderNo]", "tblOrders")
  5.  
  6. If lngLastOrderNum >= 90100 And lngLastOrderNum <= 90998 Then
  7.   fIncrementOrderNumber = lngLastOrderNum + 1
  8. ElseIf lngLastOrderNum = 90999 Then
  9.   fIncrementOrderNumber = 100100
  10. ElseIf lngLastOrderNum >= 80100 And lngLastOrderNum <= 80998 Then
  11.   fIncrementOrderNumber = lngLastOrderNum + 1
  12. ElseIf lngLastOrderNum = 80999 Then
  13.   fIncrementOrderNumber = 90100
  14. ElseIf lngLastOrderNum >= 70100 And lngLastOrderNum <= 70998 Then
  15.   fIncrementOrderNumber = lngLastOrderNum + 1
  16. ElseIf lngLastOrderNum = 70999 Then
  17.   fIncrementOrderNumber = 80100
  18. ElseIf lngLastOrderNum >= 60100 And lngLastOrderNum <= 60998 Then
  19.   fIncrementOrderNumber = lngLastOrderNum + 1
  20. ElseIf lngLastOrderNum = 60999 Then
  21.   fIncrementOrderNumber = 70100
  22. ElseIf lngLastOrderNum >= 50100 And lngLastOrderNum <= 50998 Then
  23.   fIncrementOrderNumber = lngLastOrderNum + 1
  24. ElseIf lngLastOrderNum = 50999 Then
  25.   fIncrementOrderNumber = 60100
  26. 'fill in the missing blocks
  27. '...
  28. '...
  29. '...
  30. Else
  31.   fIncrementOrderNumber = lngLastOrderNum + 1
  32. End If
  33. End Function

This sounds good, but where do I put it? I've only ever worked with Private Sub statements, where you pick where and when the event will occur through the properties box (event procedure of a form 'on current' or of a control 'on click', etc.), and when you end up in the VB screen, it's already set up, for instance, "Private Sub Form_On Current()". What do I do with the public function code?

The form is named "Add Job". The table is "1 JOB", and the field is "JobNumber".
Feb 13 '08 #3

ADezii
Expert 5K+
P: 8,638
This sounds good, but where do I put it? I've only ever worked with Private Sub statements, where you pick where and when the event will occur through the properties box (event procedure of a form 'on current' or of a control 'on click', etc.), and when you end up in the VB screen, it's already set up, for instance, "Private Sub Form_On Current()". What do I do with the public function code?

The form is named "Add Job". The table is "1 JOB", and the field is "JobNumber".
Select the JobNumber Field in Design Mode ==> Right Click ==> Properties ==> Data Tab ==> set the Default Value Property to =fIncrementOrderNumber(). Now, when ever a New Record is added, the Function will be executed, and the next number in sequence will automatically be added to the JobNumber Field.
Feb 14 '08 #4

P: 55
Select the JobNumber Field in Design Mode ==> Right Click ==> Properties ==> Data Tab ==> set the Default Value Property to =fIncrementOrderNumber(). Now, when ever a New Record is added, the Function will be executed, and the next number in sequence will automatically be added to the JobNumber Field.
OK, it's sort of working, but not quite. I'm working with a continuous form, so when I go to the last record, there's a placeholder for a new record below the last record, and it shows the next number in the sequence. Let's say the last existing record is 30104, and the placeholder for the next record shows 30105. I create record 30105 (by entering data in additional controls), which means that the form creates the placeholder for 30106. If I exit the form without creating record 30106, then open the form again, 30106 still shows as the next new number. But when I start entering a record there, so that it creates the placeholder for the next new record, it duplicates 30106 as the next new number. (If I go ahead and start to create the second 30106 record, the next placeholder is 30107.)

Angi
Feb 14 '08 #5

ADezii
Expert 5K+
P: 8,638
OK, it's sort of working, but not quite. I'm working with a continuous form, so when I go to the last record, there's a placeholder for a new record below the last record, and it shows the next number in the sequence. Let's say the last existing record is 30104, and the placeholder for the next record shows 30105. I create record 30105 (by entering data in additional controls), which means that the form creates the placeholder for 30106. If I exit the form without creating record 30106, then open the form again, 30106 still shows as the next new number. But when I start entering a record there, so that it creates the placeholder for the next new record, it duplicates 30106 as the next new number. (If I go ahead and start to create the second 30106 record, the next placeholder is 30107.)

Angi
If I exit the form without creating record 30106, then open the form again, 30106 still shows as the next new number. But when I start entering a record there, so that it creates the placeholder for the next new record, it duplicates 30106 as the next new number. (If I go ahead and start to create the second 30106 record, the next placeholder is 30107.
Sorry Angi, but to me it seems to be doing exactly what it is supposed to be doing.
Feb 14 '08 #6

P: 55
Sorry Angi, but to me it seems to be doing exactly what it is supposed to be doing.
Well, how strange. It's definitely doing funny math for me.
Feb 14 '08 #7

Scott Price
Expert 100+
P: 1,384
Well, how strange. It's definitely doing funny math for me.
When creating your 'second' 30106 record, does it then give you an error message something to the effect of "Duplicate values in the Primary Key field." ?? If not, then I'm with Adezii, it's doing what it is supposed to. Otherwise, could you give another go at explaining just what it's doing wrong?

Regards,
Scott
Feb 14 '08 #8

ADezii
Expert 5K+
P: 8,638
Well, how strange. It's definitely doing funny math for me.
I apologize angi35, since the mistake was on my part, not yours! There is one small detail that I forgot about, and that is you are using a Continuous Form. Here is what is happening and why: when you Add a New Record, the Job Number increments as it should, but unfortunately so does the Tentative Append Record (anticipated Record after the New Record). The Tentative Append Record shows the same Job Number as the New Record because the New Record has not been saved and the code reads the last Job Number of the Saved Record above it which will now be the same for both. I'm sorry if I am confusing you, but it is not a simple concept to explain, as least for me. Now let's see if we can make it up to you:
  1. In the [JobNumber] Field, DELETE the =fIncrementOrderNumber() line in the Default Value Property, that logic is out the window with a Continuous Form.
  2. Set the Locked Property of the [JobNumber] Field on your Form to Yes. This step is critical since only code should be able to enter values in this Field.
  3. In the Current() Event of your Form, place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2.   If Me.NewRecord Then Me![JobNumber] = fIncrementOrderNumber()
    3. End Sub
  4. Let me know how this works out.
  5. Again, I apologize for the inconvenience - 1 too many Posts I guess. (LOL).
Feb 15 '08 #9

P: 55
I apologize angi35, since the mistake was on my part, not yours! There is one small detail that I forgot about, and that is you are using a Continuous Form. Here is what is happening and why: when you Add a New Record, the Job Number increments as it should, but unfortunately so does the Tentative Append Record (anticipated Record after the New Record). The Tentative Append Record shows the same Job Number as the New Record because the New Record has not been saved and the code reads the last Job Number of the Saved Record above it which will now be the same for both. I'm sorry if I am confusing you, but it is not a simple concept to explain, as least for me. Now let's see if we can make it up to you:
  1. In the [JobNumber] Field, DELETE the =fIncrementOrderNumber() line in the Default Value Property, that logic is out the window with a Continuous Form.
  2. Set the Locked Property of the [JobNumber] Field on your Form to Yes. This step is critical since only code should be able to enter values in this Field.
  3. In the Current() Event of your Form, place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2.   If Me.NewRecord Then Me![JobNumber] = fIncrementOrderNumber()
    3. End Sub
  4. Let me know how this works out.
  5. Again, I apologize for the inconvenience - 1 too many Posts I guess. (LOL).

I figured it probably had something to do with it being a continuous form. And sure enough, that works! Thanks.

Angi
Feb 15 '08 #10

ADezii
Expert 5K+
P: 8,638
I figured it probably had something to do with it being a continuous form. And sure enough, that works! Thanks.

Angi
You are quite welcome, angi.
Feb 15 '08 #11

P: 55
I figured it probably had something to do with it being a continuous form. And sure enough, that works! Thanks.

Angi

Wait! One more quirk. I hope I can explain this...

#30103 is the last record. The new record box shows '0' for the JobNumber until I start adding data in the other controls; then of course it turns to 30104 (with the pencil symbol next to it), and there's a new 'new record' line below 30104 that shows number '0'. So far so good.

30103 XYZ Company Someplace USA
0

30103 XYZ Company Someplace USA
30104 New Company ...
0

Now I realize I don't want to add 30104. So I delete the record. But although the record is deleted, the line doesn't disappear. Instead, the line that was formerly 30104 now becomes 30105, with the pencil symbol next to it, and 'new' record '0' is still below it.

30103 XYZ Company Someplace USA
30105
0

I try to delete what is now 30105, and it just turns into 30106, still with a 'new record' below it.

Of course, if I go directly into the table to delete 30104 instead of doing it in the form, it goes away altogether. Then I can go back into the form and have 30103, and new record 0 will become 30104 again. Fine for me, but not for the users who will actually be entering the new jobs.

What I want is: a. If I delete 30104 in the form, that record goes away entirely, and I'm back to 30103 and new record 0.... b. Then new record 0 becomes 30104 (not 30105) when I start entering data (I'm ok with it if this requires exiting and reopening the form).

Ideas?

Angi
Feb 15 '08 #12

ADezii
Expert 5K+
P: 8,638
Wait! One more quirk. I hope I can explain this...

#30103 is the last record. The new record box shows '0' for the JobNumber until I start adding data in the other controls; then of course it turns to 30104 (with the pencil symbol next to it), and there's a new 'new record' line below 30104 that shows number '0'. So far so good.

30103 XYZ Company Someplace USA
0

30103 XYZ Company Someplace USA
30104 New Company ...
0

Now I realize I don't want to add 30104. So I delete the record. But although the record is deleted, the line doesn't disappear. Instead, the line that was formerly 30104 now becomes 30105, with the pencil symbol next to it, and 'new' record '0' is still below it.

30103 XYZ Company Someplace USA
30105
0

I try to delete what is now 30105, and it just turns into 30106, still with a 'new record' below it.

Of course, if I go directly into the table to delete 30104 instead of doing it in the form, it goes away altogether. Then I can go back into the form and have 30103, and new record 0 will become 30104 again. Fine for me, but not for the users who will actually be entering the new jobs.

What I want is: a. If I delete 30104 in the form, that record goes away entirely, and I'm back to 30103 and new record 0.... b. Then new record 0 becomes 30104 (not 30105) when I start entering data (I'm ok with it if this requires exiting and reopening the form).

Ideas?

Angi
Angi, would it be possible to send me the Database as an E-Mail Attachment and I'll have a look at it? It would simplify matters.
Feb 15 '08 #13

P: 55
Angi, would it be possible to send me the Database as an E-Mail Attachment and I'll have a look at it? It would simplify matters.

Sorry ADezii - I'm a little confused on how to do that, being rather new to this site... I tried sending you an email but it wouldn't let me. And I don't see anything on the regular reply post page for attaching a file. Just tell me how, and I'll get the database to you.

Angi
Feb 15 '08 #14

ADezii
Expert 5K+
P: 8,638
Sorry ADezii - I'm a little confused on how to do that, being rather new to this site... I tried sending you an email but it wouldn't let me. And I don't see anything on the regular reply post page for attaching a file. Just tell me how, and I'll get the database to you.

Angi
I'm going to send you my E-Mail Address in a Private Message. Next, just send an E-Mail to my address just as you would send any E-Mail outside of TheScripts. Attach your Database, as a Zip File, to the E-Mail message that you are sending me. Are you OK with all this?
Feb 15 '08 #15

ADezii
Expert 5K+
P: 8,638
Wait! One more quirk. I hope I can explain this...

#30103 is the last record. The new record box shows '0' for the JobNumber until I start adding data in the other controls; then of course it turns to 30104 (with the pencil symbol next to it), and there's a new 'new record' line below 30104 that shows number '0'. So far so good.

30103 XYZ Company Someplace USA
0

30103 XYZ Company Someplace USA
30104 New Company ...
0

Now I realize I don't want to add 30104. So I delete the record. But although the record is deleted, the line doesn't disappear. Instead, the line that was formerly 30104 now becomes 30105, with the pencil symbol next to it, and 'new' record '0' is still below it.

30103 XYZ Company Someplace USA
30105
0

I try to delete what is now 30105, and it just turns into 30106, still with a 'new record' below it.

Of course, if I go directly into the table to delete 30104 instead of doing it in the form, it goes away altogether. Then I can go back into the form and have 30103, and new record 0 will become 30104 again. Fine for me, but not for the users who will actually be entering the new jobs.

What I want is: a. If I delete 30104 in the form, that record goes away entirely, and I'm back to 30103 and new record 0.... b. Then new record 0 becomes 30104 (not 30105) when I start entering data (I'm ok with it if this requires exiting and reopening the form).

Ideas?

Angi
Hello Angi, download the Test Database related to this Thread to see the changes that I have made, then let me know what you think.
Feb 16 '08 #16

P: 55
Hello Angi, download the Test Database related to this Thread to see the changes that I have made, then let me know what you think.
Looks like it's working just right. Thanks again, ADezii!

Angi
Feb 19 '08 #17

ADezii
Expert 5K+
P: 8,638
Looks like it's working just right. Thanks again, ADezii!

Angi
You are quite welcome, Angi.
Feb 19 '08 #18

Post your reply

Sign in to post your reply or Sign up for a free account.