Change Group Level order on Access Report from selection made on form

[heidii]

Hello All:

I have no idea how to do this, but here is what my mind has dreamed
up.

I have a form with many combo boxes setup for filtering my report.

My users can select from the combo boxes what they want their search
criteria to be and hit the command button to open the report with
those parameters. This works great.

I want to add to my form a couple check boxes or dropdowns next to
three of the combo boxes that allow the users to choose what order to
group these on my report.

My report already has the particular three text boxes grouped. But
some users might like them in a different order when they view the
report.

Is there a way that the users could select from a combo box and choose
say, 1, 2, or 3 changing the group order with code to my report when
it opens?

thanks much

Heidi

 

[Duane Hookom]

Allen Browne has code at his web site http://www.allenbrowne.com/ser-33.html

 

[heidii]

Thanks.

Another question.

How do I turn certain groups already set up to not be visible if the
user chooses by check box not to group that category?

Thanks

 

[Duane Hookom]

You could include code in the On Format of any report section like:
Cancel = (Me.chkHideLevel3 = True)

 

[Marshall Barton]

How do I turn certain groups already set up to not be visible if the
user chooses by check box not to group that category?

You can "disable" a group level by setting its control
source to a constant expression and making the header/footer
invisible.

 

[heidii]

Sorry this has taken so long for me to get back.

I have been successful in making the groups header or footer
invisible. But I have not been successful in making the group not
group with the constant variable.

Example:

me.grouplevel(3).controlsource = "-1"

or

Me.grouplevel(3).controlsource = -1

Even if I place this code in the on open even of the report, the
report still groups the products by the grouping i tried to make
cancel and invisible.

Here is what I have:

Report:
Group1 = Ranch Area
Group2 = WSDA Cert Number
Group3 = Lot Number

Group4 = Products

Group 4 will never change. Will always be visible and always in
position #4
Groups 1 -3 will be canceled or invisilbe depending on check boxes
selected in open form.
How can I make it so that If the user checks that he wants to group by
Group Level 2 and not #1 or #3 that the report only lists the products
for Group2 not the others as well even those they are invisible.

Help.

Thanks

 

[Marshall Barton]

Sorry this has taken so long for me to get back.

I have been successful in making the groups header or footer
invisible. But I have not been successful in making the group not
group with the constant variable.

Example:

me.grouplevel(3).controlsource = "-1"

or

Me.grouplevel(3).controlsource = -1

Even if I place this code in the on open even of the report, the
report still groups the products by the grouping i tried to make
cancel and invisible.

Here is what I have:

Report:
Group1 = Ranch Area
Group2 = WSDA Cert Number
Group3 = Lot Number

Group4 = Products

Group 4 will never change. Will always be visible and always in
position #4
Groups 1 -3 will be canceled or invisilbe depending on check boxes
selected in open form.
How can I make it so that If the user checks that he wants to group by
Group Level 2 and not #1 or #3 that the report only lists the products
for Group2 not the others as well even those they are invisible.

The "trick" is that a control source **expression** must be
preceeded by an = sign.

I think your code should look like:

If Not Forms!yourform.checkbox1 Then
Me.GroupLevel(0).ControlSource = "=1"
Me.Section(5).Visible = False
Me.Section(6).Visible = False
End If
If Not Forms!yourform.checkbox2 Then
Me.GroupLevel(1).ControlSource = "=1"
Me.Section(7).Visible = False
Me.Section(8).Visible = False
End If
If Not Forms!yourform.checkbox3 Then
Me.GroupLevel(2).ControlSource = "=1"
Me.Section(9).Visible = False
Me.Section(10).Visible = False
End If

 

[heidii]

I don't understand what the extra numbered sections are for. I don't
have that many sections?

Heidi

 

[Duane Hookom]

Yoiu should be able to take code posted here and modify it to fit your needs.
Once you try that, report back with your success or lack of. If it doesn't
work, tell us exactly what you did as well as your results.

 

[Marshall Barton]

I don't understand what the extra numbered sections are for. I don't
have that many sections?

So leave out the lines of code for the header/footer
sections that you don't have.

 

[heidii]

Here is what I did. works great. thanks everyone.

If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]![C] Then
Me.GroupLevel(1).ControlSource = "=-1"

Me.commodityheader.Visible = False
End If
If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]! Then
Me.GroupLevel(2).ControlSource = "=-1"

Me.blocknameheader.Visible = False
End If
If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]! Then
Me.GroupLevel(3).ControlSource = "=-1"

Me.STATUSHEADER.Visible = False
End If

If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]![V] Then
Me.GroupLevel(4).ControlSource = "=-1"

Me.varietyheader.Visible = False

End If

 

[Marshall Barton]

Here is what I did. works great. thanks everyone.

If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]![C] Then
Me.GroupLevel(1).ControlSource = "=-1"

Me.commodityheader.Visible = False
End If
If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]! Then
Me.GroupLevel(2).ControlSource = "=-1"

Me.blocknameheader.Visible = False
End If
If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]! Then
Me.GroupLevel(3).ControlSource = "=-1"

Me.STATUSHEADER.Visible = False
End If

If Not [Forms]![FRM-PRODUCT_USAGE_SEARCH_SUMMARY]![V] Then
Me.GroupLevel(4).ControlSource = "=-1"

Me.varietyheader.Visible = False

End If

Glad it works for you.

If you had explained that you had 5 levels of grouping we
could have got there more quickly. (The first group level
is number 0) Be sure to check VBA Help on "GroupLevel
Object" for details.

While your in VBA Help, check the "Section Property" for
other ways to refer to header amd footer sections.