Multiple record Report (1 Viewer)

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
Hi All,

I have a report with quite a few sub report
the main report was previously only 1 single record with the sub reports displaying data from different tables
some time the report could go onto a 2nd page

i now have to change the report so the user could select more than 1 record at a time to print
this current report took me quite a while to get to what was needed and that worked
now with multiple record. i am not sure how to keep my page numbering grouped, each new record should start from page 1 where as before it was not an issue

i can not ask you to assist me in the layout an designing but what i would need is a training video of some sort with different scenarios if possible

1611929745601.png
 

bastanu

AWF VIP
Local time
Today, 12:40
Joined
Apr 13, 2010
Messages
1,401
Maybe these links would help you:
Cheers,
 

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
Hi,

I found a code from ArnelGP
I created my ClassModel
Option Compare Database
Option Explicit

Dim grpArrayPage() As Integer, grpArrayPages() As Integer
Dim grpNameCurrent As Variant, grpNamePrevious As Variant
Dim grpPage As Integer, grpPages As Integer

Public Function ReportPageOnGroup(Page As Integer, Pages As Integer, varGroupValue As Variant)
Dim i As Integer
If Pages = 0 Then
ReDim Preserve grpArrayPage(Page + 1)
ReDim Preserve grpArrayPages(Page + 1)

grpNameCurrent = varGroupValue

If grpNameCurrent = grpNamePrevious Then
grpArrayPage(Page) = grpArrayPage(Page - 1) + 1
grpPages = grpArrayPage(Page)
For i = Page - (grpPages - 1) To Page
grpArrayPages(i) = grpPages
Next

Else
grpPage = 1
grpArrayPage(Page) = grpPage
grpArrayPages(Page) = grpPage

End If
Else
ReportPageOnGroup = "Page " & grpArrayPage(Page) & " Of " & grpArrayPages(Page)

End If
grpNamePrevious = grpNameCurrent

End Function

Private Sub Class_Initialize()
' nothing to do here
End Sub

Private Sub Class_Terminate()
' remove array and reset values
Erase grpArrayPage
Erase grpArrayPages
grpNameCurrent = Null
grpNamePrevious = Null
grpPage = 0
grpPages = 0
End Sub

and on my main report (with sub reports)
Option Compare Database
Option Explicit


Dim clsReportEject As clsReportGroupPage

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Dim strText As String
Dim var As Variant

strText = clsReportEject.ReportPageOnGroup([Page], [Pages], [Sequence])
strText = Trim$(Replace$(strText, "Page ", ""))
If Len(strText) > 0 Then
var = Split(strText, " of ")
[txtPageFooter] = var(0) & " of " & var(1)
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set clsReportEject = New clsReportGroupPage
End Sub

Private Sub Report_Close()
Set clsReportEject = Nothing
End Sub

I created my Unbound control called TxtPageFooter

but when I print preview, the control is blank
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:40
Joined
Sep 21, 2011
Messages
14,046
You shouldn't be doing this on the report.?
You should have a form or other means to select the data, then use your report.?
 

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
You shouldn't be doing this on the report.?
You should have a form or other means to select the data, then use your report.?
I am not sure, i used the sample i found from ArnelGP :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:40
Joined
Sep 21, 2011
Messages
14,046
I am not sure, i used the sample i found from ArnelGP :)
If you use arnelgp's code, you need to understand it. Often it is complex, and the slightest error on your part means it does not work, which is probably the case here. Hopefully he will chip in and tell you what you have done wrong.

I would have just had a means to select relevant records and then run the report for each record? No need to change the report.?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:40
Joined
May 21, 2018
Messages
8,463
I would have just had a means to select relevant records and then run the report for each record? No need to change the report.?
@Gasman,
I think there is some confusion. The code from @arnelgp is meant for numbering 1 of N for each group in the report. It does not have to do with selecting records to print. The OP may in additiona want a form to select multiple records.
 

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
@Gasman,
I think there is some confusion. The code from @arnelgp is meant for numbering 1 of N for each group in the report. It does not have to do with selecting records to print. The OP may in additiona want a form to select multiple records.
Correct, i have already selected my records to print in a form
the report needs to reset the page numbers every time the sequence number changes
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:40
Joined
May 21, 2018
Messages
8,463
Looking at how you instantiated I did not see any glaring errors, but could you post the actual link to @arnelgp code. He is usually on later in the day.
Can you post a sample db? It is hard to debug something that does nothing versus errors without having an example.
If you want to debug I would put a lot of Debug.prints in the header format event to see if you are returning anything from the array.
 

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
Looking at how you instantiated I did not see any glaring errors, but could you post the actual link to @arnelgp code. He is usually on later in the day.
Can you post a sample db? It is hard to debug something that does nothing versus errors without having an example.
If you want to debug I would put a lot of Debug.prints in the header format event to see if you are returning anything from the array.
ArnelGP's DB
 

Attachments

  • GroupNumbering (1).accdb
    652 KB · Views: 278

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:40
Joined
May 21, 2018
Messages
8,463
I cannot see it. Looks like you repeat everything he does. I would think the subreport should still work.
Please put some debug.print code in your event to see if anything is happening. The simple thing if you copied this is to check for
[Event Procedure] in the format event.
 

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
Hi,

yes i basically copied the code and amended to my control name
the sub report does work perfectly as before
the only thing is that the unbound control does not count the pages, it remains blank
i will have to search for debugging code as i am not familiar with it
I believe there is an event screen that shows what is happening when you run a code but not sure how to use it or where to find it
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:40
Joined
May 21, 2018
Messages
8,463
here is a great link

When a code does nothing it is hard to see what "is not" happening. So a messge box or some debug.print lines can help to show what lines are executing.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:40
Joined
May 7, 2009
Messages
19,169
see this sample (20_MainReport it has 1 subreport)
see also the code behind.
 

Attachments

  • GroupNumbering (1).accdb
    940 KB · Views: 285

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
see this sample (20_MainReport it has 1 subreport)
see also the code behind.
Hi ArnelGP
i have the code pretty much the same as your's,
the control txtPageFooter remains blank

Option Compare Database
Option Explicit


Dim clsReportEject As clsReportGroupPage

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Dim strText As String
Dim var As Variant

strText = clsReportEject.ReportPageOnGroup([Page], [Pages], [SequenceTextBox])
strText = Trim$(Replace$(strText, "Page ", ""))
If Len(strText) > 0 Then
var = Split(strText, " of ")
[txtPageFooter] = var(0) & " of " & var(1)
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set clsReportEject = New clsReportGroupPage
End Sub

Private Sub Report_Close()
Set clsReportEject = Nothing
End Sub

1612243263993.png
 

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
here is a great link

When a code does nothing it is hard to see what "is not" happening. So a messge box or some debug.print lines can help to show what lines are executing.
Tnx, will definitely have al look at this
 

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
Hi,

Sorry, took me a while to have a sample DB
attached is my DB for the selected report
the report to open is DAW Sheet - Final
DAW Sheet - Final 1 is the sub report with all the other sub reports
 

Attachments

  • DAW Sheet Report.accdb
    2.7 MB · Views: 260

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:40
Joined
May 7, 2009
Messages
19,169
it only took a minute to figure it out.
i forgot to tell you, you need to "add" page number" to the report, and then
set set their Visible property to No.

see "DAW Sheet - Final" report
 

Attachments

  • DAW Sheet Report.accdb
    2.8 MB · Views: 265

Gismo

Registered User.
Local time
Today, 21:40
Joined
Jun 12, 2017
Messages
1,298
it only took a minute to figure it out.
i forgot to tell you, you need to "add" page number" to the report, and then
set set their Visible property to No.

see "DAW Sheet - Final" report
wow, works perfectly
funny enough, i had the page number on my report before but did not work, so i deleted is as it had no function

thank you very much for your code
much appreciated
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:40
Joined
May 7, 2009
Messages
19,169
good luck with your project :)
 

Users who are viewing this thread

Top Bottom