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?