Thanks for the reply.
I got a bit creative with the subreports, as you'll see from the below code.
But this gives the user a lot of power in regard to what records, and what
information relating to those records, is included on the report.
I found a solution to the problem I described in my earlier post by adding
this line of code behind the popup form:
Form_frmTools.Visible = False
And I have this on the Close Event of the report:
DoCmd.Close acForm, "frmTools"
seems to work okay...
As for the code:
This is behind the main report:
Private Sub Report_Open(Cancel As Integer)
Dim ctl As Control
Dim strRo As String
Dim strRs As String
Dim strSearchQry As String
For Each ctl In Me.Report 'show only selected report options (i.e.
subreports)
If Left(ctl.Name, 1) = "R" Then
strRo = ctl.Name
If DCount(strRo, "tblOutput", strRo & " = True") = 0 Then
ctl.Visible = False
Else
ctl.Visible = True
End If
End If
Next ctl
'set recordsource based on user-criteria
If DLookup("RecSel", "tblOutput") = 1 Then strSearchQry =
DLookup("SearchQry", "tblOutput")
If DLookup("RecSel", "tblOutput") = 2 Then strSearchQry = "qry101"
strRs = ("SELECT [tblEntity].[LastName], [tblEntity].[Company],
[tblEntity].[FirstName], [tblEntity].[MiddleName], [tblEntity].[Prefix],
[tblEntity].[Suffix], [tblEntity].[Title], [tblEntity].[Entity_ID],
[tblEntity].[Cat_ID] FROM tblEntity WHERE [tblEntity].[Entity_ID] IN (SELECT
Entity_ID FROM " & strSearchQry & ")")
Me.RecordSource = strRs
End Sub
The below code is behind each subreport, with "strRs" specifying a different
SQL statement for each subreport in question (each subreport pulls data from
a different table).
Private Sub Report_Open(Cancel As Integer)
Static intCallCount As Long
Dim strSearchQry As String
Dim strRs As String
If intCallCount = 0 Then
If DLookup("RecSel", "tblOutput") = 1 Then strSearchQry =
DLookup("SearchQry", "tblOutput")
If DLookup("RecSel", "tblOutput") = 2 Then strSearchQry = "qry101"
strRs = ("SELECT [tblDocuments].[Document], [tblDocuments].[Doc_ID],
[tblDocuments].[Entity_ID] FROM tblDocuments WHERE
(([tblDocuments].[Entity_ID]) IN (SELECT Entity_ID FROM " & strSearchQry &
"))")
Me.RecordSource = strRs
intCallCount = intCallCount + 1
End If
End Sub
"Larry Linson" <bo*****@localhost.not> wrote in message
news:eQ******************@nwrddc02.gnilink.net...
I understand your Subreports to be in the Detail section, displayed for
each record in the underlying RecordSource. So, yes, it certainly makes sense
that each will be filled only when the corresponding detail record is
displayed.
I'd suggest you consider if there isn't some other way to provide the
criteria they need, other than the popup form (or make it just a normal
Form, so it won't display on top of the Report, and leave it open).
My normal use of Subreports is to display data in tables related to the
table in the main report, and I use the LinkMasterFields and
LinkChildFields to synchronize those. But, I can see that there might be times when you
need some additional criteria information.
If you are using Access 2002 or 2003, you could pass that information in
the OpenArgs argument of the Report; if earlier, set the information in a
Public variable instead of using a Control in a PopUp, or, as I suggested
earlier, use a regular Form instead and leave it open.
Larry Linson
Microsoft Access MVP
"deko" <dj****@hotmail.com> wrote in message
news:4T******************@newssvr25.news.prodigy.c om... I can't move a multi-page report to the last record unless I keep the
popup form (that defined it's subreports) open.
DoCmd.OpenReport "rptStandard", acViewNormal
DoCmd.Close acForm, "frmReportOptions" <== popup form
This is the error I get when I try to move to the last page of the
report *after* closing the popup:
"This expression is typed incorectly, or is too complex to be
evaluate...."
If I don't close the popup form -- but only minimize it -- I can
navigate all pages in the report no problem. The problem only happens if I close
the popup *before* moving to the last page of the report.
I think what is happening is that the subreports contained within the
main report have not loaded -- and the criteria they need to load are still
somehow linked to the popup form -- so until I move to the last page in
the report (with the popup open), the report still needs to load criteria
from the popup -- and therefore the subreorts on the other pages in the
report cannot evaluate their recordsource if the popup closes before they are
displayed.
I tried creating a public subroutine in rptStandard:
Public Sub GoToLast()
DoCmd.GoToRecord , , acLast
End Sub
and called it from the popup:
Report_rptStandard.GoToLast
Then I get this error:
Error Number 2499: You can't use the GoToRecord action or method on an
object in Design View.
Somehow I need to programmatically force the report to fully load (or
move it to the last page), while the popup is open -- then I should be able
to navigate the report with the popup closed.
If anyone has any suggestions, I would greatly appreciate it!
