Related table query and DoCmd.OpenReport (1 Viewer)

[Rod C]

I have a query that has a main table and a related table, one to many. I am using the following code to open a report and to pass a query name and record selection criteria.

DoCmd.OpenReport "rptAllDataFields1", acPreview, "qryAllDataFields3a", "[County] = 'Deschutes'"

DoCmd.OpenReport "rptAllDataFields1", acPreview, "qryAllDataFields3a", "[Component] = 'ITS'"

The first syntax works, the criteria, "[County] = 'Deschutes'", is a field in the main table.

The second syntax does not work, the criteria, "[Component] = 'ITS'", is a field in the related table. It gives me an “Enter Parameter Value” pop-up that wants “Component”.

The query works if you put the criteria "ITS" in the Criteria row just not when it is passed with the code.

 

[Travis]

Can you post the SQL behind your query?

 

[Rod C]

OK, here you go, it's a little more complicated than my initial explanation, I actually have several related tables.

SELECT tblMasterNeeds.KeyNum, tblMasterNeeds.TempProjNum, tblMasterNeeds.ProjName, tblMasterNeeds.ProjDes, tblMasterNeeds.Purpose, tblMasterNeeds.History, tblMasterNeeds.ProjSource, tblMasterNeeds.HwyNum, tblMasterNeeds.RouteNum, tblMasterNeeds.BMP, tblMasterNeeds.EMP, tblMasterNeeds.LaneMiles, tblMasterNeeds.SCS, tblMasterNeeds.County, tblMasterNeeds.District, tblMasterNeeds.Category1, tblMasterNeeds.Category2, tblMasterNeeds.PEEstimate, tblMasterNeeds.RWEstimate, tblMasterNeeds.ConstEstimate, tblMasterNeeds.AppConstAmnt, tblMasterNeeds.LocalFunding, tblMasterNeeds.PrelimScope, tblMasterNeeds.RefinedScope, tblMasterNeeds.ScopeDate, tblMasterNeeds.ScopeAuthor, tblMasterNeeds.ProspectusPart, tblMasterNeeds.ProspectusDate, tblMasterNeeds.ProspectusAuthor, tblMasterNeeds.TargetSTIPYear, tblMasterNeeds.ApprovedSTIPYear, tblMasterNeeds.InTSP, tblMasterNeeds.TSPPriority, tblMasterNeeds.EcDev, tblMasterNeeds.QDOs, tblMasterNeeds.OHP1G, tblMasterNeeds.FreightGaps, tblMasterNeeds.RegPriority, IIf([RegPriority]<1000,"High",IIf([RegPriority]<2000,"Medium",IIf([RegPriority]<3000,"Low","NR"))) AS RegPriorityText, tblMasterNeeds.RegPriorityFlag, tblMasterNeeds.Notes, tblMasterNeeds.DevConsider, tblMasterNeeds.Archived, tblMasterNeeds.ArchiveReason, tblMasterNeeds.PlanningNarrative, tblExistingADT.ADTYear, tblExistingADT.ADT, tblFutureADT.ADTYear, tblFutureADT.ADT, tblExistingSegVC.VCRatioRange, tblExistingSegVC.HighestVC, tblExistingSegVC.OHPVCStd, tblSIP.SIPYear, tblSIP.SIPCategory, tblSPIS.SPISYear, tblSPIS.SPISValue, tblSPIS.SPISPercent, tblQDOLookup.QDODescription, tblOHPLookup.OHPDescription, tblComponent.Component
FROM (((((((tblMasterNeeds LEFT JOIN tblExistingADT ON tblMasterNeeds.TempProjNum = tblExistingADT.TempProjNum) LEFT JOIN tblExistingSegVC ON tblMasterNeeds.TempProjNum = tblExistingSegVC.TempProjNum) LEFT JOIN tblFutureADT ON tblMasterNeeds.TempProjNum = tblFutureADT.TempProjNum) LEFT JOIN tblOHPLookup ON tblMasterNeeds.OHP1G = tblOHPLookup.OHP1G) LEFT JOIN tblQDOLookup ON tblMasterNeeds.QDOs = tblQDOLookup.QDOs) LEFT JOIN tblSIP ON tblMasterNeeds.TempProjNum = tblSIP.TempProjNum) LEFT JOIN tblSPIS ON tblMasterNeeds.TempProjNum = tblSPIS.TempProjNum) LEFT JOIN tblComponent ON tblMasterNeeds.TempProjNum = tblComponent.TempProjNum
WHERE (((tblMasterNeeds.Archived)=No) AND ((tblMasterNeeds.ArchiveReason) Is Null Or (tblMasterNeeds.ArchiveReason)=""))
ORDER BY tblMasterNeeds.TempProjNum;