423,309 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,309 IT Pros & Developers. It's quick & easy.

Can a report be programmatically moved to last page?

P: n/a
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!

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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!

Nov 12 '05 #2

P: n/a
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!


Nov 12 '05 #3

P: n/a
>
I can't move a multi-page report to the last record unless I keep the popup
form (that defined it's subreports) open.


My solution (and I do this a lot) is that instead of closing the popup form, I
hide it (form.visible=false) and then I close it on the report's onclose event
(also on the onnodata if that's relevant).

HTH

Jan
Jan Stempel
Stempel Consulting
Nov 12 '05 #4

P: n/a
Deko,
Set <yourpopupform>.Visible = False as soon as you start running the
report, after the information has been entered. This keeps the popup
form open, but hidden.
In the .Close event of your report, issue <yourpopupform>.Close.
Cheers,
Doug

deko wrote:
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!


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.