Report w/Subreport Prompting for TBL 1

[crad]

First, I apologize for my lack of experience up front. That in mind...

I have a report with a subreport that is supposed to show Shipping Order information (on the report) and Items/quantities ordered (on the subreport). Each report is attached to a query and when I run the queries by themselves, the results provide the appropriate information to fill the controls on the reports.

I am passing an OrderID from a FORM to determine which record I will use from the query. If I just generate the report (without the subreport attached) it runs fine. I try to add the subreport and then generate the report and I get a message box prompting for the SMC_ShipOrder_TBL.

I have the Link Child as MainOrderID (from the SMC_ShipOrdrDet_TBL) and the Link Master as SMC_ShipOrder_TBL.OrderID.

The SQL statement for the main report query is:

SELECT SMC_ShipOrder_TBL.*, SMC_Hauler_TBL.*, SMC_Quarry_TBL.*, SMC_Customer_TBL.*, SMC_TaxStatus_TBL.*, SMC_Payment_TBL.*, SMC_ShipOrder_TBL.OrderID, SMC_Payment_TBL_1.*, SMC_TaxStatus_TBL.*
FROM (((SMC_Quarry_TBL INNER JOIN ((SMC_Hauler_TBL INNER JOIN SMC_ShipOrder_TBL ON SMC_Hauler_TBL.HaulerID = SMC_ShipOrder_TBL.HaulerID) INNER JOIN SMC_Customer_TBL ON SMC_ShipOrder_TBL.CustomerID = SMC_Customer_TBL.CustomerID) ON SMC_Quarry_TBL.QuarryID = SMC_ShipOrder_TBL.QuarryID) LEFT JOIN SMC_TaxStatus_TBL ON SMC_ShipOrder_TBL.Taxable = SMC_TaxStatus_TBL.TaxCode) LEFT JOIN SMC_Payment_TBL ON SMC_ShipOrder_TBL.PymntType = SMC_Payment_TBL.PaymentType) LEFT JOIN SMC_Payment_TBL AS SMC_Payment_TBL_1 ON SMC_ShipOrder_TBL.RRPymntType = SMC_Payment_TBL_1.PaymentType
WHERE (((SMC_ShipOrder_TBL.OrderID)=[Forms]![SMC_ShipOrder_FRM]![OrderID]));

And the statement from the subreport query is:

SELECT SMC_ShipOrdrDet_TBL.OrdrDetID, SMC_ShipOrdrDet_TBL.MainOrderID, SMC_ShipOrdrDet_TBL.ProductID, SMC_ShipOrdrDet_TBL.UnitsOrdered, SMC_ShipOrdrDet_TBL.ShippingMethodID, SMC_ShipOrdrDet_TBL.UnitPrice, SMC_Products_TBL.*
FROM SMC_ShipOrdrDet_TBL LEFT JOIN SMC_Products_TBL ON SMC_ShipOrdrDet_TBL.ProductID = SMC_Products_TBL.ProductID
WHERE (((SMC_ShipOrdrDet_TBL.MainOrderID)=[Forms]![SMC_ShipOrder_FRM]![OrderID]));


I've tried many variations based on things I've found in the forums over the last two days, two Access books, two VB books and a SQL book but I'm really stuck now. Any help would be greatly appreciated!

 

[dhookom]

I would set both of the Link Master Child properties to:
[OrderID]
You should then be able to remove the Where Clause from the subreport since the records in the subreport will be filter to the same value/s as found in the main report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[crad]

OK, I did try that but there seems to be a problem with the fact that the Report query uses SMC_ShipOrder_TBL.OrderID and the Subreport query uses SMC_ShipOrdrDet_TBL.MainOrderID.

I have tried adding a link between the SMC_ShipOrder_TBL.OrderID and SMC_ShipOrdrDet_TBL.MainOrderID in the subquery but it's still giving me the same message. It's definitely in that Link Child/Master scenario because I changed the SMC_ShipOrder_TBL.OrderID to SMC_ShipOrder_FRM.OrderID and then when I tried to run it, I was prompted for the SMC_ShipOrder_FRM.

Here's the new Subquery code:

SELECT SMC_ShipOrdrDet_TBL.OrdrDetID, SMC_ShipOrdrDet_TBL.MainOrderID, SMC_ShipOrdrDet_TBL.ProductID, SMC_ShipOrdrDet_TBL.UnitsOrdered, SMC_ShipOrdrDet_TBL.ShippingMethodID, SMC_ShipOrdrDet_TBL.UnitPrice, SMC_Products_TBL.*, SMC_ShipOrder_TBL.OrderID
FROM SMC_ShipOrder_TBL LEFT JOIN (SMC_ShipOrdrDet_TBL LEFT JOIN SMC_Products_TBL ON SMC_ShipOrdrDet_TBL.ProductID = SMC_Products_TBL.ProductID) ON SMC_ShipOrder_TBL.OrderID = SMC_ShipOrdrDet_TBL.MainOrderID;

Also, I neglected to mention earlier that I have a Form/Subform based on the same tables and they work fine. In fact all I'm really trying to do is print some of the information from the Form/Subform to be faxed. If there's an easier way to do this, I'm open to it.

 

[dhookom]

You can set the link master child properties for your subreport the same as you do for your form/subform. There should be no reason to include SMC_ShipOrder_TBL in your subreport record source.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[crad]

Duane,

Gave that a shot and I get a message box stating:

"The specified field 'OrderID' could refer to more than one table listed in the FROM clause of your SQL statement."

I've checked the Form/Subform and it has the Child link as MainOrderID and the Master as OrderID. I've tried the Report/Subreport as both the way you suggested (with only OrderID for both Child and Master) and the way it is set up in the Form/Subform and get the same message as above either way.

Another thing I left out earlier is the fact that I'm calling the report from a Macro attached to the Print Button. It has the following code:

OpenReport (With parameters as follows in RED):

Report Name: SMC_ShipOrder2_REP
View: Print Preview
Filter Name:
Where Condition: =IIf([Forms]![SMC_ShipOrder_FRM]![SMC_ShipOrder_FRM.OrderID] Is Null," ","[SMC_ShipRep_QRY]![SMC_ShipOrder_TBL.OrderID] = Forms![SMC_ShipOrder_FRM]![OrderID]&quot
Window Mode: Normal

 

[dhookom]

You are paying a penalty for:
- using "*" to select all fields from a table and
- using the same field names across tables
I bring down each field separately and don't add duplicate fields to the grid. I also create applications where every field has its own, unique name. For instance tblOrders would have every field begin with "ord" ie:
ordOrdID
ordOrderDate
ordCstID
ordShipVia
The OrdID field in tblOrderDetails would be named
odtOrdID
other fields might be:
odtPrdID 'link to product ID
odtQuantity
odtUnitPrice
This assures that I will never have an issue like you are having.
Consider removing all duplicates from your report's record source.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[crad]

Duane,

That got it. Thanks so much for the information and sorry it took so long for me to understand. Your last post did it. While I will not have time to go back and correct everything at this time, I will get to it soon and clean up the mess I've made for myself.

The short version of how I modified it is that I left the "*" entry for the SMC_ShipOrder_TBL in the grid but removed the OrderID specific reference (I guess those were the two conflicts for the Master link).

As far as using the "*" for each of the tables in the Query grid, I was just following the instructions in one of the Access books I'd been using (I'll be more careful about that in the future).

Anyway, thanks again.

 

[dhookom]

I would shy away from attempting to change your existing table and field names since this can get very messy. It is best to do this from the start with a new application.

Tony Toews has some good naming conventions at his site

http://www.granite.ab.ca/access/tablefieldnaming.htm

with links to others.

Selecting only the required fields versus "*" may be more efficient.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.