[AccessD] Access Reports rinting accross

Jim Lawrence accessd at shaw.ca
Sun Jul 8 14:13:24 CDT 2007


Hi A. D. Tejal:

A very eloquent solution. :-) A subreport of course can be used and I have
not ruled out that option yet...  One day Access reporting may provide the
ability to set columns per Group Heading. Until then this will have to be
the appropriate solution.

Thanks you
Jim 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Saturday, July 07, 2007 1:20 PM
To: Access Developers discussion and problem solving
Cc: A.D.TEJPAL
Subject: Re: [AccessD] Access Reports rinting accross

Jim, 

    If there is no objection to the use of multi-column subreport for
Section C, that should be most convenient.

    However, if there are compelling reasons to explore selective printing
of multiple records across each row, it can be done by manipulating the Left
property of relevant text boxes, simultaneously with report's MoveLayout
property.

    Sample code in report's module, as given below, will ensure printing of
three records on each row. The report has a group header as per field named
Account. Detail section has two controls named RefNum & ClientName
respectively. (Use of # within the names of fields/controls is not
preferred).

    When the report is opened, printing proceeds normally, till the value
"Referral" is detected in the group header. Thereafter, it starts printing
three records across each row. If another group value different from
"Referral" comes up, the printing will again revert back to normal style
(i.e. one record per row).

Best wishes,
A.D.Tejpal
---------------

Code in report's module
====================================
' Declaration Section
Dim RecNum As Long
Private RefLeft1 As Long, RefLeft2 As Long
Private RefLeft3 As Long, ClientLeft1 As Long
Private ClientLeft2 As Long, ClientLeft3 As Long
---------------------------------------------------------------

Private Sub Detail_Format(Cancel As Integer, _
                                FormatCount As Integer)
    Dim Cnt As Long
    
    If Me.Account <> "Referral" Then
        Exit Sub
    End If
    
    If RecNum > 0 Then
        Cnt = RecNum Mod 3
        Select Case Cnt
            Case 0
                Me.RefNum.Left = RefLeft3
                Me.ClientName.Left = ClientLeft3
            Case 2
                Me.RefNum.Left = RefLeft2
                Me.ClientName.Left = ClientLeft2
            Case Else
                Me.RefNum.Left = RefLeft1
                Me.ClientName.Left = ClientLeft1
        End Select
    Else
        Me.RefNum.Left = RefLeft1
        Me.ClientName.Left = ClientLeft1
    End If
End Sub
---------------------------------------------------------------

Private Sub Detail_Print(Cancel As Integer, _
                                    PrintCount As Integer)
    Dim Cnt As Long
    
    If Me.Account <> "Referral" Then
        Me.MoveLayout = True
        Exit Sub
    End If
    
    If RecNum > 0 Then
        Cnt = RecNum Mod 3
        If Cnt = 0 Then
            Me.MoveLayout = True
        Else
            Me.MoveLayout = False
        End If
    Else
        Me.MoveLayout = True
    End If
    
    RecNum = RecNum + 1
End Sub
---------------------------------------------------------------

Private Sub GroupHeader0_Format(Cancel _
                As Integer, FormatCount As Integer)
    ' Start the record counter with commencement
    ' of Referral account ( Set it only once. This is 
    ' to guard against unwanted re-setting if repeat
    ' section property of hroup header is set to Yes)
    If Me.Account = "Referral" Then
        RecNum = IIf(RecNum > 0, RecNum, 1)
    End If
End Sub
---------------------------------------------------------------

Private Sub ReportHeader_Format(Cancel _
                As Integer, FormatCount As Integer)
    RefLeft1 = Me.RefNum.Left
    ClientLeft1 = Me.ClientName.Left
    RefLeft2 = ClientLeft1 + Me.ClientName.Width + 50
    ClientLeft2 = ClientLeft1 + (RefLeft2 - RefLeft1)
    RefLeft3 = ClientLeft2 + Me.ClientName.Width + 50
    ClientLeft3 = ClientLeft2 + (RefLeft3 - RefLeft2)
End Sub
====================================

  ----- Original Message ----- 
  From: Jim Lawrence 
  To: 'Access Developers discussion and problem solving' 
  Sent: Saturday, July 07, 2007 21:31
  Subject: Re: [AccessD] Access Reports rinting accross


  Hi Rocky:

  These columns are fixed length. The wrinkle to the design requirements is
  that there are 3 individual sections within the report and the data source
  is in one table and it looks like as follows:

  Ref#, Client-Name, Account-Detail, Date, Payment

  The data is sorted so it group appropriately; Date, Client-Name etc.
  Programming within the report determines which section it is and how it
will
  be printed.
    
  The following is a text imitation of the report and how it will print:

  MY REPORT
  ---------
  Line 1: Section A: Acquired 
  Line 2: Ref#, Client-Name, Account-Detail, Date, Payment
  ...

  Line 11: Section B: Prospect
  Line 12: Account-Detail, Date, Payment
  Line 13: Account-Detail, Date, Payment
  ...

  Line 25: Section C: Referral 
  Line 26: Ref#, Client-Name
  Line 27: Ref#, Client-Name
  Line 28: Ref#, Client-Name
  Line 29: Ref#, Client-Name
  Line 30: Ref#, Client-Name
  Line 31: Ref#, Client-Name
  ...

  Section C could go on for pages but would be more compact if it looked
like:


  Line 26: Ref#, Client-Name, Ref#, Client-Name, Ref#, Client-Name
  Line 27: Ref#, Client-Name, Ref#, Client-Name, Ref#, Client-Name
  ...

  The Report would have to be able to something as follows. Within program
  control the report would have to pause while continuing to step through
the
  data, accumulating the records in an array and when 3 records were
gathered
  or the end of that section was reached, it would release the pause, print
  out and continue... I am sure it is doable... 

  Any suggestions would be greatly appreciated.

  TIA
  Jim 


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
  rockysmolin at bchacc.com
  Sent: Saturday, July 07, 2007 6:28 AM
  To: accessd at databaseadvisors.com
  Subject: Re: [AccessD] Access Reports rinting accross

  Are the records fixed length or variable? For variable, I'd use a temp
  table with one record, and concatenate the original records into temp
table records, using a fixed width font. Then bind the report to the temp
table.

  I suppose you could do the same for fixed length records as well.

  HTH

  Rocky


  Original Message:
  -----------------
  From: Jim Lawrence accessd at shaw.ca
  Date: Sat, 07 Jul 2007 00:53:17 -0700
  To: accessd at databaseadvisors.com
  Subject: [AccessD] Access Reports rinting accross

  Hi All:

  Is there a way to print across the page within an Access report? Like in
the following example:

  Record01 record02 record03 record4 record5
  Record06 record07 record08 record9 record10
  record11 record12 record13 record14 record15
  record16 record17 record18 record19 record20
  etc.........

  MTIA
  Jim  
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list