Filter main form to records with no related records in subform,maintain updateable recordset

[esn]

Hi - I need some ideas.

I have a parent form whose recordset is based on 3 tables. The tables
have strict one-to-many relationships, and the recordset is
updateable.

The subform is based on yet another table, with a one-to-many
relationship to the above recordset.

The purpose of this form is to allow users to view records in the
parent form and add a subform record. It all works nicely to this
point. Specifically, the records in the main form correspond to
photos, and the subform identifies the content of the photo. Users
can page through photos and identify the contents with no problems.

What I'm trying to add is a checkbox that can be clicked to filter the
main form. The filter would hide records where the content has
already been identified. I've done this using a left join in the
parent form's record source and that works, or by using an IIF([ID] In
(SELECT...)) field in the recordsource.

The problem with both of these methods is that I lose updateability of
the parent form's recordset as soon as I include those fields. This
is an issue because there is one field in the parent recordset that I
would like the user to be able to edit. Any ideas on how I can get
the filter to work and still have an updateable recordset in the
parent form?

Thanks

 

[esn]

Solved:

Rather than including a field in the record source to filter on, I
built the filter string using (([ID]) In (SELECT [ImageID]...)) and it
works like a charm.

Thanks for looking!

 

[esn]

Sorry to revive this thread. I thought I had it nailed by filtering
the records as mentioned above, but that causes some serious
performance issues. With a few thousand records the filter works
fine, but I now have 50,000 records (and another 100,000 on the way)
in the subform's underlying table so filtering based on "ID Not
In(SELECT..." can take several minutes. Is there a solution that
will keep the recordset updateable but avoid the performance issues?
The foreign key field is already indexed.

 

[John W. Vinson]

Sorry to revive this thread. I thought I had it nailed by filtering
the records as mentioned above, but that causes some serious
performance issues. With a few thousand records the filter works
fine, but I now have 50,000 records (and another 100,000 on the way)
in the subform's underlying table so filtering based on "ID Not
In(SELECT..." can take several minutes. Is there a solution that
will keep the recordset updateable but avoid the performance issues?
The foreign key field is already indexed.

Please repost the original question, with the SQL of the query. Not everyone
keeps every post in every thread over the history of Usenet. I suspect a
"frustrated inner join" unmatched query will work for you but based on this
post it's impossible to even tell which thread you're reviving, much less the
details of the problem.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[esn]

Sorry about that - here's some text from the original post:

I have a parent form whose recordset is based on 3 tables. The tables
have strict one-to-many relationships, and the recordset is
updateable. The subform is based on yet another table, with a one-to-
many relationship to the above recordset.

The purpose of this form is to allow users to view records in the
parent form and add a subform record. It all works nicely to this
point. Specifically, the records in the main form correspond to
photos, and the subform identifies the content of the photo. Users
can page through photos and identify the contents with no problems.

What I'm trying to add is a checkbox that can be clicked to filter the
main form. The filter would hide records where the content has
already been identified. I've done this using a left join in the
parent form's record source and that works, or by using an IIF([ID] In
(SELECT...)) field in the recordsource.

The problem with both of these methods is that I lose updateability of
the parent form's recordset as soon as I include those fields. This
is an issue because there is one field in the parent recordset that I
would like the user to be able to edit. Any ideas on how I can get
the filter to work and still have an updateable recordset in the
parent form?

(End of original post)

The solution I came up with was to add "WHERE ID Not In(SELECT ImageID
FROM CamPhotoDetections)" to the form's filter when the checkbox is
ticked. As I stated earlier, with a few thousand records this is
fine, but when the form is filtering 150,000 records based on the
50,000 results of that select statement things get ugly.

Here's the SQL for the form's record source:
SELECT CamDeployments.SeasonStartYear, CamDeployments.SampleUnitID,
CamVisits.ExistingCameraID, CamPhotos.ID, CamPhotos.VisitID,
CamPhotos.ImageNum, CamPhotos.Temperature, CamPhotos.MoonPhase,
CamPhotos.DateTime, CamPhotos.Highlight, CamPhotos.Observer1,
CamPhotos.Observer2
FROM (CamDeployments INNER JOIN CamVisits ON
CamDeployments.DeploymentID = CamVisits.DeploymentID) INNER JOIN
CamPhotos ON CamVisits.CamVisitID = CamPhotos.VisitID
ORDER BY CamDeployments.SeasonStartYear, CamDeployments.SampleUnitID,
CamVisits.ExistingCameraID, CamPhotos.ImageNum;