Column up to 4 in a Report

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hello to everybody.
using the same report how is possible to show the datas (because different
datas need to be showed in different ways) in one, two, three, or four
column.
Should I pass a parameter to a query and then few lines of VBA?
Can you please help me? If possible I would like to avoid to build 4
different report where the difference is only the number of column but I
haven't any clue.

Thanks for your help.
Regards
John
 
Your are right.
Let me try to explain in this way.
The data on each report are the same but some of them needs to be in two
columns:, because they represent a niche of cemetery, virtually based on 2
columns and 4 rows, like:

| Name 1 | Name 2 |
| Name 3 | Name 4 |
| Name 5 | Name 6 |
| Name 7 | Name 8 |

Others niches are based like a table with 4 columns and 5 rows:
| Name 01 | Name 02 | Name 03 | Name 04 |
| Name 05 | Name 06 | Name 07 | Name 08 |
| Name 09 | Name 10 | Name 11 | Name 12 |
| Name 13 | Name 14 | Name 15 | Name 16 |
| Name 17 | Name 18 | Name 19 | Name 20 |

Now I am able, using the columns option on page layout to show correctly the
data in 4 col and 5 rows.
But I use the same report for 2 x 4 layout I obtain something like:
| Name 01 | Name 02 | Name 03 | Name 04 |
| Name 05 | Name 06 |

The question is: is there any possibilities to use the same report, but
obtain the result as 2x4 or 4 x5 layout without?
In the meantime I'll check the link kindly provided from you.
Anyway, many thanks for your help and support.
Regards
John
 
The link I suggested should allow you to create your report with different
numbers of columns in each group.
 
Hi again,
I checked the link and it is OK for 2 col.
However since my knowledge of VBA is almost null, would you be so kind to
tell where and what value I should change to obtain 3 or 4 col.

Thanks again for your support.
John
 
Assuming:
You have a group header for the Niche with a text box:
Name: txtGroupCount
Control Source: =Count(*)

You have a text box in the detail section:
Name: txtRunningCount
Control Source: =1
Running Sum: Over Group

You have code in your report of:
Option Compare Database
Option Explicit
Dim bytColumn As Byte
Dim lngColumnWidth As Long
Dim booNewRow As Boolean
Dim bytNumOfColumns As Byte

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Section(0).Controls
ctl.Left = bytColumn * lngColumnWidth + Val(ctl.Tag)
Next

If Me.txtGroupCount = Me.txtRunningCount Or _
bytColumn = bytNumOfColumns - 1 Then
bytColumn = 0
booNewRow = True
Else
bytColumn = bytColumn + 1
booNewRow = False
End If

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.MoveLayout = booNewRow
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtGroupCount = 8 Then
bytNumOfColumns = 2
Else
bytNumOfColumns = 4
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim ctl As Control
booNewRow = False
bytColumn = 0
'store the original position in the tag property
For Each ctl In Me.Section(0).Controls
ctl.Tag = ctl.Left
Next
Set ctl = Nothing
'you may need to change this next value from 2000
lngColumnWidth = 2000
End Sub
 
Back
Top