Grouping on a Form

[Paul Fenton]

I have a report of construction jobs which is grouped by week. In
each week grouping, there are records for the particular day of the
week. The data comes from a query and looks like this...

Week Day Task Client Phone

Week of 7/5/04
Tuesday, July 6 Lath Smith 123-123-1234
Wednesday, July 6 Paint Jones etc

Week of 7/12/04
Thursday July 15 Paint Allen etc
Friday, July 16 Lath Frank etc.

Often the report isn't accurate due to input errors so we have to
close it, open the client record, drill down to his construction
tasks, edit that record, close that form, go back to the report, etc.
It's a pain.

What I'd like to do is display this data in a form where we can click
the client name, pop up his construction record, edit, then update the
underlying form. What I'm struggling with is the grouping issue and
I need some help with that.


Paul Fenton
(e-mail address removed)

 

[Marshall Barton]

I have a report of construction jobs which is grouped by week. In
each week grouping, there are records for the particular day of the
week. The data comes from a query and looks like this...

Week Day Task Client Phone

Week of 7/5/04
Tuesday, July 6 Lath Smith 123-123-1234
Wednesday, July 6 Paint Jones etc

Week of 7/12/04
Thursday July 15 Paint Allen etc
Friday, July 16 Lath Frank etc.

Often the report isn't accurate due to input errors so we have to
close it, open the client record, drill down to his construction
tasks, edit that record, close that form, go back to the report, etc.
It's a pain.

What I'd like to do is display this data in a form where we can click
the client name, pop up his construction record, edit, then update the
underlying form. What I'm struggling with is the grouping issue and
I need some help with that.

There is no built-in way to do grouping in a (presumably
Continuous) form.

Ordinarily, it's sufficient to just sort the records in the
desired order by adding a calculated field for the week.
This is easy to do in the form's record source query:

SELECT taskdate, Task, Client, Phone
FROM thetable
WHERE Client = xxx
ORDER BY DatePart("ww", taskdate), taskdate

If that's not sufficient, then you'll have to create a query
that has the additional records to display the week:

SELECT Null As WeekDate, taskdate, Task, Client, Phone,
taskdate AS SortField
FROM thetable
WHERE Client = xxxx
UNION
SELECT DateAdd("d", 1-DatePart("w", taskdate), taskdate) AS
WeekDate,
Null, Null, Null, Null, WeekDate
FROM thetable
WHERE Client = xxxx
ORDER BY 5

You can position the taskdate text box on top of the
WeekDate text box to make it look kind of like a group
header.

You will probably also want to use a little code in the
form's Current event to prevent a user from editing the
"header" records:

Me.AllowEdits = (Not IsNull(Client)) Or Me.NewRecord