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:
 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:
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:
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]:
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:
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
Ted PalmerInformation Technology ConsultantAsked:
Who is Participating?
 
Nitin SontakkeConnect With a Mentor DeveloperCommented:
I am not an MS-Access (I would probably hate it if called one!) and probably know less than what you already know.

How likely is it that the Sales and Wholesale are Queries and not Tables? Because (I think) there is no way to identify the difference when used in FROM clause.
0
 
Ted PalmerInformation Technology ConsultantAuthor Commented:
Nitin, Thank you for your quick response. I thought about what you suggested while I was still struggling to discover an answer on my own. I investigated that and didn't find anything that I thought would be helpful. But now that you mentioned it, I think I will go back and take a closer look. It is 2 AM here in the state of Missouri in USA. I should be in bed already. I will look at it in the morning. Perhaps we will converse some more. That would be nice. Bye for now.

Ted
0
 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
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

It's likely then that 'wholesale' and 'sales' are other Queries rather than tables.  You should be able to see them in the list of 'Queries' under your tables.

If you test the SQL statement:
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];

Open in new window


Does ShipDate have a value in the results?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Ted PalmerInformation Technology ConsultantAuthor Commented:
Nitin & Dustin,

It appears to me that i did a very poor job of looking at the Queries when I looked at them before. I just now looked at them again, and found what probably is the solution to my question. I really need to get to bed soon. I complete this process in the morning and split the points between you two. Any comments after this one no points. Assuming that Query is the solution.

Thank you both.
0
 
Ted PalmerInformation Technology ConsultantAuthor Commented:
It is almost 3 AM here now.
0
 
[ fanpages ]IT Services ConsultantCommented:
(i have just "endorsed" your question, Ted.  That is one of the most thoughful approaches in terms of providing as much detail as possible when asking a question I have seen in years at this site!)

Furthermore, coming back to Nitin's response:

...How likely is it that the Sales and Wholesale are Queries and not Tables? Because (I think) there is no way to identify the difference when used in FROM clause.

You can tell if the source of a FROM clause is a query or a table if you JOIN to or otherwise (sub-)SELECT the [MSysObjects] table in your SQL Statement.

Once Ted's discussion has run its course, we can discuss this further, if you wish.
0
 
Ted PalmerInformation Technology ConsultantAuthor Commented:
fanpages,

Thank you for the endorsement. I haven't been using my Experts-Exchange account very much lately so it was a bit of a struggle for me to learn the newer user interface to get my images in along with the text in the right places. I know what it is like to be drowning in a sea of data while dying of thirst for information. So I can see what a help endorsements can be on improving that situation. I still feel embarrassed by my oversight. I guess I am just getting old, but I still don't ever give up. Experts-Exchange has been a very big help for me in my professional life. It is good to see the evolution that has occurred to make it always better.
1
 
Ted PalmerInformation Technology ConsultantAuthor Commented:
I am looking forward to bragging about Experts-Exchange at my next computer club meeting.
0
 
Ted PalmerInformation Technology ConsultantAuthor Commented:
Sorry for the delay guys in wrapping this Question up. I have put myself in a situation like the dog that chases cars. What are they going to do with it if they catch it? Well it looks like I am going to have to struggle for a while before I can really test this form. There is some intermediate code involved in getting the ShipDate from the staging table to where it has to be to show up in the "Orders List" form. I may be able to jam some data into that intermediate process so that I can verify that the changes I have made in the "Orders List" form are working like I had hoped for. I'm scrambling as fast as I can. Thank you for your patience.

Ted
0
 
Ted PalmerInformation Technology ConsultantAuthor Commented:
Nitin, Dustin & fanpages,

I finally got this 'Order List' form tested. I had to jam the test data into the tables that the 'Sales' and 'wholesale' queries got their data from. The original software author used some very "nontraditional" techniques for foreign key assignment. Which made it much more challenging to figure out where to put the test data so that it would show up in the 'Order List' form in a predictable place as good testing techniques would require. All tests pass.

fanpages, I don't know what you had in mind when you said: "we can discuss this further, if you wish." But I am interested. I have never participated in a group discussion on Experts-Exchange (EE). So I am not familiar with the details of implementation. I have GoToMeeting software from Citrix that has always worked well in the past. So I could set up a 4 way conversation for all of us. If everybody has a headset with a microphone, the 4 of us would all be able to talk at the same time. Maybe EE has that kind of functionality built in now considering how much new process and functionality is now available on EE.

Nitin is in India, fanpages is in the UK, and I believe Dustin and I are in the USA. So scheduling a time that would be good for all of us would be very problematic. How we going to communicate once I close this question? I trust that EE has functionality for that. Is that correct fanpages? Perhaps EE has a group chat function in writing now? I have to get this Question closed out and get to bed. It's now 2:30 AM here, and I was up very late last night.

Bye Ted
0
 
Ted PalmerInformation Technology ConsultantAuthor Commented:
THANK YOU, Ted
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.