Autopopulate Text Box in SubForm

[Tophan]

I have a main form named frmInvoices and subform named frmInvoicesDtls

In the main form, when I select the SubName from the cboSubName, the combo box cboSite refreshes to show only the job sites that particular sub-contractor is working on. For each job site, a new order number is generated for the sub-contractor. This information is saved in the table tblSubContractorJobDtls

What I would like to do is that when both the SubName and Site are selected, the subcontract order number from tblSubContractJobDtls automatically enters in the subform frmInvoicesDtls in the text box txtSubContractOrder.

Is this possible?

I also have one more question but that is for a different thread

I'm getting the information in the subform using a Dlookup formula but I don't think this is the best method.

 

[informer]

Hi Tophan,

Could you, Please, show your logical data model or better still your conceptual data model?

 

[Tophan]

To clarify the information in the forms is pulled from tblSubContractors and tblSubContractorJobDtls

tblSubContractors is just basic info - AutoID, Name, Address, Contact numbers.
tblSubContractorJobDtls is joined to tblSubContractors by AutoID. The information in this form is the JobNumber (from tblJobContracts), order number, trade (whether the sub is a mason, painter, carpenter, etc.), their rate per hour/day etc., i.e. information specific to the service they are providing on that job. This also included a subcontract order number which is similar to a purchase order. This number is created in a separate programme and manually keyed into this table or its form.

In the main form, the cboSubName looks up the sub-contractor from tblSubContractors; and cboSite looks up the JobNumber from tblSubContractJobDtls.

As not all subs work on each job, I have the cboSite filtered to only show the jobs specific to the sub contractor selected.

The database I am working on generates invoices for the sub-contractors. What I would like to do is have the sub contract order number automatically populate in the invoices form once I select the subname and jobnumber

I hope I didn't confuse things more...thanks in advance for your help

 

[Tophan]

I've attached the relationships report for your reference

 

[arnelgp]

is there only one order number of each subcon?
firstly, you will have to manually put all order number in invoice detail. using update query.

then modify your form.
click on the subform and make the order number textbox property to Locked=True.
on before insert event of the subform:

private sub form_beforeinsert(cancel as integer)
me.ordernumberControl = me.parent!ordernumberControl
end sub