How do I add a SQL select statement column data element to a textbox on a form?

I accepted maintenance responsibility for this MS-Access2003 application about 10 years ago. It was supposedly originally written by a whiz-kid math genius. It has some very exotic code in it. I haven't worked on it for about 4 years and it is the only MS-Access application that I have going. So in 4 years I have forgotten a lot of what I used to know about this application.

The change requested by the owner of the business that uses this MS-Access2003 application is to add the FedEx Ship Date to a screen form upon which I have already added the FedEx Tracking Number (4 years ago) for him. The two data elements come from a FedEx provided application that integrates the FedEx software with the local business's order tracking software (the customer's MS-Access2003 application). I have the ShipDate from FedEx being saved to a staging table in the MS-Access2003 application along with the TrackingNo.

The FedEx Ship Manager software copies the TrackingNo and ShipDate when the bar coded order (I added the bar coding 10 years ago) is scanned to create the shipping label. An additional fact that may be worth knowing is that this MS-Access2003 application was created by cloning a wholesale tracking application onto a working retail sales order tracking application to make a single sales order tracking application for both retail and wholesale. The MS-Access2003 application also interfaces to an industrial robot router that cuts parts for the product. So this software also participates in the manufacturing of the product. The whole thing is pretty damn convoluted. I am trying to make my changes without making this whole program more convoluted than it already is.

The form to which I am trying to add the ShipDate data element:


The reason that the string “Ship Date goes here” appears in every row of column ‘Ship Date’ is because when I open the form a dialog box appears asking me to enter a value for parameter ShipDate which then is used for every row instead of the value from the data source for the whole form [Orders List].

dialog box appears asking me to enter a value for parameter ShipDate:
 
Which appears because of what I entered in the MS-Access2003 design form for defining what the form [Orders List] should look like

Text Box Shipdate:

I get the same result with or without the “=” equal sign in Control Source

Expression Builder to define exactly from where in the form query data source result set ShipDate is to be taken from:

Property Sheet for data source query for the whole form [Orders List]:

Query from expression builder for Record Source for Orders List above:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT OrderDate,Time,RequiredDate,LastName,FirstName,OrderMode,Quantity, ProductID, Description, Pastels, ApplySpecialColors,Instructions, OrderID,PackageID,ShipLastName, ShipFirstName,Router,TrackingNo,ShipDate,ShipperID,PurchaseOrderNo FROM sales WHERE OrderDate>=[forms]![Report Date Range]![Beginning Order Date] And OrderDate<=[forms]![Report Date Range]![Ending Order Date] ORDER BY [TrackingNo]ASC , [OrderID] ASC , [Time] ASC
UNION SELECT OrderDate,Time,RequiredDate,CompanyName,Contact,OrderMode,Quantity, ProductID,Description,Pastels,ApplySpecialColors,Instructions,OrderID,PackageID,ShipLastName, ShipFirstName,Router,TrackingNo,ShipDate,ShipperID,PurchaseOrderNo FROM wholesale WHERE OrderDate>=[forms]![Report Date Range]![Beginning Order Date] And OrderDate<=[forms]![Report Date Range]![Ending Order Date] ORDER BY [TrackingNo], [OrderID], [Time];
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Table names ‘sales’ and ‘wholesale’ in the FROM clause for each SELECT statement in the UNION are not included in the list of tables in the MS-Access2003 Designer Tables list. That seems really strange to me. Something is missing for the data element ShipDate because all the other data elements for the columns specified in the SELECT statements Including ‘TrackingNo’ are appearing in the application.

List of tables in the MS-Access2003 application:

See! All the table names are listed in alphabetical order in the list of application table names above. Table names ‘sales’ and ‘wholesale’, specified in the SQL statement above, are not in the list. Why I don’t know; but the application is working as expected for all the other column name data elements including TrackingNo. Which I added 4 years ago. What did I know 4 years ago that I don’t know now needed to add ShipDate to the [Order List] form?

One more thing. I reviewed all the questions that I have asked on Experts-Exchange since I established my account here, all 112 of them, and I couldn't find a question that would explain what is happening now as described in this question.

Ted Palmer