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 so I need 10 different work order numbers (N10001, O100001, etc..) to generate sequential for each new record.

[hyperpau]

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 so I need 10 different work order numbers (N10001, O100001, etc..) to generate sequential for each new record.

That is easy and I can answer this question. but you need to
give me the specifics. Where is the combobox with Contract numbers bound?

is this a Primary key for another table?

Tell me how many forms you have and what format you want
to put for you Work Order Number.

[jtidwell]

That is easy and I can answer this question. but you need to
give me the specifics. Where is the combobox with Contract numbers bound?

is this a Primary key for another table?

Tell me how many forms you have and what format you want
to put for you Work Order Number.

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.

[hyperpau]

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.

The way you gave me the information is vague but let me try that.
Which is the Work Order number, the one starting in A? or in E?
What is that number startin in G? It's so hard to understand it.

Assuming that the Work Order Num is the one startin in E,
this is how I understand it.
FrmMaintWO is bound to tblMaintWO.
tblMaintWO has the fields MaintworkorderID and Contract Numbers.
This Contract Numbers in TblMaintWO is a lookup combo box to
the Contract Numbers found in tblContractList.

Do i get this right?

Now, what you want to do is on the FrmMaintWO, you would select the
Contract Number from the combo box which looks up the Contract Numbers from the tblContractList.
What you want to happen is that, as soon as you select the contract number for the combo box, it would generate a Work Order for that specific contrace number.

If i get this right, this is how you do it.

1. On tblMaintWO, you must add 2 additional fields.
1st field is labeled WOID which is a number: long integer.
2nd field is labeld WONumber which is a text field.

2. On FrmMaintWO, drag and drop down the two new fields on
your desired location.
The WOID field should be set to Visible=No on the properties.
The FrmMaintWO should also be set to the properties:
Allow Additions = Yes
Data Entry = Yes

Add VBA coding to the AfterUpdate Event of the ContractNumber combo box.

Private Sub ContractNumber_AfterUpdate( )
 
    Me!WOID  = Nz(DMax("WOID", "tblMaintWO"), 0) + 1
    Me!WONumber = "E10" & CStr(Format("[Me!WOID]", "0000"))
 
End Sub

as soon as you select a Contract number from the combo box of
FrmMaintWO, the WONumber text box generates a sequential number
in this format: E100001
I dont know how to underline the last 4 digits.


Regarding saving the WONumber in a combobox, I don't understand your
question. Where would this combo box be placed on? Woudl this be on the
Same FrmMaintWO?

[jtidwell]

I apologize for the vague description. I am new to access so a lot of things I do not understand yet.

Yes you are correct except in the code on line 4 you are only identifying "E10" for the work order number.


All my contract numbers (located in the tblcontractlist) have the same format except the last letter changes.

When I click on contract numbers from the combo box, I can choose different contracts. A0009041A, A0009041B, A00009041C, etc.... When I choose a contract number, for example A0009041A, all the information for that specific contract comes up, company information, prices. etc.... the only thing I can not figure out is how to generate a specific "work order number" for each "contract number". The "work order number" should be the last letter of the "contract number" and "1" plus 4 zeros (A100001). So, every time I need to create a new work order for A0009041A a new "work order number" should appear for that specific contract number. Also, I would like if possible having another work order number field on my form which is a combo box to list all the previous work order numbers for that contract.



I hope I described it a little better.... Thank you so so much for trying to help.
I look forward to your response:)

[hyperpau]

I apologize for the vague description. I am new to access so a lot of things I do not understand yet.

Yes you are correct except in the code on line 4 you are only identifying "E10" for the work order number.


All my contract numbers (located in the tblcontractlist) have the same format except the last letter changes.

When I click on contract numbers from the combo box, I can choose different contracts. A0009041A, A0009041B, A00009041C, etc.... When I choose a contract number, for example A0009041A, all the information for that specific contract comes up, company information, prices. etc.... the only thing I can not figure out is how to generate a specific "work order number" for each "contract number". The "work order number" should be the last letter of the "contract number" and "1" plus 4 zeros (A100001). So, every time I need to create a new work order for A0009041A a new "work order number" should appear for that specific contract number. Also, I would like if possible having another work order number field on my form which is a combo box to list all the previous work order numbers for that contract.



I hope I described it a little better.... Thank you so so much for trying to help.
I look forward to your response:)

I am not aware that there is a way to generate sequential letters like what you want. Generating the last letter from A-Z might not be possible.
the first letter which is the last letter of the contract number means you have to truncate. There is a way but i do not know how. As far as i remember, you should use a wild card (ex: Like "%A").

The only choice you have is to have those work ordernumber manually typed.
I apologize, I am not that advanced yet.

[jtidwell]

Thank you for trying.