Dynamic grouping not working

[Barry]

I have a form with two comboboxes populated with the names of the
fields in a report's underlying query. The report picks up these
selections and sets the GroupLevels when it opens. This is pretty
standard stuff.

However, I can't get the GroupLevels to appear in the correct order;
they seem to be stuck in the order they are in in the Sorting/Grouping
dialog.

Here's my code:
Private Sub Report_Open(Cancel As Integer)
Dim strGroupBy1 As String
Dim strGroupBy2 As String
With Forms!frmPrintReports
If Not IsNull(.cboGroupBy1) Then
strGroupBy1 = .cboGroupBy1
Else
strGroupBy1 = "=1"
End If
If Not IsNull(.cboGroupBy2) Then
strGroupBy2 = .cboGroupBy2
Else
strGroupBy2 = "=1"
End If
End With
With Me
.GroupLevel(0).ControlSource = strGroupBy1
.GroupLevel(1).ControlSource = strGroupBy2
.GroupLevel(2).ControlSource = "=1"
.GroupLevel(3).ControlSource = "=1"
.GroupLevel(4).ControlSource = "=1"
.GroupLevel(5).ControlSource = "=1"

If strGroupBy1 <> "=1" Then
.Section(strGroupBy1 & "Header").Visible = True
.Section(strGroupBy1 & "Footer").Visible = True
End If
If strGroupBy2 <> "=1" Then
.Section(strGroupBy2 & "Header").Visible = True
End If
End With
End Sub

Any help would be appreciated.

 

[Marshall Barton]

I have a form with two comboboxes populated with the names of the
fields in a report's underlying query. The report picks up these
selections and sets the GroupLevels when it opens. This is pretty
standard stuff.

However, I can't get the GroupLevels to appear in the correct order;
they seem to be stuck in the order they are in in the Sorting/Grouping
dialog.

Here's my code:
Private Sub Report_Open(Cancel As Integer)
Dim strGroupBy1 As String
Dim strGroupBy2 As String
With Forms!frmPrintReports
If Not IsNull(.cboGroupBy1) Then
strGroupBy1 = .cboGroupBy1
Else
strGroupBy1 = "=1"
End If
If Not IsNull(.cboGroupBy2) Then
strGroupBy2 = .cboGroupBy2
Else
strGroupBy2 = "=1"
End If
End With
With Me
.GroupLevel(0).ControlSource = strGroupBy1
.GroupLevel(1).ControlSource = strGroupBy2
.GroupLevel(2).ControlSource = "=1"
.GroupLevel(3).ControlSource = "=1"
.GroupLevel(4).ControlSource = "=1"
.GroupLevel(5).ControlSource = "=1"

If strGroupBy1 <> "=1" Then
.Section(strGroupBy1 & "Header").Visible = True
.Section(strGroupBy1 & "Footer").Visible = True
End If
If strGroupBy2 <> "=1" Then
.Section(strGroupBy2 & "Header").Visible = True
End If
End With
End Sub

I don't understand what the issue is. The group levels are
definitely predefined as group level 0, group level 1, etc.

Could you expand on "can't get the GroupLevels to appear in
the correct order"

 

[Barry]

I don't understand what the issue is. The group levels are
definitely predefined as group level 0, group level 1, etc.

Could you expand on "can't get the GroupLevels to appear in
the correct order"

In the Sorting/Grouping UI, I have levels set in no particular order.
For example, I have
Employee
Product
Shift
ProcessDate

In the Report_Open event, I use code to grab values from the combo
boxes to choose which two of these four groups to use, in order of how
they are selected in the combo boxes (e.g., ProcessDate in Combo1,
Employee in Combo2 means I want ProcessDate to be group 0 and Employee
to be group 1). However, when the report renders, the group levels
render in the order in which they appear in the UI, in this case
0-Employee, 1-ProcessDate, even though I've assigned them in the
reverse order.

I hope this is more clear.
Barry

 

[Marshall Barton]

In the Sorting/Grouping UI, I have levels set in no particular order.
For example, I have
Employee
Product
Shift
ProcessDate

In the Report_Open event, I use code to grab values from the combo
boxes to choose which two of these four groups to use, in order of how
they are selected in the combo boxes (e.g., ProcessDate in Combo1,
Employee in Combo2 means I want ProcessDate to be group 0 and Employee
to be group 1). However, when the report renders, the group levels
render in the order in which they appear in the UI, in this case
0-Employee, 1-ProcessDate, even though I've assigned them in the
reverse order.

I give up. I looked at this for a long time and can not
find anything wrong with your code or anything else you've
described. I've done the same kind of thing many times
without ever running into a problem like you're seeing.

This isn't in a subreport is it? You should be geting a
error if it were, but I'm trying to cover all the bases.

Assuming I haven't missed something, I guess I would start
to suspect some kind of coruption???

 

[Guest]

Marshall

Thanks very much for your effort.

This is not a subreport

Based on your suggestion, I did try to import all my objects into a new report. I get the same result.

I've been pulling my hair out over this for several days now. It seems like it should be fairly straightforward.

I'm not too sure I understand the connection between the grouplevels and their respective headers and footers; how do they retain their connection to each other? Is there something about the fact that I've renamed my headers and footers? I would assume that Access would give me an error if it couldn't find a particular header or footer or grouplevel.

Why can't I get those damn straws? I've been grasping at them all week
Barr

----- Marshall Barton wrote: ----

Barry wrote

For example, I hav
Employe
Produc
Shif
ProcessDat
boxes to choose which two of these four groups to use, in order of ho
they are selected in the combo boxes (e.g., ProcessDate in Combo1
Employee in Combo2 means I want ProcessDate to be group 0 and Employe
to be group 1). However, when the report renders, the group level
render in the order in which they appear in the UI, in this cas
0-Employee, 1-ProcessDate, even though I've assigned them in th
reverse order

I give up. I looked at this for a long time and can no
find anything wrong with your code or anything else you'v
described. I've done the same kind of thing many time
without ever running into a problem like you're seeing

This isn't in a subreport is it? You should be geting
error if it were, but I'm trying to cover all the bases

Assuming I haven't missed something, I guess I would star
to suspect some kind of coruption??

 

[Marshall Barton]

I'm not too sure I understand the connection between the grouplevels and their respective headers and footers; how do they retain their connection to each other? Is there something about the fact that I've renamed my headers and footers? I would assume that Access would give me an error if it couldn't find a particular header or footer or grouplevel.

Wait a minute here, I think I've been reading your question
all wrong. It could be that the issue is that the group
LEVELS are correct as specified in your code, but the group
HEADER (and footer) are still displaying their original
bound field values?

If I've finally got the problem in hand, then the answer is
straightforward. The controls in the header sections are
bound to whatever fields you specified in design view, but
when you change the group level, you should also change
(some?) of the header/footer controls' ControlSource to
agree. For example:
. . .
If strGroupBy1 <> "=1" Then
.Section(strGroupBy1 & "Header").Visible = True
.txtheader1textbox.ControlSource = strGroupBy1
.Section(strGroupBy1 & "Footer").Visible = True
End If
If strGroupBy2 <> "=1" Then
.Section(strGroupBy2 & "Header").Visible = True
.txtheader2textbox.ControlSource = strGroupBy2
End If
. . .

There may be other controls that need to be set, but I don't
know what you have in the headers/footers so I'd just be
guessing.

 

[Guest]

Ok. I think I get it now. I have a group defined in the UI as, let's say, "Shift, with a header and a footer. If I reassign the control source of this at runtime to "Employee" and set the ShiftHeader's visible to True, I'm not really working on the same headers? Now the ShiftHeader section has a control source of "Employee"

It seems like the solution is to (rather than creating a group level and sections for every possible grouping), create only as many groups/headers as I expect to ever display, in this case two. Then I need to create generic text boxes and change their control sources dynamically as well

Sorry it took me so long to get this. I'll give a whirl

Thanks a bunch for your help
Barr

----- Marshall Barton wrote: ----

Barry wrote

I'm not too sure I understand the connection between the grouplevels and their respective headers and footers; how do they retain their connection to each other? Is there something about the fact that I've renamed my headers and footers? I would assume that Access would give me an error if it couldn't find a particular header or footer or grouplevel.

Wait a minute here, I think I've been reading your questio
all wrong. It could be that the issue is that the grou
LEVELS are correct as specified in your code, but the grou
HEADER (and footer) are still displaying their origina
bound field values

If I've finally got the problem in hand, then the answer i
straightforward. The controls in the header sections ar
bound to whatever fields you specified in design view, bu
when you change the group level, you should also chang
(some?) of the header/footer controls' ControlSource t
agree. For example
. .
If strGroupBy1 <> "=1" The
.Section(strGroupBy1 & "Header").Visible = Tru
.txtheader1textbox.ControlSource = strGroupBy
.Section(strGroupBy1 & "Footer").Visible = Tru
End I
If strGroupBy2 <> "=1" The
.Section(strGroupBy2 & "Header").Visible = Tru
.txtheader2textbox.ControlSource = strGroupBy
End I
. .

There may be other controls that need to be set, but I don'
know what you have in the headers/footers so I'd just b
guessing

 

[Marshall Barton]

Ok. I think I get it now. I have a group defined in the UI as, let's say, "Shift, with a header and a footer. If I reassign the control source of this at runtime to "Employee" and set the ShiftHeader's visible to True, I'm not really working on the same headers? Now the ShiftHeader section has a control source of "Employee".

It seems like the solution is to (rather than creating a group level and sections for every possible grouping), create only as many groups/headers as I expect to ever display, in this case two. Then I need to create generic text boxes and change their control sources dynamically as well.

That's a nice summary of the way to do this kind of thing.

Sometimes, you might have to construct a control source
expression (in a group footer). Let's say you want to total
the number of hours in the shift footer but otherwise you
just want a count of the items in the group:

If strGroupBy2 = "Shift" Then
.txtTotal.ControlSource = "=Sum(Hours)"
Else
.txtTotal.ControlSource = "=Count(*)"
End If
--
Marsh
MVP [MS Access]

----- Marshall Barton wrote: -----

I'm not too sure I understand the connection between the grouplevels and their respective headers and footers; how do they retain their connection to each other? Is there something about the fact that I've renamed my headers and footers? I would assume that Access would give me an error if it couldn't find a particular header or footer or grouplevel.

Wait a minute here, I think I've been reading your question
all wrong. It could be that the issue is that the group
LEVELS are correct as specified in your code, but the group
HEADER (and footer) are still displaying their original
bound field values?

If I've finally got the problem in hand, then the answer is
straightforward. The controls in the header sections are
bound to whatever fields you specified in design view, but
when you change the group level, you should also change
(some?) of the header/footer controls' ControlSource to
agree. For example:
. . .
If strGroupBy1 <> "=1" Then
.Section(strGroupBy1 & "Header").Visible = True
.txtheader1textbox.ControlSource = strGroupBy1
.Section(strGroupBy1 & "Footer").Visible = True
End If
If strGroupBy2 <> "=1" Then
.Section(strGroupBy2 & "Header").Visible = True
.txtheader2textbox.ControlSource = strGroupBy2
End If
. . .

There may be other controls that need to be set, but I don't
know what you have in the headers/footers so I'd just be
guessing.
--
Marsh
MVP [MS Access]

I give up. I looked at this for a long time and can not
find anything wrong with your code or anything else you've
described. I've done the same kind of thing many times
without ever running into a problem like you're seeing.
error if it were, but I'm trying to cover all the bases.
to suspect some kind of coruption???