Grouping Form Fields on Continuous Form

[zaz]

Hi,

I have a table which looks somewhat like this:
---------------------------
| Type | Name | Value |
---------------------------
| A | Ball | 1.00 |
| A | Cup | 2.00 |
| B | Fly | 0.50 |
(...and so on)

I want these displayed on a form, however, grouped by Type (similar to a
report), for example

Type: A
Ball [Textbox]
Cup [Textbox]
Type: B
Fly [Textbox]

Is such a thing possible?

The closest I can get is with multiple labels and conditional formatting.
Code seems to be of little use, as, if I try to set the Visible property of
the textbox, it applies to all textboxes on the form (or over the continuous
form).
Maybe there's something I missed? I wouldn't believe this to be exactly a
rare request...

Thanks a lot for reading, and many thanks in advance for any responses!

 

[Beetle]

I would say the problem is in your table, not your form. If one Type
(such as A) can have more than one item (ball, cup, whatever else),
then they should be in separate tables, related by, for example, a TypeID.

Furthermore, if an item can have more than one Type, then you have a
many-to-many relationship, in which case you need a third table.

Then you would set up a Form/subform for display & input.

 

[zaz]

Hey Beetle,
Thanks a lot for the reply!

Yes, I can manipulate the data in the table - I just posted up the "raw"
data that I have. For example, it's quite easy to write up some code which
will pull out all the types and put them into a separate table.

The problem is that subforms cannot be placed in the Detail section of a
continuous form...

The method I'm currently using, puts an additional "dummy" Type record like
follows:
---------------------------
| Type | Name | Value |
---------------------------
| A | | |
| A | Ball | 1.00 |
| A | Cup | 2.00 |
| B | | |
| B | Fly | 0.50 |
(...and so on)

And then I can use conditional formatting to make the textbox appear to be
gray when Name is Null. The problem here is that the textbox is still there,
and doesn't exactly "look right".

Thanks again for reading and taking the time to respond
Do you have any further ideas?

I would say the problem is in your table, not your form. If one Type
(such as A) can have more than one item (ball, cup, whatever else),
then they should be in separate tables, related by, for example, a TypeID.

Furthermore, if an item can have more than one Type, then you have a
many-to-many relationship, in which case you need a third table.

Then you would set up a Form/subform for display & input.
--
_________

Sean Bailey

Hi,

I have a table which looks somewhat like this:
---------------------------
| Type | Name | Value |
---------------------------
| A | Ball | 1.00 |
| A | Cup | 2.00 |
| B | Fly | 0.50 |
(...and so on)

I want these displayed on a form, however, grouped by Type (similar to a
report), for example

Type: A
Ball [Textbox]
Cup [Textbox]
Type: B
Fly [Textbox]

Is such a thing possible?

The closest I can get is with multiple labels and conditional formatting.
Code seems to be of little use, as, if I try to set the Visible property of
the textbox, it applies to all textboxes on the form (or over the continuous
form).
Maybe there's something I missed? I wouldn't believe this to be exactly a
rare request...

Thanks a lot for reading, and many thanks in advance for any responses!

 

[Marshall Barton]

I have a table which looks somewhat like this:
---------------------------
| Type | Name | Value |
---------------------------
| A | Ball | 1.00 |
| A | Cup | 2.00 |
| B | Fly | 0.50 |
(...and so on)

I want these displayed on a form, however, grouped by Type (similar to a
report), for example

Type: A
Ball [Textbox]
Cup [Textbox]
Type: B
Fly [Textbox]

Is such a thing possible?

The closest I can get is with multiple labels and conditional formatting.
Code seems to be of little use, as, if I try to set the Visible property of
the textbox, it applies to all textboxes on the form (or over the continuous
form).
Maybe there's something I missed? I wouldn't believe this to be exactly a
rare request...

Actually, I've only seen two or three requests for this kind
of thing in the last 15 years, so I think it is very rare.

What have you missed? I gues the key point is that you can
not use CF for the reason you are seeing. Instead you need
to use a transparent text box with a special block font
(http://www.mvps.org/access/forms/frm0055.htm )on top of all
the other controls using an expression that results in null
for normal records and the full block character for the
header records.

If that sounds complicated, it's only the tip of the
iceburg. Making the header records unselectable and still
being able to select the other controls on data records is a
whole 'nother can of worms.

Just in case you have a LOT of time to devote to this
relatively esoteric kind of display, here's the query I used
in my incomplete example form's record source:

SELECT Team, City, State, Null AS Display
FROM Teams
UNION ALL SELECT DISTINCT State, Null, Null, 1
FROM Teams
ORDER BY State, Team;