Group and sort using VBA

[Justin]

Is there a way to group and sort using vba instead of the "graphical" way?

 

[Tom Lake]

Is there a way to group and sort using vba instead of the "graphical" way?

You'd be better off using SQL statements than VBA.

Tom Lake

 

[Justin]

Ok.

Well what I have is a report that is grouped by category(ex. Mathematics)
and then by group(ex, Measurement). Each group has several elements. The
element field is in the detail section of the report.

Then I have the report in columns in Page Setup. What I need to do is get a
section for "comments" in one of the columns. I don't want this section in
each column. I have no idea how to do that with out adding a "comment"
section after each element because it is in the detail field.

So how could I accomplish this?

And did any of that make any sense?

 

[Marshall Barton]

Is there a way to group and sort using vba instead of the "graphical" way?

Almost. The group and sort levels must exist in the report,
then they can be modified in the report's open event
procedure.

The code in the open event would just set a group level's
ControlSource property to the field/expression as specified
on a form before opening the report.

Me.GroupLevel(x) = Forms!theform.cboSortField

If you want to change other things (e,g, a group header text
box's control source) and need help with that too, please
provide more specific information about what you need to
accomplish with the report.

 

[Justin]

Ok. that doesn't seem to difficult but to the second part of my question is
there a way to get a "comments" section in one and only one column of the
report?

 

[Marshall Barton]

Ok. that doesn't seem to difficult but to the second part of my question is
there a way to get a "comments" section in one and only one column of the
report?

I do not see a second part to your question and I am having
trouble unraveling your reply to Tom. Could you provide
more information and maybe an example of what you are after?

 

[Justin]

This is the second question:

'Then I have the report in columns in Page Setup. What I need to do is get a
section for "comments" in one of the columns. I don't want this section in
each column. I have no idea how to do that with out adding a "comment"
section after each element because it is in the detail section of the report.

So how could I accomplish this?

And did any of that make any sense?"

Not sure if anybody is going to understand this or be able to help. I may
have to do some more experimenting.

 

[Marshall Barton]

This is the second question:

'Then I have the report in columns in Page Setup. What I need to do is get a
section for "comments" in one of the columns. I don't want this section in
each column. I have no idea how to do that with out adding a "comment"
section after each element because it is in the detail section of the report.

So how could I accomplish this?

And did any of that make any sense?"

Not sure if anybody is going to understand this or be able to help. I may
have to do some more experimenting.

Well, that's just a copy of the part that I can't unravel so
it's not making any more sense than it did before.

Can't you provide more information that demonstrates where
each record and its controls is positioned on the page?

 

[Justin]

Here is what the report contains:

A StuID Header(groups by stuid)
A Category Header(groups by category)
A Group Header(groups by group)
The default detail section(contains the element field)

Each category has many groups and a groups has many elements.

ex.
Category
- Special Subjects
Group
-Art
Element
-Exhibits appropriate behavior
-Applies art skills and demonstrates understanding of
basic concepts
-Library Education
Element
-Exhibits appropriate behavior
-Uses the library effectively

I think that will give you the idea.

The report when viewed looks like this

ex.
Special subjects Quarter
Art 1
2 3 4
Exhibits appropriate behavior A A B C
Applies art skills and demonstrates understanding of basic concepts
Library Education 1 2 3
4
Exhibits appropriate behavior A A B C
Uses the library effectively A A B C

And this will do this for every category.

The page is setup for landscape and has three columns per page.

Now what i need to do next is to add a "comments" section to the end of one
of the columns. I don't know where/how to do that and if i even can.


Is there any way to send you a copy of the database so that you can see
exactly what is going on?

 

[Marshall Barton]

Here is what the report contains:

A StuID Header(groups by stuid)
A Category Header(groups by category)
A Group Header(groups by group)
The default detail section(contains the element field)

Each category has many groups and a groups has many elements.

ex.
Category
- Special Subjects
Group
-Art
Element
-Exhibits appropriate behavior
-Applies art skills and demonstrates understanding of
basic concepts
-Library Education
Element
-Exhibits appropriate behavior
-Uses the library effectively

I think that will give you the idea.

The report when viewed looks like this

ex.
Special subjects Quarter
Art 1
2 3 4
Exhibits appropriate behavior A A B C
Applies art skills and demonstrates understanding of basic concepts
Library Education 1 2 3
4
Exhibits appropriate behavior A A B C
Uses the library effectively A A B C

And this will do this for every category.

The page is setup for landscape and has three columns per page.

Now what i need to do next is to add a "comments" section to the end of one
of the columns. I don't know where/how to do that and if i even can.

Is there any way to send you a copy of the database so that you can see
exactly what is going on?

I assume that your "comments section" is just a blank area
for people to write something.

A column is not an object and there is no Access feature
that allows you to specify where something should go in a
column. If it makes any sense in your report, you should
put that kind of thing in a real report section, probably a
group footer. Maybe the Page footer would be a good place??

 

[Justin]

Ok. How do you do a Group Footer? I now there is page footer and report
footer. I think that option will be my best bet.

Thanks.

 

[Marshall Barton]

Ok. How do you do a Group Footer? I now there is page footer and report
footer. I think that option will be my best bet.

Use the Sorting and Grouping window (View menu), just like
when you created your group header sections.

 

[Justin]

Ok. Is there a way to do an "if" statement that says at the end of this group
put a footer. That way I could tell it what group to put the comment section
under. Does that make sense?

 

[Marshall Barton]

Not quite, but you can make the section invisible or just
Cancel the section's Format event. Maybe something like:

Cancel = (groupingfield <> "Art")

BTW, "Group" is a really bad name for a field in a table,
It's an SQL reserved work and can seriously mess qith your
queries.

 

[Justin]

Ok, I put that statement in the report_open and now it doesn't even open the
report.

What did i do wrong?

Not quite, but you can make the section invisible or just
Cancel the section's Format event. Maybe something like:

Cancel = (groupingfield <> "Art")

BTW, "Group" is a really bad name for a field in a table,
It's an SQL reserved work and can seriously mess qith your
queries.
--
Marsh
MVP [MS Access]

Ok. Is there a way to do an "if" statement that says at the end of this group
put a footer. That way I could tell it what group to put the comment section
under. Does that make sense?

 

[Marshall Barton]

Since you are trying to show/suppress a group footer, the
line of code should be in the group footer's Format event
procedure.

FYI, the open event is only used to configure the report
(e.g. set its record source) before the report is processed.
All data related actions need to be done in a section's
Format or Print events.

 

[Justin]

Ok. It has been a while but...

I tried to put this in the group footer section.

Results:

In Report View I see all of the Comment fields under every Category.

In Print Preview i see nothing expect for a blank space under each Category.

what am i doing wrong?

Since you are trying to show/suppress a group footer, the
line of code should be in the group footer's Format event
procedure.

FYI, the open event is only used to configure the report
(e.g. set its record source) before the report is processed.
All data related actions need to be done in a section's
Format or Print events.
--
Marsh
MVP [MS Access]

Ok, I put that statement in the report_open and now it doesn't even open the
report.

What did i do wrong?

 

[Marshall Barton]

It's been awhile for me too. My news server has had some
serious problems that pretty much locked me out of here.

To be sure of what you did, I would need to see a Copy/Paste
of the group footer section's Format event procedure that
you used.

Report Layout view is not useful for viewing your data. You
must either print the report using Normal view or use
Preview to display it in a window.

Are you sure that the groupingfield has at least one record
that contains "Art"?