Query problem

Hi all,
I have this problem with a subform in that it will not open/refresh
with a blank/new record for the user to use. I suspect it is because of
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 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 ?

Thanks in advance,
Mitch...


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

On Mon, 3 May 2004 11:50:31 -0500, MBison80
wrote:

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?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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...


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/