Printing static number of data per page, regardless of actual record n

[foxrainer]

I really need help with this, it is a recurrent problem with many reports I need to generate:

Example ( not what I need but same idea for simplicity):
Regular sized page should be filled with a line every inch with labels of: 'Name'.... 'DOB'.... 'Attitude'....

Each Report should be grouped by 'Attitude'

Each printed page should have 5 lines per page, even if there are less than 5 records satisfying the grouping.

Report should look like this:

'Name'Joe 'DOB'Shmo 'Attitude'good
'Name'Mary 'DOB'shmo 'Attitude'OK
'Name'Dan 'DOB'Smith 'Attitude'Bad
'Name'.... 'DOB'.... 'Attitude'....
'Name'.... 'DOB'.... 'Attitude'....

I could see two solutions, but don't know how to do either:
1) Have a query 'add' blank records to satisfy the need for record totals being a sum of 5
2) Placing the lines and labels on the report layout with 5 lines and using a sub_report to add the information - I tried this, but the Sub-Report will 'Displace' the underlying lines downward.

Any ideas, I could really use help on this!

Thanks in advance

Rainer

 

[dhookom]

I would remove the 'Name', 'DOB', and 'Attitude' from your report as controls and use code to print them on your page. The following code is an example that draws 30 lines and prints a consecutive number on each page.

'---------------------------------
' Procedure : Report_Page
' DateTime  : 8/9/2004 14:13
' Author    : hookomd
' Purpose   :
'---------------------------------
'
Private Sub Report_Page()
    Dim intNumLines As Integer
    Dim intLineNum As Integer
    Dim intDetailHeight As Integer
    Dim intPageHeadHeight As Integer
   On Error GoTo Report_Page_Error

    intNumLines = 30
    intDetailHeight = Me.Section(acDetail).Height
    intPageHeadHeight = Me.Section(3).Height
    For intLineNum = 1 To intNumLines
        Me.CurrentY = (intLineNum - 1) * intDetailHeight + intPageHeadHeight
        Me.CurrentX = 0
        Me.FontBold = True
        Me.FontSize = 14
        Me.Print intLineNum  'print the line number
        Me.Line (0, intPageHeadHeight + intLineNum * intDetailHeight)- _
            Step(Me.Width, 0)
    Next

   On Error GoTo 0
   Exit Sub

Report_Page_Error:
    Select Case Err
        Case 2462  'no page header
            intPageHeadHeight = 0
            Resume Next
    End Select
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure Report_Page of VBA Document Report_Report1"
    
End Sub

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[foxrainer]

Duane,

thank you very much for your reply, unfortunately, it will not work for me. The 'background' actually is much more involved, and looks more like (even with more detail than that):

_______________________________________
Field 1| Field 2 | | | | | | | | | | | |
_______________________________________
Field 1| Field 2 | | | | | | | | | | | |
_______________________________________
Field 1| Field 2 | | | | | | | | | | | |

More importantly, the data to be printed isn't anything consecutive: I have a database with medication information. the record data needs to be printed in the field area, but may only be 1 or two records each page, however, lines having to be for a three record page.

Could you help me with that?

(R)

 

[dhookom]

The code that I suggested prints any number of copies of the lines/rectangles and text.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[CSADataCruncher]

Duane,

I am having a similiar problem. So, I tried your code and (of course) it works. However, in my case it only gave me a partial solution.

I'm trying to create a checklist for my husband's baseball card collection. Actually, he already has all the data for the checklist, but in the report we want to have the extra lines so there can be additional cards handwritten. But, we don't just want lines, we want a table format so we need 3 vertical lines duplicated as well as the horizontal line.

Plus, with your code, it's adding numbers to my lines which I don't need.

Can you help me?
CSADataCruncher

 

[dhookom]

Either remove or comment out this line

       Me.Print intLineNum  'print the line number

Then add some code to loop through printing the vertical lines.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CSADataCruncher]

Sorry.... I tried sending a note a few minutes ago but my computer fritzed.

I did get rid of the line numbers... what do you mean add some code? See, that's my problem... I'm not very good at code creation. I've been looking in my books for information on modules and VBA but haven't reached a solution.

If you could give me just a little more help I'd be ever so grateful. The vertical lines I need would be at the left and right of my detail (or end of the line) and then about 1 1/4 inch in from the left.

Thank you for the help you've already given me (we are SO excited!) and thank you, in advance, for any further assistance you can provide.

Peggy
CSADataCruncher

 

[dhookom]

Add a rectangle control to your page header section and align its left side with the position of your middle line. Name the rectangle "BoxMarker" and maybe set it to invisible. You can move the rectangle to move the middle vertical line.

Private Sub Report_Page()
'---------------------------------
' Procedure : Report_Page
' DateTime  : 8/9/2004 14:13
' Author    : hookomd
' Purpose   :
'---------------------------------
'

    Dim intNumLines As Integer
    Dim intLineNum As Integer
    Dim intDetailHeight As Integer
    Dim intPageHeadHeight As Integer
   On Error GoTo Report_Page_Error

    intNumLines = 30
    intDetailHeight = Me.Section(acDetail).Height
    intPageHeadHeight = Me.Section(3).Height
    For intLineNum = 1 To intNumLines
        Me.Line (0, intPageHeadHeight + intLineNum * intDetailHeight)- _
            Step(Me.Width, 0)
    Next
    'draw vertical line on left
    Me.Line (0, 0)- _
            Step(0, 20000)
    'draw vertical line on right
    Me.Line (Me.Width, 0)- _
        Step(0, 20000)
    'draw vertical line on left control BoxMarker
    Me.Line (Me.BoxMarker.Left, 0)- _
        Step(0, 20000)
   On Error GoTo 0
   Exit Sub

Report_Page_Error:
    Select Case Err
        Case 2462  'no page header
            intPageHeadHeight = 0
            Resume Next
    End Select
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure Report_Page of VBA Document Report_Report1"
    
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CSADataCruncher]

OK.... we're getting closer. Now the vertical lines continue past the horizontal lines all the way through my page footer.

I wish I new how to attach a sample so I could show you what it looks like. The good thing is we are getting so close! )

I tried changing the line "Me.Line (0, 0)-" to read "Me.Line (0, 5)-" and I tried changing the 20000 to 5 but neither of these seemed to work (or am I just using too low of a number?

Thanks again! )
Peggy
CSADataCruncher

 

[CSADataCruncher]

O my... those smiley faces look stupid. Anyway.... I went ahead and tried playing with the 20000 number and it's working. So, I'll continue to tweak.

One last question... how do I change the boldness of the line? They seem to be hairline. I changed that ", 0) number again to 10 and noticed that the line got progressively bolder, but the vertical lines were still thin.

Sorry, as mentioned, I'm not much of a programmer. But, I really am appreciating all the help you've been giving me.

Peggy
CSADataCruncher

 

[CSADataCruncher]

Ooops... and one more thing... the box marker vertical line is going into the page header. I moved it to my group header (year) but it still starts at the page header and I found out I definitely don't want to mess with the "step (0" number... that gave me a real crooked line.

Sorry, to be such a pest.
Peggy

 

[dhookom]

The weight of the line is set with
Me.DrawWidth = 20
All measurements are in Twips. There are 1440 twips per inch. The numbers inside the ()s are the coordinates of starting and ending points.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CSADataCruncher]

YAY! I'm still not seeing where to change the starting point of the boxmarker line. When I change it from 0, it gives me a crooked/slanted line.

Here is what I've changed the code to so far:

For intLineNum = 1 To intNumLines
Me.DrawWidth = 30
Me.Line (0, intPageHeadHeight + intLineNum * intDetailHeight)- _
Step(Me.Width, 0)
Next
'draw vertical line on left
Me.Line (0, 0)- _
Step(0, 9420)
'draw vertical line on right
Me.Line (Me.Width, 0)- _
Step(0, 9420)
'draw vertical line on left control BoxMarker
Me.Line (Me.BoxMarker.Left, 0)- _
Step(0, 9420)

Thank you for explaining the twips. I hadn't heard of that before.

(sorry to take so long in replying... bad weather does things to my connection)

Peggy
CSADataCruncher

 

[dhookom]

How do you want to change the starting point of BoxMarker? Do you want it (and the other vertical lines) down the page? Or, do you want to move the line left or right?

To move the starting points down the page 1", use:

Me.Line (Me.BoxMarker.Left, 1440)-Step(0,9420-1440)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CSADataCruncher]

YES! YES! YES! You're the Man! Thank you for all your help. Yes, I just wanted to move that one line.

I was going to send you a sample but don't know how to do it (without taking up this whole box). Basically, we have a 2 column table with a single cell Title on top. So, the top box says "Cardinals: 1951" (for instance) and then underneath there is a box on the left to check things off or write pricing information then the box to the right has the card number and player name/card description.

We've had the checklists in the Excel format for over a year now, but wanted to move to a more uniform size and move his "database" to an actual database rather than keeping all the information in Excel. So, this whole line thing really had us stumped.

I wish I could do more to thank you for all your help today. You've really helped us over this puzzling hurdle.

Thank you again,
Peggy
CSADataCruncher
aka CSADataGirl

 

[dhookom]

Glad to hear you got the code working.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]