Query problem

[MBison80]

Hi all,
I have this problem with a subform in that it will not open/refres
with a blank/new record for the user to use. I suspect it is because o
this recordsource...


SELECT DISTINCT tblCalendar.MainProj, tblCalendar.Project
tblCalendar.Issue, tblStatus.DueDate
FROM tblCalendar LEFT JOIN tblStatus ON tblCalendar.CalID
tblStatus.CalID
WHERE (((tblStatus.DueDate)=[forms]![frmcal].[calendar0].[Value]));

when it is not DISTINCT the new record row is there however I woul
like to have the form only display distinct records BUT I would als
like it to have a blank record for the user to fill in. Is there a wa
around this ?

Thanks in advance,
Mitch..

 

[John Vinson]

when it is not DISTINCT the new record row is there however I would
like to have the form only display distinct records BUT I would also
like it to have a blank record for the user to fill in. Is there a way
around this ?

Nope.

No DISTINCT query can ever be updateable, because the individual
records that you see are each (potentially) composite records - if
there are six records with identical values for the selected fields,
you'll only see one of them, and it's undefined WHICH one you see.
Since Access cannot unambiguously determine which record to update, it
won't let you update (or insert!) any.

Any way you could use a subform and update the one and many side
tables separately?

 

[MBison80]

Thanks John,
i've taken your advice, i've set up a hidden subform with a form that
displays the records without the Distinct in the query and it is
visible only when the user decides they want to add a new record.

Thanks again...