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

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开发者_Python百科 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

I found a number of problems with the solutions posted. Most obviously there are issues (as noted) if there are duplicate entries. But I found there was a deeper issue which is that you can't do this and still sort the data on yet another field (suppose you wanted to sort by the date by which each name was first registered in each state, and yet NOT rank them that way).

A better solution is found in the Using Code section of the Microsoft KB:

http://support.microsoft.com/kb/q101081

The code from that section retrieves the actual data from the Previous field, in whatever sort order you define.

Computationally this can be a bit intense, so it works only for small datasets, but I achieved good results by first testing the function:

=PrevRecVal([Forms]![myForm],"ID",[ID],"Names")=[Names])

then putting it inside the IIF function:

=IIf(PrevRecVal([Forms]![myForm],"ID",[ID],"Names")=[Names],"",[FullName])

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.

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.