Dlookup to pull values from Form to use in Criteria portion

Question

I have a single Form and two Tables: Tbl_CustomerShipToLocation, Tbl_MasterCustomerList, and Frm_NewShipToLocation.

Tbl_CustomerShipToLocation

• Customer_ID - Number - PK
• Customer_Name - Short Text - PK - (lookup value from Tbl_MasterCustomerList; Customer_Name)
• Ship_To_Location - Short Text - PK

This is a triple PK because of complex customer information. We have multiple companies that require different processes for the same part based on which location they are ordering for.

Tbl_MasterCustomerList

• Customer_ID - Number - PK
• Customer_Name - Short Text
• Contact Person / CP Email / CP Phone Number (These are not being used in my current Form struggles.)

Creation form for new Ship To Locations:

Simply saving the record always left Customer_Name blank because it is a lookup field linked to Customer_ID in Tbl_MasterCustomerList, but displaying the corresponding Customer_Name.
Here is my code for the Save Form button:

Private Sub Save_Form_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim CustID As Long
    Dim CustName As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Tbl_CustomerShipToLocation")
    'Open Tbl_CustomerShipToLocation as recordset
    
    CustID = Me.cmbo_Customer_Name.Value
    
    CustName = DLookup("[Customer_Name]", "Tbl_MasterCustomerList", "[Customer_ID] =" & CustID)
    
    With rs
        .AddNew 'Add new record with values from Frm_NewShipToLocation
            ![Customer_Name] = CustName
            ![Customer_ID] = Me.txt_Customer_ID.Value
            ![Ship_To_Location] = Me.txt_Ship_To_Location.Value
        .Update
    End With
    
    rs.Close
    
    Set rs = Nothing
    Set db = Nothing
        
End Sub

What VBA is pulling:

I understand why the value of by Customer_Name Combo is pulling in Numbers.

I don't understand how or why it always pulls an incorrect ID. If I use Customer_ID = 2072 for example VBA pulls in 70.

I don't have entries with Customer_ID = 126 or Customer_ID = 70.

Answer 1

you didn't provide a proper minimal reproducible example so I can't be sure but it looks like you set the default column of your (cmb)CustomerName combobox to the wrong column. Since you are loosing your mind here is a full answer:

In more depth your tables are not properly normalized and you will want ManytoMany relationships. What if the contact person is fired or the customer simply has more than one contact? Further, it is better to view and edit the data in forms and reports because entering data in all the right tables rapidly becomes very error prone and very time consuming. At that point you look at the tables to see what is actually there and look-ups obscure that so delete all table look ups and use the default forms to rapidly generate starter versions of any forms and reports you need.

here is an example normalized table structure:

Next lets take advantage of the default forms for rapid form generation. In the sidebar, select CustomersShipToLocations which is a junction table that creates the Customers ShipToLOcations ManytoMany relationship. while the table is selected use the ribbon to create a form for the table (I used the form wizard to get a tabular starting structure:

Improve the form: for instance (right-click for context menu) change all id textboxes to comboboxes and then set those comboboxes to display readable values instead of the ids (we use lookups in the forms). for example, for the Purple CustomerID in the detail section(go to form design mode) and adjust the following properties:

in the format tab: 
note the column count is 1 change to 2 

set the column widths to 0,1 
(hiding first column which is column 1 and will be set as the bound column)

in the data tab: 
note the bound column is 1 by default 

Set the record source to the Customers table where column 1 is the id
 the control source is CustomerID  which is that column 1
having a control source makes this a bound column box

in the other tab: I set name to cmbCustomerID
to distinguish the form control from the table field

compare to the yellow CustomerName Combo box where we want an unbound control which means the controls value is not bound to any data

still a combobox so we will need 2 columns the ConsumerID and the readable Consumer Name 

under format 
set column count to 2 
set column widths to 0,1

under the data tab 
control source should be empty 

we add a row source where we will make sure the first two columns are our id column and a descriptive column to match our format.  Incidentally that is how I set up the first columns of the Customers Table so everything defaults in the right place. 

no controlsource so this combobox is unbound 
unbound means we can change the combobox value without change a value in the tables

if you want a textbox showing the selected id do something like

add a textbox like txtShowCustomerID and set it's control source to =[cmbSelectCustomer]

In this example, to show what is happening in the tables, I left the detail section of the form and its form record source bound controls so there is no need of a button to insert records into the form's record source, but this leaves many problems similar to editing the tables directly unless we do more form improvements. Often a button is a good way to add records: MS Access 2016 - Multi column listbox to add values to multiple fields in a table

Private Sub cmdAddCustomerShipToLocation_Click()
  Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("CustomersShipToLocations")
    With rs
        .AddNew
            rs!CustomerID = Me.cmbSelectCustomer
            rs!ShipToLocationID = Me.cmbSelectShipToLocation
        .Update
    End With
    rs.Close
    'The selected CustomerID is show with an unshown bound textbox(labels dont' change) 
   
   
    Set rs = Nothing
    Set db = Nothing
  Me.Requery 'show changes
End Sub