Setting RecordSource from 2 Queries?

[joel009]

Windows XP SP3, Access 2010 in 2007 mode.
I need to set the record source for a summary report on open. I have the detail query and and the summary query set up as queries but I need to set them up dyanamically on form open to specify the Plant Code and Model Year. The summary query uses the detail query and I can't figure out how to do that in VBA.

Detail Query:
SELECT tblPartRouteAssignment.Route, tblPartRouteAssignment.PartNumber, tblPartRouteAssignment.Station, tblPartRouteAssignment.LineFeedDeliveryType, tblPartRouteAssignment.ReceivingDock, tblPartRouteAssignment.LocFullStorage, tblPartRouteAssignment.LocLineFeedStaging, tblPartRouteAssignment.LocEmptyStorage, tblPartRouteAssignment.LocDockReturn, tblPartInfo.Description, IIf([StationBld_Rate]=0,[VerifiedBld_Rate],[StationBld_Rate]) AS Bld_Rate, tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height, [Bld_Rate]/[PackDensity] AS COPD, tblLinefeedHandling.LineFeedHandling, tblLinefeedHandling.StagingHandling, tblLinefeedHandling.EmptyReturnHandling, tblPartRouteAssignment.StackHtReceiving, tblPartRouteAssignment.StackHtLFDelivery, (([LineFeedHandling]/[StackHtLFDelivery])+([StagingHandling]/[StackHtReceiving])+([EmptyReturnHandling]/[StackHtReceiving])) AS HdlgPerCont, tblPartRouteAssignment.TravDistLineFeed, tblPartRouteAssignment.TravDistStaging, tblPartRouteAssignment.TravDistDock, tblPartRouteAssignment.TravDistEmpty, (([TravDistLineFeed]/[StackHtLFDelivery])+([TravDistStaging]/[StackHtReceiving])+([TravDistEmpty]/[StackHtReceiving])) AS TravelPerTrip, [TravelPerTrip]*0.00227 AS TravelMinPerTrip, [HdlgPerCont]+[TravelMinPerTrip] AS AllocPerCont, [AllocPerCont]*[COPD] AS LFAllocation, tblPlantInfo.WorkingMinPerShift, tblPartRouteAssignment.PlantCode, tblPartRouteAssignment.ModelYear
FROM (((tblPartRouteAssignment INNER JOIN tblPlantInfo ON tblPartRouteAssignment.PlantCode = tblPlantInfo.PlantCode) INNER JOIN tblPartStation ON (tblPartRouteAssignment.PlantCode = tblPartStation.PlantCode) AND (tblPartRouteAssignment.PartNumber = tblPartStation.PartNumber) AND (tblPartRouteAssignment.Station = tblPartStation.Station)) INNER JOIN tblLinefeedHandling ON tblPartRouteAssignment.LineFeedDeliveryType = tblLinefeedHandling.LineFeedType) INNER JOIN tblPartInfo ON (tblPartRouteAssignment.PlantCode = tblPartInfo.PlantCode) AND (tblPartRouteAssignment.PartNumber = tblPartInfo.PartNumber)
WHERE (((tblPartRouteAssignment.PlantCode)="02452") AND ((tblPartRouteAssignment.ModelYear)="2010"));

Summary Query:
SELECT tblRouteInfo.Route, tblRouteInfo.Department, tblRouteInfo.ModelType, tblRouteInfo.JobType, tblRouteInfo.Validated, tblRouteInfo.NumberofDrivers, tblRouteInfo.AddlMinPerShift, Sum(qryRouteEfficiencyDetail.LFAllocation) AS SumOfLFAllocation
FROM tblRouteInfo INNER JOIN qryRouteEfficiencyDetail ON (tblRouteInfo.Route = qryRouteEfficiencyDetail.Route) AND (tblRouteInfo.PlantCode = qryRouteEfficiencyDetail.PlantCode)
GROUP BY tblRouteInfo.Route, tblRouteInfo.Department, tblRouteInfo.ModelType, tblRouteInfo.JobType, tblRouteInfo.Validated, tblRouteInfo.NumberofDrivers, tblRouteInfo.AddlMinPerShift;

Am I missing the obvious? Probably!

Thanks for any help,
Joel



Joel

 

[MajP]

Make an input form. Have two controls on the form (I assume combos):
frmInput:
cmboPlant
cmboYear
Have an ok button on the form to open the report and if you want you can hide the form with code like
me.visible = false
but you cannot close the form because the reports query will use it.

then in your query change to the hard code to reference the values in the control.
WHERE (((tblPartRouteAssignment.PlantCode)= Forms![frmInput].cmboPlant) AND ((tblPartRouteAssignment.ModelYear)=Forms![frmInput].cmboYear));

 

[joel009]

MajP,
Should have explained I already have that working for the detail report. Using 2 combo boxes and store the values selected in public variables that are used multiple times.

My problem is how to set the summary report with the second query as the record source from above as it calls the first query.

The Detail Query is named qryRouteEfficiencyDetail as a query but I need to be able to change the Plant code and ModelYear from what is selected in the combo boxes.

This code I use to open a report from 2 forms, results dependant on which form opens it:
Private Sub Report_Open(Cancel As Integer)


If PubblnMainFormLast = True Then
'for selection from main form
Me.RecordSource = "SELECT tblPartRouteAssignment.Route, tblPartRouteAssignment.PartNumber, tblPartRouteAssignment.Station, tblPartRouteAssignment.LineFeedDeliveryType," _
& " tblPartRouteAssignment.ReceivingDock, tblPartRouteAssignment.LocFullStorage, tblPartRouteAssignment.LocLineFeedStaging," _
& " tblPartRouteAssignment.LocEmptyStorage, tblPartRouteAssignment.LocDockReturn, tblPartInfo.Description," _
& " IIf([StationBld_Rate]=0,[VerifiedBld_Rate],[StationBld_Rate]) AS Bld_Rate, tblPartInfo.PackDensity," _
& " tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height," _
& " [Bld_Rate]/[PackDensity] AS COPD, tblLinefeedHandling.LineFeedHandling, tblLinefeedHandling.StagingHandling," _
& " tblLinefeedHandling.EmptyReturnHandling, tblPartRouteAssignment.StackHtReceiving, tblPartRouteAssignment.StackHtLFDelivery," _
& " (([LineFeedHandling]/[StackHtLFDelivery])+([StagingHandling]/[StackHtReceiving])+([EmptyReturnHandling]/[StackHtReceiving])) AS HdlgPerCont," _
& " tblPartRouteAssignment.TravDistLineFeed, tblPartRouteAssignment.TravDistStaging, tblPartRouteAssignment.TravDistDock," _
& " tblPartRouteAssignment.TravDistEmpty, (([TravDistLineFeed]/[StackHtLFDelivery])+([TravDistStaging]/[StackHtReceiving])+([TravDistEmpty]/[StackHtReceiving])) AS TravelPerTrip," _
& " [TravelPerTrip]*0.00227 AS TravelMinPerTrip, [HdlgPerCont]+[TravelMinPerTrip] AS AllocPerCont, [AllocPerCont]*[COPD] AS LFAllocation," _
& " tblPlantInfo.WorkingMinPerShift, tblPartRouteAssignment.PlantCode, tblPartRouteAssignment.ModelYear" _
& " FROM ((((tblPartRouteAssignment INNER JOIN tblPlantInfo ON tblPartRouteAssignment.PlantCode = tblPlantInfo.PlantCode)" _
& " INNER JOIN tblRouteInfo ON (tblPartRouteAssignment.PlantCode = tblRouteInfo.PlantCode)" _
& " AND (tblPartRouteAssignment.Route = tblRouteInfo.Route))" _
& " INNER JOIN tblPartStation ON (tblPartRouteAssignment.PlantCode = tblPartStation.PlantCode)" _
& " AND (tblPartRouteAssignment.PartNumber = tblPartStation.PartNumber)" _
& " AND (tblPartRouteAssignment.Station = tblPartStation.Station))" _
& " INNER JOIN tblLinefeedHandling ON tblPartRouteAssignment.LineFeedDeliveryType = tblLinefeedHandling.LineFeedType)" _
& " INNER JOIN tblPartInfo ON tblPartRouteAssignment.PartNumber = tblPartInfo.PartNumber" _
& " WHERE (((tblPartRouteAssignment.PlantCode)=" & "'" & PubstrPlantCode & "'" & ") AND ((tblPartRouteAssignment.ModelYear)=" & "'" & PubstrModelYear & "'" & "))" _
& " ORDER BY tblPartRouteAssignment.PartNumber;"
Else
'for selection from frmRouteDetailInfo
Me.RecordSource = "SELECT tblPartRouteAssignment.Route, tblPartRouteAssignment.PartNumber, tblPartRouteAssignment.Station, tblPartRouteAssignment.LineFeedDeliveryType," _
& " tblPartRouteAssignment.ReceivingDock, tblPartRouteAssignment.LocFullStorage, tblPartRouteAssignment.LocLineFeedStaging," _
& " tblPartRouteAssignment.LocEmptyStorage, tblPartRouteAssignment.LocDockReturn, tblPartInfo.Description," _
& " IIf([StationBld_Rate]=0,[VerifiedBld_Rate],[StationBld_Rate]) AS Bld_Rate, tblPartInfo.PackDensity," _
& " tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height," _
& " [Bld_Rate]/[PackDensity] AS COPD, tblLinefeedHandling.LineFeedHandling, tblLinefeedHandling.StagingHandling," _
& " tblLinefeedHandling.EmptyReturnHandling, tblPartRouteAssignment.StackHtReceiving, tblPartRouteAssignment.StackHtLFDelivery," _
& " (([LineFeedHandling]/[StackHtLFDelivery])+([StagingHandling]/[StackHtReceiving])+([EmptyReturnHandling]/[StackHtReceiving])) AS HdlgPerCont," _
& " tblPartRouteAssignment.TravDistLineFeed, tblPartRouteAssignment.TravDistStaging, tblPartRouteAssignment.TravDistDock," _
& " tblPartRouteAssignment.TravDistEmpty, (([TravDistLineFeed]/[StackHtLFDelivery])+([TravDistStaging]/[StackHtReceiving])+([TravDistEmpty]/[StackHtReceiving])) AS TravelPerTrip," _
& " [TravelPerTrip]*0.00227 AS TravelMinPerTrip, [HdlgPerCont]+[TravelMinPerTrip] AS AllocPerCont, [AllocPerCont]*[COPD] AS LFAllocation," _
& " tblPlantInfo.WorkingMinPerShift, tblPartRouteAssignment.PlantCode, tblPartRouteAssignment.ModelYear" _
& " FROM ((((tblPartRouteAssignment INNER JOIN tblPlantInfo ON tblPartRouteAssignment.PlantCode = tblPlantInfo.PlantCode)" _
& " INNER JOIN tblRouteInfo ON (tblPartRouteAssignment.Route = tblRouteInfo.Route)" _
& " AND (tblPartRouteAssignment.PlantCode = tblRouteInfo.PlantCode))" _
& " INNER JOIN tblPartStation ON (tblPartRouteAssignment.Station = tblPartStation.Station)" _
& " AND (tblPartRouteAssignment.PartNumber = tblPartStation.PartNumber)" _
& " AND (tblPartRouteAssignment.PlantCode = tblPartStation.PlantCode))" _
& " INNER JOIN tblLinefeedHandling ON tblPartRouteAssignment.LineFeedDeliveryType = tblLinefeedHandling.LineFeedType)" _
& " INNER JOIN tblPartInfo ON tblPartRouteAssignment.PartNumber = tblPartInfo.PartNumber" _
& " WHERE (((tblPartRouteAssignment.Route)=[Forms]![frmRouteDetailInfo]![Route])" _
& " AND ((tblPartRouteAssignment.PlantCode)=" & "'" & PubstrPlantCode & "'" & ") AND ((tblPartRouteAssignment.ModelYear)=" & "'" & PubstrModelYear & "'" & "))" _
& " ORDER BY tblPartRouteAssignment.PartNumber;"
End If

End Sub

That is working well but I need to allow the user to open a Summary Report, summing up the values from the first query. It's on a seperate report so I imagine I need the same type of original query but do a sum of the columns. The Summary Query aboce is what I get if I create a query in Access to sum up the values from the query qryRouteEfficiencyDetail. Is there a way I can do this at form open of the Summary Report?

Thanks,
Joel




Joel

 

[joel009]

In case of any confusion.

The report mentioned above that I am opening and closing is the detail report.
I am having problems with the Summary report RecordSource.

Joel

Joel

 

[joel009]

Either this question is so simple folks are waiting for me to figure it out....
OR
I stumped the stars?????

Anyone have any ideas???

I've looked at filters and they do not seem to be capable unless I am misreading or misinterpreting.

Joel

 

[dhookom]

I don't have the time to look at your query. If have a detail report and need a similar totals report, I typically just hide the detail section.

Duane
Hook'D on Access
MS Access MVP