How to Manipulate the group and sorting in a report?

  • Thread starter Thread starter Jorge Novoa
  • Start date Start date
J

Jorge Novoa

Hello everyone!

I'm Using Access XP, with a .MDB file

I want to create some reports, with filters that the user can select the
sorting and grouping in a form.
I mean, suppose a form with the following options to the user:
Group by:
User
Client
Task
Subtotals o primary sort? (checkbox)
Subtotals o secondary sort? (checkbox)



They will be able to select:
1- Group only by one user
2- Group by User AND Client
3- and so on...

In order to get a report like this for the first case:

User Nº1
records
records
...
records
SubTotal of User Nº1:[Subtotal1]

User Nº2
records
records
...
records
SubTotal of User Nº2: [Subtotal2]
Grant Total: [Total]


Or in the second case:

User: User Nº1
Client A
records
records
...
records
Subtotal of Client A

Client B
records
records
...
records
Subtotal of Client B

SubTotal of User Nº1

User: User Nº1
Client A
records
records
...
records
Subtotal of Client A

Client B
records
records
...
records
Subtotal of Client B

Client T
records
records
...
records
Subtotal of Client T

Grant Total: [Total]


I know well this can be done, but I want to setup all these grouping and
sorting with VBA using one or two reports, I don't know if this is possible,
if so please help me.. if not...I'll make as many reports as it takes, and
at least I'd like to know if there's a way to handle values for this report
properties¿?.

Maybe it can be done with one single report and Hide/Show some parts of it.
¿?



Thanx!

Be well guys!
 
You cannot create group levels on the fly (without using design mode), but
you can create as many group levels as you need, and assign their
ControlSource in Report_Open. If you end up needing only one group-level,
set the others to the same field. The assignments are made with:
Me.GroupLevel(i).ControlSource = "SomeField"

Your example involves multiple group levels, with indentation. This will
involved hiding some of the text boxes or even entire sections (group level
and group footer). You may also need to assign the Left property (in twips)
to get the boxes in the desired places, and even assign the ControlSource of
the text boxes themselves to match the group levels. All this has to occur
in Report_Open.

HTH
 
Very usefull information

Thanx a lot!

--
________________
Jorge Novoa
F.A. Arias & Muñoz
El Salvador, C.A.


Allen Browne said:
You cannot create group levels on the fly (without using design mode), but
you can create as many group levels as you need, and assign their
ControlSource in Report_Open. If you end up needing only one group-level,
set the others to the same field. The assignments are made with:
Me.GroupLevel(i).ControlSource = "SomeField"

Your example involves multiple group levels, with indentation. This will
involved hiding some of the text boxes or even entire sections (group level
and group footer). You may also need to assign the Left property (in twips)
to get the boxes in the desired places, and even assign the ControlSource of
the text boxes themselves to match the group levels. All this has to occur
in Report_Open.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jorge Novoa said:
Hello everyone!

I'm Using Access XP, with a .MDB file

I want to create some reports, with filters that the user can select the
sorting and grouping in a form.
I mean, suppose a form with the following options to the user:
Group by:
User
Client
Task
Subtotals o primary sort? (checkbox)
Subtotals o secondary sort? (checkbox)



They will be able to select:
1- Group only by one user
2- Group by User AND Client
3- and so on...

In order to get a report like this for the first case:

User N:1
records
records
...
records
SubTotal of User N:1:[Subtotal1]

User N:2
records
records
...
records
SubTotal of User N:2: [Subtotal2]
Grant Total: [Total]


Or in the second case:

User: User N:1
Client A
records
records
...
records
Subtotal of Client A

Client B
records
records
...
records
Subtotal of Client B

SubTotal of User N:1

User: User N:1
Client A
records
records
...
records
Subtotal of Client A

Client B
records
records
...
records
Subtotal of Client B

Client T
records
records
...
records
Subtotal of Client T

Grant Total: [Total]


I know well this can be done, but I want to setup all these grouping and
sorting with VBA using one or two reports, I don't know if this is possible,
if so please help me.. if not...I'll make as many reports as it takes, and
at least I'd like to know if there's a way to handle values for this report
properties??.

Maybe it can be done with one single report and Hide/Show some parts of it.
??



Thanx!

Be well guys!

--
________________
Jorge Novoa
F.A. Arias & Muqoz
El Salvador, C.A.
Click to expand...
Click to expand...
 
Back
Top