Subdatasheets. subforms and record selection

[Dale Walker]

I've got a form that currently has a list box used to "find a record
on my form" but the list in it is far too long and consists of fields
of repeating data.

Is there a way of creating a subform to do the same job that is in
datasheet view with a collapsible subdatasheet?

The main form is based on a table consisting of dates, events and
venues (plus a lot of other guff not needed to explain this little
problem). Each record has an ID, a date, an event name and a venue
name (Event Names and Venue Names are IDs to load of other data on
other tables but again, that's not part of the problem).

Simplified the table looks like this...

ID Date Event Venue
01 06/08/2005 Lost SE1
02 06/08/2005 RetroVert Boat Party (TowerBridge)
03 06/08/2005 Fabric Fabric
04 06/08/2005 Jaded Egg
05 13/08/2005 AsOne End, The
06 13/08/2005 Fabric Fabric
07 13/08/2005 Saiko! Medicine Bar
08 20/08/2005 Fabric Fabric
09 20/08/2005 Haywire SE1

Currently I can select an entry on my list box to find the full
details of the record in question. The list box entries look basically
like the table above but sorted by event then date.

However, I would prefer a 'subdatasheet' style selection process that
may look something similar to this...

[+] AsOne
[+] Fabric
[+] Haywire
[+] Jaded
[+] Lost
[+] RetroVert
[+] Saiko!

Which could expand when needed like this...

[+] AsOne
[-] Fabric
06/08/2005 Fabric
13/08/2005 Fabric
20/08/2005 Fabric
[+] Haywire
[+] Jaded
[+] Lost
[+] RetroVert
[+] Saiko!

If I then selected the "13/08/2005 Fabric" entry, then it would find
the record in question.

Is this possible? Would love to know how.

I know how to create subforms with datasheets, I know how to use
subforms to select records, I know how to create queries with
subdatasheets and have worked out how to create the effect from the
single table but just can't seem to work out how to glue the whole
thing together.

Am I barking up the wrong tree? Is there a control (3rd party maybe)
that does essentially the same job?

 

[Mondrogan]

What about having two tables on your form, linked 1:M, where the main table
lists the Events and the second table shows the associated Venues, Dates,
etc., for each Event?

 

[Dale Walker]

What about having two tables on your form, linked 1:M, where the main table
lists the Events and the second table shows the associated Venues, Dates,
etc., for each Event?

That was my backup plan but my form is already choc full of subforms,
linked tables and whatnot. I'm already having problems with screen
real estate and to be honest adding two tables simply for locating
particular records is going to make the whole form unuserfriendly. I
suppose I could farm off the searching functions to another form but
that's not that elegant and it adds to the amount of clicks needed to
find something. The subdatasheet subform select box just seems so much
more of an elegant way to do things.

I've managed to work out how to display a subdatasheet within a
subform (by creating a subform within a subform and then display as a
datasheet) but I'm still having difficulties with getting the main
form to display the record selected by that subform.

 

[SUAT ERDEM]

What about having two tables on your form, linked 1:M, where the main table
lists the Events and the second table shows the associated Venues, Dates,
etc., for each Event?

I've got a form that currently has a list box used to "find a record
on my form" but the list in it is far too long and consists of fields
of repeating data.

Is there a way of creating a subform to do the same job that is in
datasheet view with a collapsible subdatasheet?

The main form is based on a table consisting of dates, events and
venues (plus a lot of other guff not needed to explain this little
problem). Each record has an ID, a date, an event name and a venue
name (Event Names and Venue Names are IDs to load of other data on
other tables but again, that's not part of the problem).

Simplified the table looks like this...

ID Date Event Venue
01 06/08/2005 Lost SE1
02 06/08/2005 RetroVert Boat Party (TowerBridge)
03 06/08/2005 Fabric Fabric
04 06/08/2005 Jaded Egg
05 13/08/2005 AsOne End, The
06 13/08/2005 Fabric Fabric
07 13/08/2005 Saiko! Medicine Bar
08 20/08/2005 Fabric Fabric
09 20/08/2005 Haywire SE1

Currently I can select an entry on my list box to find the full
details of the record in question. The list box entries look basically
like the table above but sorted by event then date.

However, I would prefer a 'subdatasheet' style selection process that
may look something similar to this...

[+] AsOne
[+] Fabric
[+] Haywire
[+] Jaded
[+] Lost
[+] RetroVert
[+] Saiko!

Which could expand when needed like this...

[+] AsOne
[-] Fabric
06/08/2005 Fabric
13/08/2005 Fabric
20/08/2005 Fabric
[+] Haywire
[+] Jaded
[+] Lost
[+] RetroVert
[+] Saiko!

If I then selected the "13/08/2005 Fabric" entry, then it would find
the record in question.

Is this possible? Would love to know how.

I know how to create subforms with datasheets, I know how to use
subforms to select records, I know how to create queries with
subdatasheets and have worked out how to create the effect from the
single table but just can't seem to work out how to glue the whole
thing together.

Am I barking up the wrong tree? Is there a control (3rd party maybe)
that does essentially the same job?

---
Dale Walker
London Techno Events
dale@sorted,org
http://london.sorted.org