Transferring Filter and OrderBy string from Subform to Report

[Dave the wave]

I hope someone can point out what I am missing.
I have a subform1 based on query1 and a report1 based on the same query1.
The user is allowed to filter and order the data in the subform. I want to
be able to print out a hard copy of the filtered data using report1. (For
now I am not concerned about grouping on the report.)
I'm assuming that if I copy the values for subform1.Form.Filter and
subform1.Form.OrderBy and paste them into report1.Filter and
report1.OrderBy, the ending result will be a report that contains the same
records as the screen display.

I tried referencing "subform1.Form.OrderBy" in the Open event of the report
but I either get "variable not defined" or "Can't find object.." depending
on my syntax for referencing the values.

Any help would be greatly appreciated.

 

[Fons Ponsioen]

Dave, Why not include the filter in the query and that way
both reports have the same information?
Fons

 

[Dave the wave]

That was my first plan; however, a parameter dialog window would pop up
asking me for values for the fields being filtered. I know I must be missing
some little point that keeps everything from working. I just can't seem to
figure out what that missing thing is.

Here's my latest attempt to solve the puzzle:
Private Sub Command35_Click()
If Not (Me.sfrmIReview.Form.FilterOn) Then
Exit Sub
End If
strSQL = "SELECT Repacks.RepackID, Repacks.RepackStatus,
Repacks.RepackNumber, Repacks.RepackDate, "
strSQL = strSQL & "Products.ProductsDescription, Defects.DefectName,
Employees.EmployeeName, Repacks.RepackRCARequired, "
strSQL = strSQL & "Repacks.Repack5YRequired, Repacks.RepackTotalCases "
strSQL = strSQL & "FROM Employees INNER JOIN (Defects INNER JOIN (Products
INNER JOIN Repacks ON Products.ProductID = Repacks.ProductID) "
strSQL = strSQL & "ON Defects.DefectID = Repacks.DefectID) ON
Employees.EmployeeID = Repacks.EmployeeID"
strSQL = strSQL & " WHERE" & sfrmIReview.Form.Filter
If sfrmIReview.Form.OrderBy <> "" Then
strSQL = strSQL & " ORDER BY " & sfrmIReview.Form.OrderBy
End If
strSQL = strSQL & ";"

stDocName = "rptFilteredReport"
DoCmd.OpenReport stDocName, acPreview, , sfrmIReview.Form.Filter

strSQL = ""
End Sub

rptFilteredReport is a Report design with it's record source set to the same
source as the subform ("sfrmIReview") -which is "qryRepackReview", and its
FilterOn and SortByOn set to true.

This actually works, but only temporarily. Maybe 1 or 2 times then I start
seeing the parameter dialog window again.

 

[Dave the wave]

DUH! Most of the code below -regarding the SQL statement- is not needed to
open the report with filtered data. Here is the crucial code:

Private Sub Command35_Click()
If Not (Me.sfrmIReview.Form.FilterOn) Then
Exit Sub
End If

stDocName = "rptFilteredReport"
DoCmd.OpenReport stDocName, acPreview, , sfrmIReview.Form.Filter

End Sub

With the report's record source set to the same query as the subform, the
above seems to work. What I now need is to sort the data according to any
sort the user applied. I tried to store the SortBy string in a public
variable, then set the report's SortBy property to the store string. I kept
getting a variable undefined error.

Any ideas?

Thanks for the help.