Report trouble in Access 2000

[blago]

I have an Access 2000 database that contains a report that is supposed to have data populated in the eight rows. When the data is more than eight, it should go to another page with the extra rows, and I get a message that properties for r.nextrecord and r.movelayout are being changed to true from false. It is putting all the rows on the same page and pushing the footer to the next page.



Also, if the records on the report equal seven, I get a duplicate of record #7. My code is below. What am I missing?

Option Compare Database
Option Explicit
Dim rpt As Report
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
On Error GoTo errorHandler

Set rpt = Reports(Me.Name)
Call PrintLines(rpt, [TotGrp])
Exit Sub
errorHandler:
MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
On Error GoTo errorHandler

Set rpt = Reports(Me.Name)
Call SetCount(rpt)
Exit Sub
errorHandler:
MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub
Private Sub Report_Activate()
On Error GoTo errorHandler
Set rpt = Reports(Me.Name)
Call SetCount(rpt)
Call PrintLines(rpt, [TotGrp])

Exit Sub
errorHandler:
MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo errorHandler
MsgBox "Sorry, there is not data to report", vbInformation, "Print Request Cancelled"
Cancel = True
Exit Sub
errorHandler:
MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub

' Call the SetCount() function from the group header section's
' OnPrint property using the syntax: =SetCount(Report)
Function SetCount(R As Report)
TotCount = 0
R![col1].Visible = True
R![col2].Visible = True
R![col3].Visible = True
R![col4].Visible = True
R![col5].Visible = True
R![col6].Visible = True
R![col7].Visible = True
R![col8].Visible = True
R![col9].Visible = True
End Function
' Call the PrintLines() function from the detail section's OnPrint
' property using the syntax: =PrintLines(Report,[TotGrp]).
Function PrintLines(R As Report, TotGrp)

TotCount = TotCount + 1

If TotCount = TotGrp Then
R.NextRecord = False
If TotGrp >= 8 Then
R.MoveLayout = False
Else
R.MoveLayout = True
End If
ElseIf TotCount > TotGrp Then
If TotCount < 8 Then
R.NextRecord = False
R.MoveLayout = True
R![col1].Visible = False
R![col2].Visible = False
R![col3].Visible = False
R![col4].Visible = False
R![col5].Visible = False
R![col6].Visible = False
R![col7].Visible = False
R![col8].Visible = False
R![col9].Visible = False
End If
Else
R.MoveLayout = True
R.NextRecord = True
End If

End Function

[weekend00]

too complicated to me.

[ajetrumpet]

too complicated to me.

Not necessarily complicated, but very unorganized. This needs format, [CODE] tags in the post, and general oganization. Without, there probably won't be an answer here

[blago]

Not necessarily complicated, but very unorganized. This needs format, [CODE] tags in the post, and general oganization. Without, there probably won't be an answer here

Ok. Where do I start as far as getting you what you need to help me? I do not follow your post. I just did a copy and paste from the code in access. Do you need to know what sections the codes go in?

[ajetrumpet]

Ok. Where do I start as far as getting you what you need to help me? I do not follow your post. I just did a copy and paste from the code in access. Do you need to know what sections the codes go in?

For example, enclose all of you text in the post below this line:

Also, if the records on the report equal seven, I get a duplicate of record #7. My code is below. What am I missing?

in [CODE] tags. Indentation wouldn't hurt either, so it doesn't look jumbled. I personally don't have time right now to read through it all and try to assist, but this will give you a better chance at getting an answer from someone in the community.

How to use tags: https://www.accessforums.net/misc.php?do=bbcode#code

[blago]

For example, enclose all of you text in the post below this line:in [CODE] tags. Indentation wouldn't hurt either, so it doesn't look jumbled. I personally don't have time right now to read through it all and try to assist, but this will give you a better chance at getting an answer from someone in the community.

How to use tags: https://www.accessforums.net/misc.php?do=bbcode#code

Thanks. Here it goes:

Option Compare Database
Option Explicit
 
Dim rpt As Report
 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
On Error GoTo errorHandler
 
     Set rpt = Reports(Me.Name)
     Call PrintLines(rpt, [TotGrp])
 
Exit Sub
errorHandler:
     MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub
 
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
On Error GoTo errorHandler
 
     Set rpt = Reports(Me.Name)
     Call SetCount(rpt)
Exit Sub
errorHandler:
     MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub
 
Private Sub Report_Activate()
On Error GoTo errorHandler
 
     Set rpt = Reports(Me.Name)
     Call SetCount(rpt)
     Call PrintLines(rpt, [TotGrp])
 
Exit Sub
errorHandler:
     MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub
 
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo errorHandler
 
     MsgBox "Sorry, there is not data to report", vbInformation, "Print Request Cancelled"
     Cancel = True
 
Exit Sub
errorHandler:
     MsgBox Err.Number & " - " & Err.Description, vbInformation, strErrorLabel_pub
End Sub
 
' Call the SetCount() function from the group header section's
' OnPrint property using the syntax: =SetCount(Report)
 
Function SetCount(R As Report)
 
     TotCount = 0
     R![col1].Visible = True
     R![col2].Visible = True
     R![col3].Visible = True
     R![col4].Visible = True
     R![col5].Visible = True
     R![col6].Visible = True
     R![col7].Visible = True
     R![col8].Visible = True
     R![col9].Visible = True
 
End Function
 
' Call the PrintLines() function from the detail section's OnPrint
' property using the syntax: =PrintLines(Report,[TotGrp]).
 
Function PrintLines(R As Report, TotGrp)
 
     TotCount = TotCount + 1
 
     If TotCount = TotGrp Then
          R.NextRecord = False
          If TotGrp >= 8 Then
               R.MoveLayout = False
          Else
               R.MoveLayout = True
          End If
     ElseIf TotCount > TotGrp Then
          If TotCount < 8 Then
               R.NextRecord = False
               R.MoveLayout = True
               R![col1].Visible = False
               R![col2].Visible = False
               R![col3].Visible = False
               R![col4].Visible = False
               R![col5].Visible = False
               R![col6].Visible = False
               R![col7].Visible = False
               R![col8].Visible = False
               R![col9].Visible = False
          End If
     Else
          R.MoveLayout = True
          R.NextRecord = True
     End If
 
End Function

[blago]

Can anyone please help with this? My report has eight rows in a table based on an old paper form. This table works great in the report for six or less records (in the detail section) but starts to blow up after that. I can't see where my mistake is...

[NTC]

give me a plain english (no code) explanation of the goal here;

A report that handles 8 records on one page?

What is to happen if there are more than 8 records?

keep it brief.

[blago]

give me a plain english (no code) explanation of the goal here;

A report that handles 8 records on one page?

What is to happen if there are more than 8 records?

keep it brief.

Thanks, NTC...

Yes, my report handles 8 records. It would be nice for it to go to another page if the records exceed 8.

[NTC]

because your post began with alot of custom code - - it isn't clear to me whether or not you have attempted to set this up using the vanilla report features. grouping & sorting has properties to 'keep together' (or not).

also of course - you can control which header/footer info repeats on each page by placing it in the correct section i.e. 'report' header vs 'page' header.

now - for sure it is tricky to make a report that handles exactly 8 records on a single page. one has to adjust white space, font etc. alot of tweaking & trial. Also you are a little at the mercy of the graphic driver/print driver such that sometimes I see a report make its page break slightly different at a different PC. But all in all I think it is feasible for you to get this working with some experimentation using generic report features.

[blago]

I have played around with the various settings for the report any have gotten various outcomes. Just not quite what I was looking for...

Any idea why it would repeat record #7 if there are seven records?

[NTC]

First you should check that the underlying record set doesn't have a duplicate record - if so it needs to be fixed there.

If it is for sure in the report only; definitely do a compact/repair - and retry. Also try it on a different PC to see if it is a graphic/print issue - if so it may appear on the screen but not when printed...

[blago]

Thanks for the suggestion. I'll give it a try.

[blago]

I tried the suggestions to no avail...