> Method 1: I'll assume the form that calls the report is called MainForm.
In the subreports recordsource, you have a column field called
YearOfPublication. In the criteria row for that field enter
=Forms!MainForm!YearOfPublication Or IsNull()
Method 2: I'm not testing this...I'll leave this up to you.
I don't know what the recordsource is. Let's say
Select * From Employee
You don't need an orderby clause since that is performed in the Groupings
and Sortings
Let's say the form that opens the report is named MainForm. Let's assume
you have a method to determine it the form is open (IsOpen(),
IsLoaded()...see Google if you don't)...then store the "filter" in a
invisible field. Lets call it HiddenFilter. In this case
Me.HiddenFilter = "YearOfPublication=1926"
Docmd.OpenReport.
Now in the OnOpen event of the subreport enter
If IsLoaded("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm!HiddenFilter
Me.Recordsource = strSQL
Endif
Endif
If that doesn't work, you could try it from the OnOpen event of the Main
report.
If IsLoaded("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm!HiddenFilter
Me("SubReportName").Report.Recordsource = strSQL
Endif
Endif
Many thanks for you reply,
Method 1 works well, i have used qryBooks as the record source for my
subreport rptBooks and in the criteria of YearOfPublication have simply put
the following:
[Forms]![MainForm]![cboYear]
cboYear being the combo box on the main form which is used to select the
year of publication.
This gives the following SQL:
SELECT tblBooks.BookID, tblBooks.Title, tblBooks.AuthorID,
tblBooks.YearOfPublication
FROM tblBooks
WHERE (((tblBooks.YearOfPublication)=[Forms]![MainForm]![cboYear]));
This works excellently and is nice and simple for me to understand.
The only problem is if cboYear is blank it returns no books; is there any
way that it can be made to return all the books if it is blank?
Below i have detailed the problems i encountered with method 2 for the sake
of interest.
Method2
Doesn't seem to work so good. Trying to chang the recordsource of the
subreport by putting the following in the OnOpen event of the subreport:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "Select * from qryBooks"
End Sub
gives me the following error:
Run-time error '2191':
You can't set Record Source propery in print preview or after printing has
started.
Strangely this works fine if the subreport is opened on its own. the error
only occurs when it is opened as part of the main form. This seems similar
to the problem of setting the filter in the OnOpen - it works fine when the
subreport is opened on its own but causes an error when opened form the main
form.
Whilst trying to change the recordsource of the subreport from the OnOpen
event of the main report:
Private Sub Report_Open(Cancel As Integer)
Me("Books").Report.RecordSource = "select * from qryBooks"
End Sub
gives me the following error:
Run-time error '2455':
You entered an expression that has an invalid reference to the property
Form/Report