Automatically assign (sort of) incremental numbers

[angi35]

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

[ADezii]

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:
[CODE=vb]
Public Function fIncrementOrder Number()
Dim lngLastOrderNum As Long

lngLastOrderNum = DLast("[OrderNo]", "tblOrders" )

If lngLastOrderNum >= 90100 And lngLastOrderNum <= 90998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 90999 Then
fIncrementOrder Number = 100100
ElseIf lngLastOrderNum >= 80100 And lngLastOrderNum <= 80998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 80999 Then
fIncrementOrder Number = 90100
ElseIf lngLastOrderNum >= 70100 And lngLastOrderNum <= 70998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 70999 Then
fIncrementOrder Number = 80100
ElseIf lngLastOrderNum >= 60100 And lngLastOrderNum <= 60998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 60999 Then
fIncrementOrder Number = 70100
ElseIf lngLastOrderNum >= 50100 And lngLastOrderNum <= 50998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 50999 Then
fIncrementOrder Number = 60100
'fill in the missing blocks
'...
'...
'...
Else
fIncrementOrder Number = lngLastOrderNum + 1
End If
End Function[/CODE]

[angi35]

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:
[CODE=vb]
Public Function fIncrementOrder Number()
Dim lngLastOrderNum As Long

lngLastOrderNum = DLast("[OrderNo]", "tblOrders" )

If lngLastOrderNum >= 90100 And lngLastOrderNum <= 90998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 90999 Then
fIncrementOrder Number = 100100
ElseIf lngLastOrderNum >= 80100 And lngLastOrderNum <= 80998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 80999 Then
fIncrementOrder Number = 90100
ElseIf lngLastOrderNum >= 70100 And lngLastOrderNum <= 70998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 70999 Then
fIncrementOrder Number = 80100
ElseIf lngLastOrderNum >= 60100 And lngLastOrderNum <= 60998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 60999 Then
fIncrementOrder Number = 70100
ElseIf lngLastOrderNum >= 50100 And lngLastOrderNum <= 50998 Then
fIncrementOrder Number = lngLastOrderNum + 1
ElseIf lngLastOrderNum = 50999 Then
fIncrementOrder Number = 60100
'fill in the missing blocks
'...
'...
'...
Else
fIncrementOrder Number = lngLastOrderNum + 1
End If
End Function[/CODE]

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" .

[ADezii]

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 =fIncrementOrde rNumber(). 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.

[angi35]

Select the JobNumber Field in Design Mode ==> Right Click ==> Properties ==> Data Tab ==> set the Default Value Property to =fIncrementOrde rNumber(). 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

[ADezii]

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.

[angi35]

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.

[Scott Price]

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

[ADezii]

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 =fIncrementOrde rNumber() 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:
   [CODE=vb]
   Private Sub Form_Current()
   If Me.NewRecord Then Me![JobNumber] = fIncrementOrder Number()
   End Sub[/CODE]
4. Let me know how this works out.
5. Again, I apologize for the inconvenience - 1 too many Posts I guess. (LOL).

[angi35]

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 =fIncrementOrde rNumber() 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:
   [CODE=vb]
   Private Sub Form_Current()
   If Me.NewRecord Then Me![JobNumber] = fIncrementOrder Number()
   End Sub[/CODE]
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

[ADezii]

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.

[angi35]

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

[ADezii]

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.

[angi35]

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

[ADezii]

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?