Report displaying wrong page number

[gmazza via AccessMonster.com]

Hey there,
I have a bunch of reports and this problem is only failing on 1 report and I
can't figure it out.
At the bottom of each report I have a text box with this as the control
source:
="Page " & [Page] & " of " & [Pages]

For 1 report of mine, its only 3 pages and this text box says Page 1 of 13
every time.
After the 3rd and final page it says Page 3 of 13 and you can't go forward
anymore on the navigation bar. Thats it, its really weird, anyone have any
suggestions?
Thanks!

 

[Allen Browne]

There can be several reasons for this.

One is if you anything in the report or its module named Pages. Setting this
property/textbox/variable/whatever is likely to interfere with the Pages
property of the report.

The other thing is any code in the report's sections that hides or repeats
sections. The may not fire for all the pages, with the result that the page
count is out. You can get some quite interesting nonsense, such as Page 10
of 9.

 

[Marshall Barton]

I have a bunch of reports and this problem is only failing on 1 report and I
can't figure it out.
At the bottom of each report I have a text box with this as the control
source:
="Page " & [Page] & " of " & [Pages]

For 1 report of mine, its only 3 pages and this text box says Page 1 of 13
every time.
After the 3rd and final page it says Page 3 of 13 and you can't go forward
anymore on the navigation bar. Thats it, its really weird, anyone have any
suggestions?

This kind of thing is almost always caused by some code
behind the report that is making something visible/invisible
based on the value of Pages.

Try searching the report's module for Pages and checking
what the code is doing. If you can't fix the problem, post
back with a Copy/Paste of the procedure(s?) and an
explanation of why you wanted to use that kind of code.

 

[gmazza via AccessMonster.com]

Thanks for the replies guys.
I do have code that is changing Control Sources based on the parameter form
that opens the report.
In the Report_Activate Event, I have this:
Me.Filter = Forms![SelectFieldDateRange]![ReportFilter]
' Changing values depending on what was chosen in the parameter form.
Me.GroupLevel(0).ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value
Me.txtFieldName.ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value

If Me.txtFieldName.ControlSource = "FormName" Then
lblField.Caption = "Username"
txtChange.ControlSource = "Username"
Else
lblField.Caption = "Form"
txtChange.ControlSource = "FormName"
End If

I have a bunch of reports and this problem is only failing on 1 report and I
can't figure it out.

[quoted text clipped - 7 lines]

anymore on the navigation bar. Thats it, its really weird, anyone have any
suggestions?

This kind of thing is almost always caused by some code
behind the report that is making something visible/invisible
based on the value of Pages.

Try searching the report's module for Pages and checking
what the code is doing. If you can't fix the problem, post
back with a Copy/Paste of the procedure(s?) and an
explanation of why you wanted to use that kind of code.

 

[Marshall Barton]

Thanks for the replies guys.
I do have code that is changing Control Sources based on the parameter form
that opens the report.
In the Report_Activate Event, I have this:
Me.Filter = Forms![SelectFieldDateRange]![ReportFilter]
' Changing values depending on what was chosen in the parameter form.
Me.GroupLevel(0).ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value
Me.txtFieldName.ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value

If Me.txtFieldName.ControlSource = "FormName" Then
lblField.Caption = "Username"
txtChange.ControlSource = "Username"
Else
lblField.Caption = "Form"
txtChange.ControlSource = "FormName"
End If

None of that uses Pages so I don't see how it can have
anything to do with the problem.

I question your use of the Activate event though. It is
only used when you preview the report and totally ignored if
you open the report in Normal view (i.e. print). I think
all that code should be in the Open event.

 

[gmazza via AccessMonster.com]

Hey there,
I am still getting this problem, not sure what to do.
I went over all my code and I assure you I am not using the word Pages
anywhere.
I will paste ALL the code I have for the report and explain briefly what its
doing.
My employer wants all reports to work like so, open the report, then open the
parameter form, then filter it, then make the parameter form invisible and
display the report.
Here is the code for the report, please let me know if you have any advice,
thanks!

Private Sub Report_Activate()
If Me.HasData = False Then
MsgBox "No Data for Report.", vbExclamation + vbOKOnly, "No data
matches selection criteria"
Cancel = True
DoCmd.Close acReport, "ErrorLogField"
End If
End Sub

Private Sub Report_Close()
DoCmd.Close acForm, "SelectFieldDateRange", acSaveNo
End Sub

Private Sub Report_Open(Cancel As Integer)

DoCmd.OpenForm "SelectFieldDateRange", , , , , acDialog
Me.Filter = ""
If Forms![SelectFieldDateRange]![rptcancel] = True Then
Cancel = True
Else
Me.Filter = Forms![SelectFieldDateRange]![ReportFilter]
' Changing values depending on what was chosen in the parameter form.
Me.GroupLevel(0).ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value
Me.txtFieldName.ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value

If Me.txtFieldName.ControlSource = "FormName" Then
lblField.Caption = "Username"
txtChange.ControlSource = "Username"
Else
lblField.Caption = "Form"
txtChange.ControlSource = "FormName"
End If
End If
End Sub

Thanks for the replies guys.
I do have code that is changing Control Sources based on the parameter form

[quoted text clipped - 14 lines]

txtChange.ControlSource = "FormName"
End If

None of that uses Pages so I don't see how it can have
anything to do with the problem.

I question your use of the Activate event though. It is
only used when you preview the report and totally ignored if
you open the report in Normal view (i.e. print). I think
all that code should be in the Open event.

 

[Marshall Barton]

I am still getting this problem, not sure what to do.
I went over all my code and I assure you I am not using the word Pages
anywhere.
I will paste ALL the code I have for the report and explain briefly what its
doing.
My employer wants all reports to work like so, open the report, then open the
parameter form, then filter it, then make the parameter form invisible and
display the report.
Here is the code for the report, please let me know if you have any advice,
thanks!

Private Sub Report_Activate()
If Me.HasData = False Then
MsgBox "No Data for Report.", vbExclamation + vbOKOnly, "No data
matches selection criteria"
Cancel = True
DoCmd.Close acReport, "ErrorLogField"
End If
End Sub

Private Sub Report_Close()
DoCmd.Close acForm, "SelectFieldDateRange", acSaveNo
End Sub

Private Sub Report_Open(Cancel As Integer)

DoCmd.OpenForm "SelectFieldDateRange", , , , , acDialog
Me.Filter = ""
If Forms![SelectFieldDateRange]![rptcancel] = True Then
Cancel = True
Else
Me.Filter = Forms![SelectFieldDateRange]![ReportFilter]
' Changing values depending on what was chosen in the parameter form.
Me.GroupLevel(0).ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value
Me.txtFieldName.ControlSource = Forms!SelectFieldDateRange.cboChoice.
Value

If Me.txtFieldName.ControlSource = "FormName" Then
lblField.Caption = "Username"
txtChange.ControlSource = "Username"
Else
lblField.Caption = "Form"
txtChange.ControlSource = "FormName"
End If
End If
End Sub

You're right, there is nothing there that can cause the
problem I was talking about. I am stumped.

At this point the only thing I can think of is that the
report may have been corrupted somehow. Backup the
database, and try cutting all the code in the report to the
clipboard. then set the report's HasModule property to No.
Then close the report and compact the database. Finally,
reopen the report and paste the code into its new module.

BTW, the Cancel = True line in the Activate event doesn't do
anything and should have caused a compile error. If it does
not cause a compile error, it implies that you do not have
the line:
Option Explicit
at the top of the module. Using that line at the top of
every module is a very good idea because it tells the
compiler to complain about undeclared variables, which are
often the ones with typos.

 

[gmazza via AccessMonster.com]

I did what you said, copied the code to the clipboard, set HasModule property
to No, closed report, compacted db. Opened db, set HasModule property to Yes,
pasted code back in, ran the report, same thing.

One other thing it does is when it says Page 1 of 14, whenever I press the
navigation button to goto the last page, I get an Access error and the
database closes. Happens every time, weird...
I can send you my database, its tiny, if you are interested.

I also added Option Explicit and it did error and I took the line out, thanks!

I am still getting this problem, not sure what to do.
I went over all my code and I assure you I am not using the word Pages

[quoted text clipped - 43 lines]

End If
End Sub

You're right, there is nothing there that can cause the
problem I was talking about. I am stumped.

At this point the only thing I can think of is that the
report may have been corrupted somehow. Backup the
database, and try cutting all the code in the report to the
clipboard. then set the report's HasModule property to No.
Then close the report and compact the database. Finally,
reopen the report and paste the code into its new module.

BTW, the Cancel = True line in the Activate event doesn't do
anything and should have caused a compile error. If it does
not cause a compile error, it implies that you do not have
the line:
Option Explicit
at the top of the module. Using that line at the top of
every module is a very good idea because it tells the
compiler to complain about undeclared variables, which are
often the ones with typos.

 

[Marshall Barton]

I did what you said, copied the code to the clipboard, set HasModule property
to No, closed report, compacted db. Opened db, set HasModule property to Yes,
pasted code back in, ran the report, same thing.

One other thing it does is when it says Page 1 of 14, whenever I press the
navigation button to goto the last page, I get an Access error and the
database closes. Happens every time, weird...

That is weird. Maybe(?) there is more corruption than just
the report's code, check out the Corruption / Repair section
at http://allenbrowne.com/tips.html

 

[gmazza via AccessMonster.com]

Yes I have tried all that, and I just did it again and nothing.
I only have 77 records in the table. I deleted all but 2 and it said page 1
of 1 on the report. I added 18 more records, and it said Page 1 of 2. I added
another 18 records and it said Page 1 of 14 again.
I am at a stand still here, maybe I'll try creating a new database and
copying the fields over from the report to a new report, same with the table
and module code and see what happens?

I did what you said, copied the code to the clipboard, set HasModule property
to No, closed report, compacted db. Opened db, set HasModule property to Yes,

[quoted text clipped - 3 lines]

navigation button to goto the last page, I get an Access error and the
database closes. Happens every time, weird...

That is weird. Maybe(?) there is more corruption than just
the report's code, check out the Corruption / Repair section
at http://allenbrowne.com/tips.html

 

[Marshall Barton]

Yes I have tried all that, and I just did it again and nothing.
I only have 77 records in the table. I deleted all but 2 and it said page 1
of 1 on the report. I added 18 more records, and it said Page 1 of 2. I added
another 18 records and it said Page 1 of 14 again.
I am at a stand still here, maybe I'll try creating a new database and
copying the fields over from the report to a new report, same with the table
and module code and see what happens?

We're both shooting in the dark here. Your plan sounds like
a good next step, but who knows?