Report using wrong source

[Wayne311]

Hello,

I have 2 reports with queries as the data source for both. They both have the same code with a loop, query, and querydef. They both have the same query in the code as is the data source. In the code a variable is dumped into a string as a "WHERE" parameter for stepping through the loop and creating a report...one report per record. The first report (Account) works fine. It has the string in the code query and a literal in the data source query...it uses the code to create the reports.

Private Sub cmdPrintRoute_Click()
 
 
'------------------------------------------------------------------------------
'
' Print Route Slip
'
'-------------------------------------------------------------------------------
 
On Error GoTo Statement_Err
 
Dim qdf As DAO.QueryDef
Dim RouteRst As DAO.Recordset
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strCaseNumber As String
'Dim strNewDBPath As String
Dim strQueryName As String
'Dim fso
Dim strRouteSavedReportPath As String
 
'strRoute = DLookup("Account", "AccountNumbers")
strRouteSavedReportPath = DLookup("SavedReport", "RoutePath")
strQueryName = "RouteSlip"
 
'MsgBox (strRoute)
Me.Refresh
'MsgBox (strQueryName)
 
Set RouteRst = CurrentDb.OpenRecordset("SELECT * FROM [Route] WHERE [Printt] = True")
With RouteRst
If Not .EOF Then
.MoveFirst
 
Do Until .EOF
 
strCaseNumber = (RouteRst![CaseNumber])
 
MsgBox (strCaseNumber)
'MsgBox (strQueryName)
 
 
 
strSQL = "SELECT Route.CaseNumber, Route.PID, Route.TypeOfService, Route.EntryDate, Route.HearingDate, Route.LastDateOfService, Route.Memo, " & _
Route.Printt, SERVTYPS.[TYPE OF PAPERS], PID.PIDID, PID.LastName, PID.FirstName, PID.MiddleName, PID.DOB, PID.Race, PID.Ethnicity, PID.Gender, " & _
PID.Height, PID.Weight, PID.Hair, PID.Eyes, PID.Memo, AddressPIDJunction.PIDFK, Address.AddressID, Trim(Address.StreetNumber & ' ' & Address.Direction " & _
& ' ' & Address.StreetName) & (' ' +StreetType) & (' '+UnitNumber) & (' '+City) & (', '+State) & (' '+Zip) & (' '+Address.Memo) AS FullAddress, " & _
Types.Description, AddressPIDJunction.AddressPIDJunctionID, AddressPIDJunction.AddressFK, AddressPIDJunction.TypesFK " & _
"FROM SERVTYPS, Types INNER JOIN ((PID INNER JOIN Route ON PID.PIDID = Route.PID) INNER JOIN (Address INNER JOIN AddressPIDJunction " & _
ON Address.AddressID = AddressPIDJunction.AddressFK) ON PID.PIDID = AddressPIDJunction.PIDFK) ON Types.TypesID = AddressPIDJunction.TypesFK " & _
"WHERE (((Route.CaseNumber = '" & strCaseNumber & "') AND (Route.Printt)=True) AND ((SERVTYPS.ID)=Int([Route]![TypeOfPaper])));"
 
'Debug.Print strSQL
 
'Look for QueryDef RouteSlip. If it exists, delete it.
If DCount("*", "MSysObjects", "Type = 5 AND Name = '" & strQueryName & "'") > 0 Then
CurrentDb.QueryDefs.Delete strQueryName
End If
 
'MsgBox (strQueryName)
 
'Create new QueryDef
Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL) 'this requires the Query to be deleted each pass
'Set qdf = CurrentDb.QueryDefs("RouteSlip") 'this assumes the Query always exists
qdf.SQL = strSQL
 
Set rst = qdf.OpenRecordset
 
 
 
'MsgBox (strAccountsSavedReportPath)
 
 
 
'Save the report as PDF
DoCmd.OutputTo acOutputReport, "Route1", acFormatPDF, strRouteSavedReportPath & strCaseNumber & ".pdf"
 
strCaseNumber = ""
 
 
.MoveNext
 
Loop
End If
.Close
End With
 
 
'Open folder containing created documents
'Shell "C:\WINDOWS\explorer.exe """ & ProjPath & "", vbNormalFocus
Shell "C:\Windows\explorer.exe """ & strRouteSavedReportPath & "", vbNormalFocus
 
 
'DoCmd.SetWarnings (False)
'DoCmd.RunSQL ("UPDATE Route SET Route.Printt = 0;")
'Me.Refresh
'DoCmd.SetWarnings (True)
 
 
Statement_Exit:
Exit Sub
 
Statement_Err:
MsgBox Error$
Resume Statement_Exit
 
End Sub

The other report is the same, but it is using the data source query for creating the reports. If it has a literal in the "WHERE" statement in the data source it creates that report. If it has the string, it creates a blank report. I cannot figure out why the same code is behaving so different. Any suggestions?

Thanks
Wayne