Solved Populate Form & Associated Table with Values from another Table (1 Viewer)

DJL910

New member
Local time
Today, 03:58
Joined
Sep 19, 2024
Messages
20
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
 
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.
 
Sorry for not clarifying... yes, these two fields are PF/PK dependent.
> Tbl_CBillTo.CUSTNO (PK) links to Tbl_SOHeader.SO_CUSTNOREF (FK)
> Tbl_SOHeader.SalesOrderID (PK)


To further clarify my scenario, I'm basically opening up the form to a new record, assigning the PK (SalesOrderID) and then assigning the other three FK for the table (SO_CustNoREF, SO_CCarrierIDREF, SO_ShipToIDREF).

Seems your suggestion is to use the value of the combobox (i.e. CustNoREFfield) to lookup in the corresponding record in the master table Tbl_CUSTNO and grab the field value I want. I'm just not sure what that code would be but I'll check into the DLookup further.

I guess I could also save the record, and then drive the lookup off the SO_CustNoREF field.
 
change the combobox and make it 3 column to include BillTo_xPTerms and BillTo_yRep field.
if you don't want to show these 2 columns on the combo, set its column width to 0, example:
on design view of the combo:

Column Count: 3
Column Widths: 1;0;0

now, you can easily populate SO_PTerms and SO_CALRep:

SO_PTerms = CustomerSelectComboBox.Column(1)
SO_CALRep = CustomerSelectComboBox.Column(2)
 
Ahh, very straight forward. It was a three column display, so added these as 4th and 5th with width 0. Just implemented, and it works great. THANK YOU!!
 
You avoid much confusion when you give the PK and FK identical names. If you insist on a difference then add "_FK" as the suffix to the FK field. The fact that they are the same actual value should tell you that it is not wrong for the fields to be the same name. Also, you would never include both values in the same query. In a query where you join both tables, only the FK field is ever selected so there is no "duplicate" name in the query.
 

Users who are viewing this thread

Back
Top Bottom