Help on limit no of records in report

[history]

Hello friends

I have a report about the Procedures Type.
This report has group (ProcType)
So, every procedure type will show related employees.

The problem is :
The committee need just 8 records per page, it's OK I did it, but if the no exceed 8 (for example 9 ) the report should show just 5 records in the first page and 4 in the second page.

Example:
9 employees
first page is 5 second is 4
10 employees
first page is 5 second is 5
11 employees
first page is 6 second is 5
12 employees
first page is 7 second is 5
13 employees
first page is 8 second is 5
14 employees
first page is 8 second is 6
15 employees
first page is 8 second is 7
16 employees
first page is 8 second is 8

if the page exceeds 8 it should not has less than 5 and the remains should be distributed to next page

I don't know, it seems simple but I couldn't figure out

waiting your help

 

[vbaInet]

You need to explain to the committee that they should ask for realistic goals. Plus I don't understand the logic in how you derive the proportioning of records depending on the number of employees? Explain how that's worked out.

This statement, "if the page exceeds 8 it should not has less than 5 and the remains should be distributed to next page", isn't clear. Could you expand on this?

 

[history]

You need to explain to the committee that they should ask for realistic goals. Plus I don't understand the logic in how you derive the proportioning of records depending on the number of employees? Explain how that's worked out.

This statement, "if the page exceeds 8 it should not has less than 5 and the remains should be distributed to next page", isn't clear. Could you expand on this?

Hello friend

Thanks for response
They need the report show just 8 records per page for each procedure, sometimes they are 9 , " so the first page will be 8 and the second page will show 1 !!! they don't like this .
They're still thinking about Excel and Word !!! very traditional
I know that is a very stupid option, but I've nothing to say.

 

[vbaInet]

In that case it wouldn't be difficult. Here's one way:

You need:
1. A Page Break control placed in the Detail section - call it pgb1. Set: Visible to No.
2. A textbox control placed in the Detail section - call it txtCounter. Set: Control Source to =1, Running Sum to OverAll, Visible to No.

Your code should look like:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    pgb1.Visible = (txtCounter = 8)
End Sub

Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
    pgb1.Visible = False
End Sub

 

[history]

In that case it wouldn't be difficult. Here's one way:

You need:
1. A Page Break control placed in the Detail section - call it pgb1. Set: Visible to No.
2. A textbox control placed in the Detail section - call it txtCounter. Set: Control Source to =1, Running Sum to OverAll, Visible to No.

Your code should look like:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    pgb1.Visible = (txtCounter = 8)
End Sub

Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
    pgb1.Visible = False
End Sub

Unfortunately, it did nothing
The problem still exists,
The problem is :
If the no exceed 8 (for example 9 ) the report should show just 5 records in the first page and 4 in the second page.

Example:
9 employees
first page is 5 second is 4
10 employees
first page is 5 second is 5
11 employees
first page is 6 second is 5
12 employees
first page is 7 second is 5
13 employees
first page is 8 second is 5
14 employees
first page is 8 second is 6
15 employees
first page is 8 second is 7
16 employees
first page is 8 second is 8

I don't know if this problem has a solution or not.

Great thanks for your effort

 

[vbaInet]

And what if there are more than 16 employees?

 

[history]

And what if there are more than 16 employees?

it will be the same
for example:
17 employees
first page is 8 second is 5 Third is 4
18 employees
first page is 8 second is 5 Third is 5
19 employees
first page is 8 second is 6 Third is 5
20 employees
first page is 8 second is 6 Third is 6

I know it's very stupid, but according to their thoughts, it's so excellent because they can see nice reports with no big gaps.

 

[vbaInet]

What about 40 employees. How will that look?

 

[history]

What about 40 employees. How will that look?

It will be 5 pages with 8 records in each.

simply they need maximum 8 records in each page and minimum 5

 

[DCrake]

The problem here is what happens if you actually acheive this solution? They are going to expect you to do hte next quirky thing they can think of. You need to tell them that what they hae ased for is not technically feasable and stand your ground. You are making a rod for your own back.

 

[history]

The problem here is what happens if you actually acheive this solution? They are going to expect you to do hte next quirky thing they can think of. You need to tell them that what they hae ased for is not technically feasable and stand your ground. You are making a rod for your own back.

may be you're telling the correct, but they were doing this before on MS Word, as you know it's normal in MS word and I'm still new at this firm where I moved to it since 3 months ago ... I told them that's a little bit hard to achieve but no way, their minds more solid than flint.
Now they need the same in MS Access, any way now I'm working by selecting the people according to their view ... I know it's foolish way but I've no other way at the moment until I get some solution.

 

[vbaInet]

Word and Access are two different tools. What you can do easily in Word sometimes is impossible to achieve in Access. It was your mistake to tell them it is "a little bit" difficult.

Anyway, what you mentioned in post #9 doesn't correlate with what you were explaining with 17, 18, 19 and 20 employees. Why is the second page in those ones 5 or 6 and the rest goes to the third? Why aren't they (i.e. the second page) just 8 and the rest go to the third page?

 

[history]

Word and Access are two different tools. What you can do easily in Word sometimes is impossible to achieve in Access. It was your mistake to tell them it is "a little bit" difficult.

Anyway, what you mentioned in post #9 doesn't correlate with what you were explaining with 17, 18, 19 and 20 employees. Why is the second page in those ones 5 or 6 and the rest goes to the third? Why aren't they (i.e. the second page) just 8 and the rest go to the third page?

You're right, it was my mistake to tell that may be solved in MS Access but because I'm not expert in Database Programming I thought that may be some solution can be.

They need maximum 8 records in each page and if exceed 8 then it should be not less than 5 in each page.
So if 17 : then 8 will be in the first page, the remain are 9 so it couldn't be in one page so in normal cases it will take another 8 records in the second page and 1 record will be in the third ; their vision is to put 5 records in the second page and 4 records to the third.
Look may be it's difficult to understand or to imagine

 

[vbaInet]

Well, I have to say you're not consistent here. Look at your example for 16, 19 and 20 employees. The second page isn't 5 but 6. How is that?

 

[history]

Well, I have to say you're not consistent here. Look at your example for 16, 19 and 20 employees. The second page isn't 5 but 6. How is that?

I told you that's difficult to imagine, I'm sure you had some confusion

19 - 20

[B]19 employees [/B]
 first page is 8 second is 6 Third is 5
[B]20 employees [/B]
  first page is 8 second is 6 Third is 6

the page should not has less than 5
the second page has 6 to let the third page reach the desired number (5) or not to be less than it.
In case 19:
A- first page is 8 the second 8 the third will be 3 ... not accepted
B- first page is 8 the second 7 the third will be 4 ... not accepted
C- first page is 8 the second 6 the third will be 5 ... accepted

hope that I could explained their view

 

[vbaInet]

Right, so 17 employees still doesn't make sense. The third page is 4 which is obviously less than 5. If the third page can be less than 5 in this case only, why can't the second page be 8 and the third be 1? I'm talking about just for 17 employees.

 

[history]

Right, so 17 employees still doesn't make sense. The third page is 4 which is obviously less than 5. If the third page can be less than 5 in this case only, why can't the second page be 8 and the third be 1? I'm talking about just for 17 employees.

You're very right.
But no way, we're forced to keep 4 records because what we can do ??
for example : the current procedure is for 17 employees !!!
it must be kept as it is

 

[vbaInet]

Here you go. I'm sure you can work out which bit goes where:

Option Compare Database
Option Explicit

Private pgNumbers() As Byte, runCount As Integer
Public pgNum As Byte


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        runCount = runCount + 1
        If runCount = pgNumbers(Me.Page) Then
            Me.Detail.ForceNewPage = 1
        Else
            Me.Detail.ForceNewPage = 0
        End If
        
        ' If you want a running count per page, you can set it here. The runCount variable returns this. _
          In the example below I have called the textbox for the running count txtCounter
        txtCounter = runCount
    End If
End Sub


Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        runCount = 0
    End If
End Sub


Public Function GetPageNums() As Byte
    ' This will get you the correct Number of Pages. Use this function in place of [Pages]
    GetPageNums = pgNum
End Function


Private Sub Report_Open(Cancel As Integer)
    Dim intCountAll As Integer, isSolved As Boolean, counter As Integer
    Dim intNext As Integer
    
    ' If your report's record source is based on an SQL SELECT clause, you must put it in a query. _
      An SQL SELECT clause won't work in a DCount() function.
    intCountAll = DCount("*", "NameOfQuery")

    ReDim pgNumbers(1 To ((intCountAll / 5) + 3))
    pgNum = 1
    
    Do While isSolved = False And counter < 100
        counter = counter + 1
            
        Select Case intCountAll
            Case Is <= 8
                pgNumbers(pgNum) = intCountAll
                isSolved = True
                
            Case 16
                pgNumbers(pgNum) = 8
                pgNumbers(pgNum + 1) = 8
                pgNum = pgNum + 1
                isSolved = True
                
            Case 9 To 15
                intNext = intCountAll - 8
                
                If intNext < 5 Then
                    intNext = intCountAll - 5
                    If intNext > 5 Then
                        pgNumbers(pgNum) = intNext
                        pgNumbers(pgNum + 1) = 5
                    ElseIf intNext < 5 Then
                        pgNumbers(pgNum) = 5
                        pgNumbers(pgNum + 1) = intNext
                    Else
                        pgNumbers(pgNum) = 5
                        pgNumbers(pgNum + 1) = 5
                    End If
                Else
                    pgNumbers(pgNum) = 8
                    pgNumbers(pgNum + 1) = intNext
                End If
                pgNum = pgNum + 1
                isSolved = True
                
            Case Else
                pgNumbers(pgNum) = 8
                intCountAll = intCountAll - 8
                pgNum = pgNum + 1
                
        End Select
    Loop
End Sub

 

[history]

Hello again my friend vbaInet

Thank you for taking care, I'm so much appreciated for your effort to assist me

** regarding the posted code, I've something wrong, I couldn't figure out.
It gives error, I'm sure I didn't name all the needed objects correctly

I'm still here with you

 

[vbaInet]

What is the error message and what line does it show the error?

Remove the Page Break control from before. You don't need it.