How to simulate a Group Header in an Access Continuous Form?

Question

I have a form (not a report) in Access displayed in Continuous View that has alot of repeat data. i.e. it looks like:

State Names
FL Abe
FL Bart
FL Charlie
GA Deleanor
GA Mary

This needs to be interactive (there's some command buttons on each row as well as in the form header), but there's alot of repetitive data. Is there a way to add a group header by state? i.e. make it look like:

State Names
FL
Abe
Bart
Charlie
GA
Deleanor
Mary

Answer 1

The closest I can get is to add a field to your form's record source which numbers each row within a group. Then you could add a text box in the detail section of your form with this expression as its control source.

=IIf(rank = 1, [State], ""]

The text box would display the first occurrence of each state, and a zero length string otherwise.

I tested with this query for the record source.

SELECT
    m1.state,
    m1.names,
    (
        SELECT Count(names)
        FROM MyTable AS m2
        WHERE
            m2.state=m1.state
            AND m2.names<=m1.names
    ) AS rank
FROM MyTable AS m1
ORDER BY m1.state, m1.names;

And it worked, but is not editable. If you need to be able to edit, try a query which uses DCount to generate the rank.

SELECT
    m1.state,
    m1.names,
    DCount("names", "MyTable",
    "state ='" & state & "' AND " & "names <= '" & names & "'") AS rank
FROM MyTable AS m1
ORDER BY m1.state, m1.names;

Both those queries give this result set with your sample data:

state names     rank
FL    Abe       1
FL    Bart      2
FL    Charlie   3
GA    Deleanor  1
GA    Mary      2

Note, I assumed unique combinations of state and name.

Answer 2

Have you looked at subdatasheets? If you have a state table (or just query your existing data for the unique states), you could use that in a non-editable parent datasheet, and then the subdatasheet would display the people data, and you could make it editable.

I'd implement this with datasheet forms, not with the actual table or query datasheets. This gives you a lot more control over the subdatasheet linking and formatting, as well as making interacting with the datasheet events easier (it can be done with a table or query datasheet, but only through Screen.ActiveDatasheet.