How To Use Form To Sort Report (Three Levels)

[doyle60]

I'm using the Code below to Sort a report with a form's controls. It
works except for the fact that the header repeats for GroupLevel(3)
when GroupLevel(1) changes. What should happen is GroupLevel(3)
should not repeat itself. That is, I am seeing:

Bloomingdales [GroupLevel(0)]
Daywear [GroupLevel(3)]
Bras [GroupLevel(1)]
Style1
Style2
Style3

Daywear [GroupLevel(3)] (I DON"T WANT THIS
REPEATED)
Briefs [GroupLevel(1)]
Style1
Style2
Style3

(The issue has nothing to do with the weird order of (0), (3), and (1)
and my Sort1, Sort2, Sort3 controls.)

Here is the code:
__________________________________________________________

Dim Dlg As Form
Set Dlg = Forms!OTSTranfrm
With Me
.GroupLevel(0).ControlSource = Dlg!Sort1
.GroupLevel(0).SortOrder = False
Sort1txt.ControlSource = Dlg!Sort1

.GroupLevel(3).ControlSource = Dlg!Sort2
.GroupLevel(3).SortOrder = False
Sort2txt.ControlSource = Dlg!Sort2

.GroupLevel(1).ControlSource = Dlg!Sort3
.GroupLevel(1).SortOrder = False
Sort3txt.ControlSource = Dlg!Sort3
End With
___________________________________________

Matt

 

[Marshall Barton]

There is no way you can get GroupLevel(3) to appear before
GroupLevel(1)

What happened to GroupLevel(2)?

If your group levels are as they should be, the only reason
I have ever heard of for that effect is when the field for
bras actually has a different value than the field for
briefs (extra space?).

 

[doyle60]

I believe the grouplevels are out of order because they are named in
the order that they are created. When you change the sequence in the
Sort and Grouping box, lifting one above the other, the names do not
change. The odd order of them should not have anything to do with
anything, I believe.

I am not trying to have one appear before another when it shouldn't.
I just don't what the middle one to be repeated on a change in the
third when it is the same thing.

As you suggest, it does appear as if there is some sort of difference,
like a missing space I am not seeing. But this is not the case. I
checked before writing.

It's only happening on the middle one and will happen when I change
the field to something else on the form. If I put the same field in
sort1, it does not repeat the name (as if it had the space, let's
say).

Also, the system does not allow me to leave the first three fields
blank in the sort and grouping window. I've done this before, and I
left the fields blank and it works. When I do it with this form it
tells me something needs to be there and so I put in a field that is
usually always the same. The code should override this choice and
does.

So that is an oddity I don't get either which may or may not be
connected to the issue.

But perhaps the most important point I can make is this:
When I don't use the code but put the same values in the sorting and
grouping window as on my form---"hard" coding it, if you will---the
middle sort does NOT repeat on a change in the thrid level (named
GroupLevel(1) in this case), it does what we would expect.

So the code is doing something that the "hard" method is not.

Thanks,

Matt

 

[ryguy7272]

This is how I learned to do it; check it out:
http://www.fontstuff.com/access/acctut19.htm

Download the .zip file at the bottom of the page for a sample.

HTH,
Ryan---

 

[Marshall Barton]

I think there is some kind of disconnect here. GroupLevel
is an array and does not have names. What you are saying
makes sense if you're talking about group header section
names??

Regardless of all that, I still think the grouping field
with DayWear is different in the briefs records. Or maybe
GroupLevel(2) is causing it.

 

[doyle60]

There are two facts that I can't let go:

1) When I have done this before, I have left the "Field/Expression" in
the Sorting and Grouping window blank. I get no error when I do so.
But on this report, I get an error stating I can't leave them blank.
So I field them in with the broadest field. The code works and
overrides this choice, but I get that strange repeat.

The error says: "You must define a sort field or expression for the
group header or footer in the report you tried to preview or print."

2) When I hard code the form to the choices in my combobox selections,
and "tick" out the code, greening it, the report runs correctly
without repeating the heading. It is most definately not the data,
believe me.

I do not get this error when I use only two levels. My other report
is actually a two-level situation. So this 3 level is new to me. But
even with two levels I still have the issue number 1 above on this
report.

I wonder if Access somehow cannot handle a three-level situation
properly.

Puzzled,

Matt

 

[Marshall Barton]

There are two facts that I can't let go:

1) When I have done this before, I have left the "Field/Expression" in
the Sorting and Grouping window blank. I get no error when I do so.
But on this report, I get an error stating I can't leave them blank.
So I field them in with the broadest field. The code works and
overrides this choice, but I get that strange repeat.

The error says: "You must define a sort field or expression for the
group header or footer in the report you tried to preview or print."

2) When I hard code the form to the choices in my combobox selections,
and "tick" out the code, greening it, the report runs correctly
without repeating the heading. It is most definately not the data,
believe me.

I do not get this error when I use only two levels. My other report
is actually a two-level situation. So this 3 level is new to me. But
even with two levels I still have the issue number 1 above on this
report.

I wonder if Access somehow cannot handle a three-level situation
properly.

I have never had a problem using many more than two levels.

When you say you 'disable' a group level by "field them in
with the broadest field", there might(?) be something going
on there?? In these situations I use:
Me.GroupLevel(n).ControlSource = "=1"
and then use:
Me.section(x).Visible = False
to hide the group header/footer.

 

[doyle60]

Thanks Marsh,
When you do this, do you leave the Field/Expression blank in the
Sorting and Grouping window?

I do. But this form doesn't allow me to do that. I have to put
something in. Somehow the code does not run first and the database is
seeing that blank Field/Expression and giving me the error. I put the
code in the OnOpen Event, right? I think this is the major clue. So
when you do this, do you have the Field/Expression blank for each
level?

Matt

 

[Marshall Barton]

When you do this, do you leave the Field/Expression blank in the
Sorting and Grouping window?

I do. But this form doesn't allow me to do that. I have to put
something in. Somehow the code does not run first and the database is
seeing that blank Field/Expression and giving me the error. I put the
code in the OnOpen Event, right? I think this is the major clue. So
when you do this, do you have the Field/Expression blank for each
level?

I don't think its possible to leave a group level
Field/Expression blank. I always use design view to put
something in, usually the fields for the most common
situation so I don't need any code for that case.

 

[doyle60]

It must have been currupted some how. I recreated it in another
report, starting from scratch, and it works. I cannot see any
difference between the two.

I do remember once having a formula in the "Field/Expresion" of the
Sorting and Grouping Box for GroupHeader(2) when I first started
making the report. It doesn't seem to have fully forgotten about
that.

Also, I believe Access automatically closed down on me a few times
when building this report---for an unrelated issue though.

Who knows?

Thanks all.

Matt

 

[Marshall Barton]

It must have been currupted some how. I recreated it in another
report, starting from scratch, and it works. I cannot see any
difference between the two.

I do remember once having a formula in the "Field/Expresion" of the
Sorting and Grouping Box for GroupHeader(2) when I first started
making the report. It doesn't seem to have fully forgotten about
that.

Also, I believe Access automatically closed down on me a few times
when building this report---for an unrelated issue though.

Who knows?

Who knows indeed!

I really hesitate to suggest corruption as the answer to a
problem. It seems like a cop-out on what is almost always a
communication issue or a misunderstanding of some kind, but
there are times when corruption really is the problem.

Good job working it out and good luck on the rest of your
project.