Generate Sequential numbers for new records

[jtidwell]

I am developing a Work Order Database for my job. I have a combo box with
"Contract Numbers" to select from. When you select on any Contract Number I
need a new "Work Order Number" to appear. There are 10 different contract
numbers (A0009041N, A0009041P, etc...) so I need 10 different work order
numbers (N10001, P100001, etc..) to generate sequential for each new record.

This is my layout.

tblcompanies
PK - CompanyID
tblcontractlist
PK - Contract NUmbers (this is where I have my combo box)
- Company ID
tblMaintWO
PK - MaintWorkorderID
- Contract Numbers

Right now I have one form (FrmMaintWO).

I want to select a contract number from the combo box. This should
automatically bring up a new work order number for that specific contract
number

example:
A0009041E - (Work Order Num) E100001, E100002, E100003
A0009041G - G100001, G100002

Also, I would like to see the Work Order Numbers stored in a Combo So I will
see the Work Orders that have already been entered

 

[John W. Vinson]

I am developing a Work Order Database for my job. I have a combo box with
"Contract Numbers" to select from. When you select on any Contract Number I
need a new "Work Order Number" to appear. There are 10 different contract
numbers (A0009041N, A0009041P, etc...) so I need 10 different work order
numbers (N10001, P100001, etc..) to generate sequential for each new record.

This is my layout.

tblcompanies
PK - CompanyID
tblcontractlist
PK - Contract NUmbers (this is where I have my combo box)
- Company ID
tblMaintWO
PK - MaintWorkorderID
- Contract Numbers

Right now I have one form (FrmMaintWO).

I want to select a contract number from the combo box. This should
automatically bring up a new work order number for that specific contract
number

example:
A0009041E - (Work Order Num) E100001, E100002, E100003
A0009041G - G100001, G100002

Also, I would like to see the Work Order Numbers stored in a Combo So I will
see the Work Orders that have already been entered

Whoa.

You're making some assumptions here. Do you have ten *FIELDS* for contract
numbers? or is your database set up so you can only add ten records to the
contract table (you must not want your business to expand much!)?

And you apparently have a combo box in your tblcontractlist *table*. Do you
(it's probably not a good idea), or are you just using tblContractList as the
row source for a combo box on a Form (that's fine)?

Also, you present a lot of different alphabetical prefixes with no (obvious)
rationale for how they're generated. If your contract number is an
"intelligent key" where the last letter has an independent existance and is
inherited by the work order number, you're on the wrong track: a field should
be "atomic", containing only ONE piece of information.

I think you really should step back and do some proper normalization. If a
Work Order can have many Contracts, you need *a one to many relationship*
between the two tables. Can a Contract be involved with multiple Workorders?
or just one?

John W. Vinson [MVP]

 

[jtidwell]

All my relationships in my database are one to many.



I have a table set up with all my companies which I can add or delete. Now,
that table is related to my tblcontractlist table. This table consists of 9
fields, one of them being my "contract number" field and it has a drop down
box. The other fields are prices. I have it set up in my form when I click
on "contract number" the company name and all the related prices and
calculations come up for that specific "contract number". The only thing I
can not figure out is how to generate a sequential work order number to
relate to EACH different contract number.



For Example if I click on "contract numbers" and I choose A0009041P (which
all contract numbers have the same format but different last letters) then a
work order number should appear, if P00039 was the last work order number
then P00040 should appear.

 

[John W. Vinson]

I have a table set up with all my companies which I can add or delete. Now,
that table is related to my tblcontractlist table. This table consists of 9
fields, one of them being my "contract number" field and it has a drop down
box. The other fields are prices. I have it set up in my form when I click
on "contract number" the company name and all the related prices and
calculations come up for that specific "contract number". The only thing I
can not figure out is how to generate a sequential work order number to
relate to EACH different contract number.



For Example if I click on "contract numbers" and I choose A0009041P (which
all contract numbers have the same format but different last letters) then a
work order number should appear, if P00039 was the last work order number
then P00040 should appear.

Ok... I *REALLY* feel that these fields are incorrectly normalized, not
atomic, and faulty. The P in this example has an independent meaning from the
rest of the field; ideally it should be in a field of its own, or should not
be included at all!

But; if you're stuck with (or determined to use) this - you can put code in
the BeforeInsert event of the subform in which you enter the WO numbers. I'll
assume that the contract number has already been selected and is the
Master/Child Link Field. The code would be something like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim varWO As Variant
Dim iNext As Integer
'find largest existing WO for this contract
varWO = DMax("WO", "[tablename]", "[Contract Number] = '" _
& Me!txtContractNumber & "'")
If IsNull(varWO) Then
Me!txtWO = Right(Me!txtContractNumber, 1) & "00000"
Else
Me!txtWO = Right(Me!txtContractNumber, 1) & _
Format(Val(Mid(varWO, 2)) + 1), "00000")
End If
End Sub

Some error checking, validation of values, etc. would be prudent.

John W. Vinson [MVP]

 

[jtidwell]

I do not have it in a subform. I want to use the "work order number" like
an autonumber. I can not change the contract numbers. I have one form, the
way I have it set up when I choose a contract number from the drop down box
all the
information from that contract comes up. Now, I need to figure out a way to
have an autonumber for each contract number. That autonumber needs to begin
with the last letter of that contract number. I am new to access and know
very little about codes. I hope I explained this well enough.

I really do appreciate all your help.

I have a table set up with all my companies which I can add or delete.
Now,
that table is related to my tblcontractlist table. This table consists of
9
fields, one of them being my "contract number" field and it has a drop
down
box. The other fields are prices. I have it set up in my form when I
click
on "contract number" the company name and all the related prices and
calculations come up for that specific "contract number". The only thing
I
can not figure out is how to generate a sequential work order number to
relate to EACH different contract number.



For Example if I click on "contract numbers" and I choose A0009041P (which
all contract numbers have the same format but different last letters) then
a
work order number should appear, if P00039 was the last work order number
then P00040 should appear.

Ok... I *REALLY* feel that these fields are incorrectly normalized, not
atomic, and faulty. The P in this example has an independent meaning from
the
rest of the field; ideally it should be in a field of its own, or should
not
be included at all!

But; if you're stuck with (or determined to use) this - you can put code
in
the BeforeInsert event of the subform in which you enter the WO numbers.
I'll
assume that the contract number has already been selected and is the
Master/Child Link Field. The code would be something like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim varWO As Variant
Dim iNext As Integer
'find largest existing WO for this contract
varWO = DMax("WO", "[tablename]", "[Contract Number] = '" _
& Me!txtContractNumber & "'")
If IsNull(varWO) Then
Me!txtWO = Right(Me!txtContractNumber, 1) & "00000"
Else
Me!txtWO = Right(Me!txtContractNumber, 1) & _
Format(Val(Mid(varWO, 2)) + 1), "00000")
End If
End Sub

Some error checking, validation of values, etc. would be prudent.

John W. Vinson [MVP]

 

[John W. Vinson]

I do not have it in a subform. I want to use the "work order number" like
an autonumber. I can not change the contract numbers. I have one form, the
way I have it set up when I choose a contract number from the drop down box
all the
information from that contract comes up. Now, I need to figure out a way to
have an autonumber for each contract number. That autonumber needs to begin
with the last letter of that contract number. I am new to access and know
very little about codes. I hope I explained this well enough.

Sorry... misunderstood. The same code will work in the AfterUpdate event of
the combo box selecting the contract number. You'll need to adapt it to your
table and fieldnames of course.

John W. Vinson [MVP]

 

[jtidwell]

How do I do that?

Sorry... misunderstood. The same code will work in the AfterUpdate event
of
the combo box selecting the contract number. You'll need to adapt it to
your
table and fieldnames of course.

John W. Vinson [MVP]

 

[John W. Vinson]

I want to select a contract number from the combo box. This should
automatically bring up a new work order number for that specific contract
number

example:
A0009041E - (Work Order Num) E100001, E100002, E100003
A0009041G - G100001, G100002

Also, I would like to see the Work Order Numbers stored in a Combo So I will
see the Work Orders that have already been entered

At what point do you want a new work order number assigned? Surely you want
the form to be able to browse existing work orders without assigning new ones?
What is the workflow process here: when you open the form, what do you want to
happen, and what will you do next?


John W. Vinson [MVP]