two part sequential purchase order numbers

[Guest]

I am trying to track project managers purchase orders, the first part of
their PO number is the specific 4 digit project number, then a dash, then a
sequential 5 digit number starting with 00001

example, 9999-00001
but other project manager in same database would start 8888-00001
i need the next sequential number for both to be 00002
i am not sure how to track this for many project numbers, i am hoping there
is a simple way to do this without a ton of programming as this is supposed
to be a simple solution for our comany....

 

[Allen Browne]

Use two fields:
- one for the project number;
- one for the purchase within that project.

The next purchase number for project 9999 is then:
Nz(DMax("[PurchNum]", "[MyPurchaseTable]", "[ProjectNum] = 9999"),0)+1

 

[Guest]

this would seem to imply that i can only use project number 9999
or i am completely dumb =)
i need to be able to use many project numbers, sorry if i am an idiot and
completely missed the point...

Byron

Use two fields:
- one for the project number;
- one for the purchase within that project.

The next purchase number for project 9999 is then:
Nz(DMax("[PurchNum]", "[MyPurchaseTable]", "[ProjectNum] = 9999"),0)+1

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to track project managers purchase orders, the first part of
their PO number is the specific 4 digit project number, then a dash, then
a
sequential 5 digit number starting with 00001

example, 9999-00001
but other project manager in same database would start 8888-00001
i need the next sequential number for both to be 00002
i am not sure how to track this for many project numbers, i am hoping
there
is a simple way to do this without a ton of programming as this is
supposed
to be a simple solution for our comany....

 

[John Vinson]

I am trying to track project managers purchase orders, the first part of
their PO number is the specific 4 digit project number, then a dash, then a
sequential 5 digit number starting with 00001

example, 9999-00001
but other project manager in same database would start 8888-00001
i need the next sequential number for both to be 00002
i am not sure how to track this for many project numbers, i am hoping there
is a simple way to do this without a ton of programming as this is supposed
to be a simple solution for our comany....

Just to amplify on Allen's suggestion: you will need about two ounces
of programming (not a ton <g>) but you will need some. For one thing
you must - no option! - do all your data entry in a Form, since tables
don't have any usable events. Your table should have a ProjectNumber
field, I'd suggest of Text datatype, and a sequential Long Integer
field I'll call SeqNo. You'll want to have a separate table of
projects, and probably a combo box on the form bound to the
ProjectNumber field (I'll call the combo box cboProjectNumber).

In the AfterUpdate event of the ProjectNumber field put code like:

Private Sub cboProjectNumber_AfterUpdate()
If IsNull(Me!SeqNo) Then ' don't overwrite existing SeqNo
Me!SeqNo = NZ(DMax("[SeqNo]", "[Your-table-name]", _
"[ProjectNumber] = '" & Me!cboProjectNumber & "'")) + 1
End If
End Sub

This will find the largest existing SeqNo for the selected project
number; if there is no SeqNo for that project at all, the NZ()
function will give you a zero. Adding 1 to that value will give the
next number in the sequence.

You can have textbox on a form or report with a control source

=[ProjectNumber] & Format([SeqNo], "-00000")

to display the concatenated identifier. It should not be stored in
your table in concatenated form though!

John W. Vinson[MVP]

 

[Guest]

I am trying to track project managers purchase orders, the first part of
their PO number is the specific 4 digit project number, then a dash, then a
sequential 5 digit number starting with 00001

example, 9999-00001
but other project manager in same database would start 8888-00001
i need the next sequential number for both to be 00002
i am not sure how to track this for many project numbers, i am hoping there
is a simple way to do this without a ton of programming as this is supposed
to be a simple solution for our comany....

Just to amplify on Allen's suggestion: you will need about two ounces
of programming (not a ton <g>) but you will need some. For one thing
you must - no option! - do all your data entry in a Form, since tables
don't have any usable events. Your table should have a ProjectNumber
field, I'd suggest of Text datatype, and a sequential Long Integer
field I'll call SeqNo. You'll want to have a separate table of
projects, and probably a combo box on the form bound to the
ProjectNumber field (I'll call the combo box cboProjectNumber).

In the AfterUpdate event of the ProjectNumber field put code like:

Private Sub cboProjectNumber_AfterUpdate()
If IsNull(Me!SeqNo) Then ' don't overwrite existing SeqNo
Me!SeqNo = NZ(DMax("[SeqNo]", "[Your-table-name]", _
"[ProjectNumber] = '" & Me!cboProjectNumber & "'")) + 1
End If
End Sub

This will find the largest existing SeqNo for the selected project
number; if there is no SeqNo for that project at all, the NZ()
function will give you a zero. Adding 1 to that value will give the
next number in the sequence.

You can have textbox on a form or report with a control source

=[ProjectNumber] & Format([SeqNo], "-00000")

to display the concatenated identifier. It should not be stored in
your table in concatenated form though!

John W. Vinson[MVP]

This makes a bit more sense, however do i really want a sequential long
integer field?? would that defeat the purpose of this formula?? it would
seem that if:

project manager A entered 8804 for his project number, he would get 8804-00001

project manager B entered 8904 for his project number, he would get
8904-00002 where i would want hime to get 8904-00001

also would the combo box be bound to the project number field in the table
of projects or to the project number field in my original table...

thanks in advance for the help, i really appreciate it, and i feel like an
idiot at this point... if you would like to respond off forum please respond
to

bolsen at oregoniron dot com

 

[Guest]

Thanks everyone, it is all very clear now and I got it working!!!

Byron

I am trying to track project managers purchase orders, the first part of
their PO number is the specific 4 digit project number, then a dash, then a
sequential 5 digit number starting with 00001

example, 9999-00001
but other project manager in same database would start 8888-00001
i need the next sequential number for both to be 00002
i am not sure how to track this for many project numbers, i am hoping there
is a simple way to do this without a ton of programming as this is supposed
to be a simple solution for our comany....

Just to amplify on Allen's suggestion: you will need about two ounces
of programming (not a ton <g>) but you will need some. For one thing
you must - no option! - do all your data entry in a Form, since tables
don't have any usable events. Your table should have a ProjectNumber
field, I'd suggest of Text datatype, and a sequential Long Integer
field I'll call SeqNo. You'll want to have a separate table of
projects, and probably a combo box on the form bound to the
ProjectNumber field (I'll call the combo box cboProjectNumber).

In the AfterUpdate event of the ProjectNumber field put code like:

Private Sub cboProjectNumber_AfterUpdate()
If IsNull(Me!SeqNo) Then ' don't overwrite existing SeqNo
Me!SeqNo = NZ(DMax("[SeqNo]", "[Your-table-name]", _
"[ProjectNumber] = '" & Me!cboProjectNumber & "'")) + 1
End If
End Sub

This will find the largest existing SeqNo for the selected project
number; if there is no SeqNo for that project at all, the NZ()
function will give you a zero. Adding 1 to that value will give the
next number in the sequence.

You can have textbox on a form or report with a control source

=[ProjectNumber] & Format([SeqNo], "-00000")

to display the concatenated identifier. It should not be stored in
your table in concatenated form though!

John W. Vinson[MVP]

This makes a bit more sense, however do i really want a sequential long
integer field?? would that defeat the purpose of this formula?? it would
seem that if:

project manager A entered 8804 for his project number, he would get 8804-00001

project manager B entered 8904 for his project number, he would get
8904-00002 where i would want hime to get 8904-00001

also would the combo box be bound to the project number field in the table
of projects or to the project number field in my original table...

thanks in advance for the help, i really appreciate it, and i feel like an
idiot at this point... if you would like to respond off forum please respond
to

bolsen at oregoniron dot com

 

[John Vinson]

This makes a bit more sense, however do i really want a sequential long
integer field?? would that defeat the purpose of this formula?? it would
seem that if:

project manager A entered 8804 for his project number, he would get 8804-00001

project manager B entered 8904 for his project number, he would get
8904-00002 where i would want hime to get 8904-00001

That's why the DMax() is called from the Project combo's afterupdate
event: it's looking for the specific entered project in its third
(criteria) argument, not at the whole table.

also would the combo box be bound to the project number field in the table
of projects or to the project number field in my original table...

The table of projects.

thanks in advance for the help, i really appreciate it, and i feel like an
idiot at this point... if you would like to respond off forum please respond
to

bolsen at oregoniron dot com

Private offline support is generally reserved for paying customers;
posting the answer in public makes it available to other folks who
might have the same problem. Good luck!

John W. Vinson[MVP]