Transferring Filter and OrderBy string from Subform to Report

  • Thread starter Thread starter Dave the wave
  • Start date Start date
D

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.
 
Dave, Why not include the filter in the query and that way
both reports have the same information?
Fons
 
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.
 
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.
 
Back
Top