Populate Form & Associated Table with Values from another Table (4 Viewers)

[DJL910]

I would appreciate someone's help in what is likely a very straight forward task. I tried searching the forums but haven't been able to figure out the correct way to achieve it, although I suspect it may be related to setting the recordset. I'm dangerously familiar with VBA/SQL but haven't used it in a number of years and have forgotten various syntaxes, etc.

Basically ... my goal is to create a new Sales Order record for a selected customer account and pre-populate some of these "Sales Order" fields with both fixed data and associated master data from that customer account. In this way, the user can modify the value so it can specific for the given Sales Order without affecting the master data. I've have it all working, except being able to add the associated master data.

Tables & relevant fields
Tbl_CBillTo > CustNo (text CST-####), BillTo_xPTerms, BillTo_yRep
Tbl_SOHeader > SalesOrderID (text SO-######), SO_CustNoREF, SO_PTerms, SO_CALRep

Forms
Select-Customer: includes "Button_CreateNewCustSO" button
Frm_SalesOrder: record source is Tbl_SOHeader

Goal: on creating the record, set SO_PTerms = BillTo_xPTerms and SO_CALRep = BillTo_yRep

Process:
> I created the "Select-Customer" form with a combo box showing CustNo and Customer Name along with a button "Button_CreateNewCustSO". The ComboBox is bound to Tbl_CBillTo.CustNo
> I have created an OnClick event for the button to open the form "Frm_SalesOrder" in Data Entry / Add View
> I then calculate the next available Sales Order number (NextSOID) (code note shown) and assign the new record with that number (primary key)
> I pre-populate two additional fields (required by the Tbl_SOHeader table) with default text values that match dummy records (the user later updates these in the form)
> I then want to pre-populate the SO_PTerms and SO_CALRep fields with the master data but cannot figure out how to do it (orange below)

Code:
Private Sub Button_CreateNewCustSO_Click()

Dim CustNoRefField As String
CustNoRefField = CustomerSelectComboBox

DoCmd.OpenForm "Frm_SalesOrder", , , , acFormAdd

'Code not shown to create next Sales Order Number as NextSOID

'Add key fields to the SO_Header record
Forms!Frm_SalesOrder.SalesOrderID = NextSOID
Forms!Frm_SalesOrder.SO_CustNoREF = CustNoRefField

'Pre-populate Carrier and Shipper IDs (so the record can save)
Forms!Frm_SalesOrder.SO_CCarrierIDREF = "CST-1100-C01"
Forms!Frm_SalesOrder.SO_CShipToIDREF = "CST-1100-S01"

'Pre-populate remaining "header" information that the user can modify to be SO specific
Forms!Frm_SalesOrder.SO_PTerms = BillTo_xPTerms '
Forms!Frm_SalesOrder.SO_CALRep = BillTo_yRep

End Sub

 

[GaP42]

set SO_PTerms = BillTo_xPTerms and SO_CALRep = BillTo_yRep

'Pre-populate remaining "header" information that the user can modify to be SO specific
Forms!Frm_SalesOrder.SO_PTerms = BillTo_xPTerms '
Forms!Frm_SalesOrder.SO_CALRep = BillTo_yRep

Yet you do not define how you obtain Billto_xPTerms or BillTo_yRep. As this is referenced as Master Data are they PK/FK dependent on the Customer Number Reference Field? Needs a Dlookup or SQL to get the value(s) you need I think.