Plus sign on datasheet forms not showing

Dear All,

Just a hope that somebody may know the answer to this off
the top of their head...

I've got an Access 2000 database for a customer based on a
SQL Server 2000 backend. There are three tables: Event,
VenueGroup and Venue linked together as follows:
* Event linked to VenueGroup as a One to many by EventId
(int with a sequence to generate the next number
automatically)
* VenueGroup linked to Venue as a one to many by
VenueGroupId (int with a sequence to generate the next
number automatically)

so an event record can have a number of VenueGroup records
which in turn contain a number of venue records.

The purpose of this system is to create an overall event
item with smaller venues belonging to it.
The smaller venues are grouped together under VenueGroup
which contains resources common to venue records.

I have a single form to show Event records, this has a
number of tabbed pages on it, one of which has a datasheet
form to display VenueGroup records
The VenueGroup datasheet form in turn has a datasheet on it
to display Venue Records.

When I open my VenueGroup datasheet form outside of the
event form, it displays a plus sign to show that venue
records are available under the VenueGroup

If I put the VenueGroup datasheet on the event form without
linking it by eventid it still shows the plus sign

If I link the VenueGroup datasheet to the event form by
eventid then the plus sign won't show, even if venue records
exist. This seems to happen whether I use Parent/Child
Linked fields, or incorporate a link in the VenueGroup
forms datasource

What am I missing??

Thanks in advance!

Paul Le Sueur.

I just figured it out! and thought that it would be a good idea to
post the solution here to help anybody else...

It appears that when the sub datasheet first opens it must
display a record otherwise it will not bother displaying the
plus sign for subsequent queries.

My particular interface used a combo box in the header of the
main form to allow selection of the current record on the main
form. This meant that when the form opened for the first time
there was no current record in the main form (as the combo box
has not selected anything yet). Therefore the sub datasheet is
not selecting based on a valid value and therefore has no
records to display. This means that it will not display the plus
sign column in the sub datasheet.

I got around it by building a dummy SQL statement to return a
record when the sub datasheet form first opens. This displays
the plus sign as desired. When a new record is selected with
the combo box, the afterupdate event updates the recordsource
of the sub datasheet to the correct query, and it still has the
plus sign column on the sub datasheet.

I hope this helps somebody else!

Paul Le Sueur.

wrote:

Dear All,

Just a hope that somebody may know the answer to this off
the top of their head...

I've got an Access 2000 database for a customer based on a
SQL Server 2000 backend. There are three tables: Event,
VenueGroup and Venue linked together as follows:
* Event linked to VenueGroup as a One to many by EventId
(int with a sequence to generate the next number
automatically)
* VenueGroup linked to Venue as a one to many by
VenueGroupId (int with a sequence to generate the next
number automatically)

so an event record can have a number of VenueGroup records
which in turn contain a number of venue records.

The purpose of this system is to create an overall event
item with smaller venues belonging to it.
The smaller venues are grouped together under VenueGroup
which contains resources common to venue records.

I have a single form to show Event records, this has a
number of tabbed pages on it, one of which has a datasheet
form to display VenueGroup records
The VenueGroup datasheet form in turn has a datasheet on it
to display Venue Records.

When I open my VenueGroup datasheet form outside of the
event form, it displays a plus sign to show that venue
records are available under the VenueGroup

If I put the VenueGroup datasheet on the event form without
linking it by eventid it still shows the plus sign

If I link the VenueGroup datasheet to the event form by
eventid then the plus sign won't show, even if venue records
exist. This seems to happen whether I use Parent/Child
Linked fields, or incorporate a link in the VenueGroup
forms datasource

What am I missing??

Thanks in advance!

Paul Le Sueur.