Generate Sequential numbers and letters combined

[jtidwell]

All my relationships in my database are one to many.

The 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 P100039 was the last work order number
then P100040 should appear.

This is my layout.

tblcompanies
PK - CompanyID
- tblcontractlist
PK - Contract Numbers (this is where my combo box is located)
- 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


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

 

[SteveM]

It would be better to have this as a numeric field. You can then prefix the
number on forms/queries/reports with the letter 'P'. All you then have to do
is create the next number using a DMax() domain aggregate function.

lngNewOrderNum = DMax("lngOrderNum","tblWorkOrders") + 1
Then prefix it with 'P' - OrderNumField = "P" & lngNewOrderNum

If that is not an option, you could to create it from the primary key ID
(AutoNumber?). The problem with this approach is that you can get gaps in
your numbers if records are created and subsequently deleted or aborted.

Another method you could use is:
txtOrderNum = DMax("txtOrderNum","tblWorkOrders")

OrderNumField = "P" & Right(txtOrderNum,Len(txtOrderNum)-1)+1

You could use the OnDirty event to place the new number in the appropriate
field on your form.

Steve

 

[jtidwell]

Would I have to do this for every contract number, they all end in a letter.
I have 8 different contract numbers
A0009041E - work order number would be E10001, E10002, etc.
A0009041F - work order number would be F10001, F10002, etc.
and so on.....

It would be better to have this as a numeric field. You can then prefix the
number on forms/queries/reports with the letter 'P'. All you then have to do
is create the next number using a DMax() domain aggregate function.

lngNewOrderNum = DMax("lngOrderNum","tblWorkOrders") + 1
Then prefix it with 'P' - OrderNumField = "P" & lngNewOrderNum

If that is not an option, you could to create it from the primary key ID
(AutoNumber?). The problem with this approach is that you can get gaps in
your numbers if records are created and subsequently deleted or aborted.

Another method you could use is:
txtOrderNum = DMax("txtOrderNum","tblWorkOrders")

OrderNumField = "P" & Right(txtOrderNum,Len(txtOrderNum)-1)+1

You could use the OnDirty event to place the new number in the appropriate
field on your form.

Steve

All my relationships in my database are one to many.

[quoted text clipped - 31 lines]

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

 

[BruceM]

Are Contracts and WorkOrders in related tables? If so, what is the linking
field (number, text, etc.)?

Would I have to do this for every contract number, they all end in a
letter.
I have 8 different contract numbers
A0009041E - work order number would be E10001, E10002, etc.
A0009041F - work order number would be F10001, F10002, etc.
and so on.....

It would be better to have this as a numeric field. You can then prefix
the
number on forms/queries/reports with the letter 'P'. All you then have to
do
is create the next number using a DMax() domain aggregate function.

lngNewOrderNum = DMax("lngOrderNum","tblWorkOrders") + 1
Then prefix it with 'P' - OrderNumField = "P" & lngNewOrderNum

If that is not an option, you could to create it from the primary key ID
(AutoNumber?). The problem with this approach is that you can get gaps in
your numbers if records are created and subsequently deleted or aborted.

Another method you could use is:
txtOrderNum = DMax("txtOrderNum","tblWorkOrders")

OrderNumField = "P" & Right(txtOrderNum,Len(txtOrderNum)-1)+1

You could use the OnDirty event to place the new number in the appropriate
field on your form.

Steve

All my relationships in my database are one to many.

[quoted text clipped - 31 lines]

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

 

[jtidwell]

Right now I really do not know how to set the "work order numbers" up, I
have tried so many ways.

right now I have contract number (text) and workordernum (num) in a table
together.

I would appreciate any suggestions.

Thank you for your time

Are Contracts and WorkOrders in related tables? If so, what is the
linking field (number, text, etc.)?

Would I have to do this for every contract number, they all end in a
letter.
I have 8 different contract numbers
A0009041E - work order number would be E10001, E10002, etc.
A0009041F - work order number would be F10001, F10002, etc.
and so on.....

It would be better to have this as a numeric field. You can then prefix
the
number on forms/queries/reports with the letter 'P'. All you then have to
do
is create the next number using a DMax() domain aggregate function.

lngNewOrderNum = DMax("lngOrderNum","tblWorkOrders") + 1
Then prefix it with 'P' - OrderNumField = "P" & lngNewOrderNum

If that is not an option, you could to create it from the primary key ID
(AutoNumber?). The problem with this approach is that you can get gaps in
your numbers if records are created and subsequently deleted or aborted.

Another method you could use is:
txtOrderNum = DMax("txtOrderNum","tblWorkOrders")

OrderNumField = "P" & Right(txtOrderNum,Len(txtOrderNum)-1)+1

You could use the OnDirty event to place the new number in the
appropriate
field on your form.

Steve

All my relationships in my database are one to many.

[quoted text clipped - 31 lines]
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

 

[SteveM]

If I understand correctly, you have contracts that may have multiple work
orders?

If that is the case, you should have a table for your contract data
(tblContracts) and a table for your work order data (tblWorkOrders).
tblWorkOrders should have a foreign key to the Primary key (AutoNumber) of
your tblContracts table.

The link between the records should not be on work order numbers or anything
that is user defined or created in code, it should be totally transparent to
the user using Primary and Foreign keys. If you use Form/Subform
configuration in your interface design, the appropriate values are added
automatically for you provided the Master/Child link fields are setup
correctly.

The work order number would simply be the ID that users see but it should
also have a unique index in the tblWorkOrders table and be a required field.

So to summarise:
tblContracts would have an Autonumber Primary key field named lngContractID
tblWorkOrders would have a Long Integer Foreign key field name lngContractID
These fields will form the relationship between your two sets of data.

To have text ID's like you described for each item, you need to have another
field in each table, let's say txtContractID in tblContracts and
txtWorkOrderID in tblWorkOrders. These will be the fields that users see on
the forms.

Steve

Right now I really do not know how to set the "work order numbers" up, I
have tried so many ways.

right now I have contract number (text) and workordernum (num) in a table
together.

I would appreciate any suggestions.

Thank you for your time

Are Contracts and WorkOrders in related tables? If so, what is the
linking field (number, text, etc.)?

Would I have to do this for every contract number, they all end in a
letter.
I have 8 different contract numbers
A0009041E - work order number would be E10001, E10002, etc.
A0009041F - work order number would be F10001, F10002, etc.
and so on.....


SteveM wrote:
It would be better to have this as a numeric field. You can then prefix
the
number on forms/queries/reports with the letter 'P'. All you then have to
do
is create the next number using a DMax() domain aggregate function.

lngNewOrderNum = DMax("lngOrderNum","tblWorkOrders") + 1
Then prefix it with 'P' - OrderNumField = "P" & lngNewOrderNum

If that is not an option, you could to create it from the primary key ID
(AutoNumber?). The problem with this approach is that you can get gaps in
your numbers if records are created and subsequently deleted or aborted.

Another method you could use is:
txtOrderNum = DMax("txtOrderNum","tblWorkOrders")

OrderNumField = "P" & Right(txtOrderNum,Len(txtOrderNum)-1)+1

You could use the OnDirty event to place the new number in the
appropriate
field on your form.

Steve

All my relationships in my database are one to many.

[quoted text clipped - 31 lines]
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

 

[Bob Quintal]

The link between the records should not be on work order
numbers or anything that is user defined or created in code,
it should be totally transparent to the user using Primary and
Foreign keys. If you use Form/Subform configuration in your
interface design, the appropriate values are added
automatically for you provided the Master/Child link fields
are setup correctly.

Steve

Where do you come up with this nonsense. There is absolutely no
need to use an artificial key instead of a valid natural key and
the artificial key makes keeping duplicate project numbers out
of the table much more convoluted. the natural primary key for
the Projects table is the Project number (no two projects will
ever have the same number), and the natural PK in the work
orders table is the Work order number, either stored as text
with the project identifier prefix or as a text field
concatenated with a numeric field to give the correct Work
order. each of the storage methods offers advantages and
disadvantages, but either is a better solution than an
autonumber.

As to the method of obtain

 

[Bob Quintal]

Right now I really do not know how to set the "work order
numbers" up, I have tried so many ways.

right now I have contract number (text) and workordernum (num)
in a table together.

I would appreciate any suggestions.

Thank you for your time

Create the WorkOrders table with at least the following fields:
ProjectNumber ( this is the link to your projects table), A
ProjectDesignator field and a WorkOrderNumber field. You'll need
a function in Visual Basic to extract the project designator
letter from the projectID and then query the work order number
field for the maximum WorkOrderNumber WHERE [ProjectDesignator]
= is the one for the work order's project.

From what you've told so far, the rightmost letter in the
ProjectNumber is your designator, you get that with the
strPD = right(ProjectNumber,1) function.

You then pass the project designator to
Nz(DMax("WorkOrderNumber","WorkOrders",
PD = """& right(ProjectNumber,1) & """),0)+1
which will return the numeric portion of the Work Order Number.

On your forms and reports, you can put =PD & Format
(workOrderNumber,"00000") in a textbox to show the number you
want people to actually see.

Or if you want to start at F10001 instead of F00001 change the
last part of DMax to ,10000) + 1

Q

Are Contracts and WorkOrders in related tables? If so, what
is the linking field (number, text, etc.)?

Would I have to do this for every contract number, they all
end in a letter.
I have 8 different contract numbers
A0009041E - work order number would be E10001, E10002, etc.
A0009041F - work order number would be F10001, F10002, etc.
and so on.....


SteveM wrote:
It would be better to have this as a numeric field. You can
then prefix the
number on forms/queries/reports with the letter 'P'. All you
then have to do
is create the next number using a DMax() domain aggregate
function.

lngNewOrderNum = DMax("lngOrderNum","tblWorkOrders") + 1
Then prefix it with 'P' - OrderNumField = "P" &
lngNewOrderNum

If that is not an option, you could to create it from the
primary key ID (AutoNumber?). The problem with this approach
is that you can get gaps in your numbers if records are
created and subsequently deleted or aborted.

Another method you could use is:
txtOrderNum = DMax("txtOrderNum","tblWorkOrders")

OrderNumField = "P" &
Right(txtOrderNum,Len(txtOrderNum)-1)+1

You could use the OnDirty event to place the new number in
the appropriate
field on your form.

Steve

All my relationships in my database are one to many.

[quoted text clipped - 31 lines]
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

 

[jtidwell]

I know very very little about VB and codes, where do i insert the below
codes.

Right now I really do not know how to set the "work order
numbers" up, I have tried so many ways.

right now I have contract number (text) and workordernum (num)
in a table together.

I would appreciate any suggestions.

Thank you for your time

Create the WorkOrders table with at least the following fields:
ProjectNumber ( this is the link to your projects table), A
ProjectDesignator field and a WorkOrderNumber field. You'll need
a function in Visual Basic to extract the project designator
letter from the projectID and then query the work order number
field for the maximum WorkOrderNumber WHERE [ProjectDesignator]
= is the one for the work order's project.

From what you've told so far, the rightmost letter in the
ProjectNumber is your designator, you get that with the
strPD = right(ProjectNumber,1) function.

You then pass the project designator to
Nz(DMax("WorkOrderNumber","WorkOrders",
PD = """& right(ProjectNumber,1) & """),0)+1
which will return the numeric portion of the Work Order Number.

On your forms and reports, you can put =PD & Format
(workOrderNumber,"00000") in a textbox to show the number you
want people to actually see.

Or if you want to start at F10001 instead of F00001 change the
last part of DMax to ,10000) + 1

Q

Are Contracts and WorkOrders in related tables? If so, what
is the linking field (number, text, etc.)?

Would I have to do this for every contract number, they all
end in a letter.
I have 8 different contract numbers
A0009041E - work order number would be E10001, E10002, etc.
A0009041F - work order number would be F10001, F10002, etc.
and so on.....


SteveM wrote:
It would be better to have this as a numeric field. You can
then prefix the
number on forms/queries/reports with the letter 'P'. All you
then have to do
is create the next number using a DMax() domain aggregate
function.

lngNewOrderNum = DMax("lngOrderNum","tblWorkOrders") + 1
Then prefix it with 'P' - OrderNumField = "P" &
lngNewOrderNum

If that is not an option, you could to create it from the
primary key ID (AutoNumber?). The problem with this approach
is that you can get gaps in your numbers if records are
created and subsequently deleted or aborted.

Another method you could use is:
txtOrderNum = DMax("txtOrderNum","tblWorkOrders")

OrderNumField = "P" &
Right(txtOrderNum,Len(txtOrderNum)-1)+1

You could use the OnDirty event to place the new number in
the appropriate
field on your form.

Steve

All my relationships in my database are one to many.

[quoted text clipped - 31 lines]
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