From: deb on
I need help really really bad!!

Access 2003
I have a main form f040ProjectMain(PK ProjectID)
I have a continuous subform f4ProjKeyMilestones.

How can I verify that the user has, at minimum, two records in the
continuous subform.
one record with at least one KeyMilestonesSubID = 12 and ActualDt
and another record with at least one KeyMilestonesSubID = 20 and ActualDt

If user does not activate the subform by entering data in it, the subform
validation is never triggered and the critical data is rarely populated.

PLEASE HELP!!!!
How can I assure user enters these two records into subform?
--
deb
From: Dirk Goldgar on
"deb" <deb(a)discussions.microsoft.com> wrote in message
news:6419B9CE-8598-4B12-8E15-0D5BA23A8780(a)microsoft.com...
>I need help really really bad!!
>
> Access 2003
> I have a main form f040ProjectMain(PK ProjectID)
> I have a continuous subform f4ProjKeyMilestones.
>
> How can I verify that the user has, at minimum, two records in the
> continuous subform.
> one record with at least one KeyMilestonesSubID = 12 and ActualDt
> and another record with at least one KeyMilestonesSubID = 20 and ActualDt
>
> If user does not activate the subform by entering data in it, the subform
> validation is never triggered and the critical data is rarely populated.
>
> PLEASE HELP!!!!
> How can I assure user enters these two records into subform?


Using a normal subform, you cannot prevent the parent record from being
saved before the subform records are created. The best you can do is
detect, before you move on to the next parent record or close the form, that
the required subform records don't exist, and return the user to the
original record to complete the subform.

An alternative is to use unbound controls on the main form to fill in the
information for the required sub-records, and have code to load/unload these
controls. That would enable you to validate that these controls have been
filled in before saving the main record. Then you could use a subform for
the other, optional sub-records.

I lean toward the first approach, but either way, it's going to involve some
special coding.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: Roger Carlson on
Something to try. I haven't verified it.

In the BeforeUpdate event of the mainform, use a DCount domain aggregate
function to count the number of records in the table behind the subform
which have a foreign key value that matches the main form primary key.

If it is <2, cancel the form update and direct the user to enter data into
the subform.

If you need more specifics, you need to give the names of your tables and
fields.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




"deb" <deb(a)discussions.microsoft.com> wrote in message
news:6419B9CE-8598-4B12-8E15-0D5BA23A8780(a)microsoft.com...
>I need help really really bad!!
>
> Access 2003
> I have a main form f040ProjectMain(PK ProjectID)
> I have a continuous subform f4ProjKeyMilestones.
>
> How can I verify that the user has, at minimum, two records in the
> continuous subform.
> one record with at least one KeyMilestonesSubID = 12 and ActualDt
> and another record with at least one KeyMilestonesSubID = 20 and ActualDt
>
> If user does not activate the subform by entering data in it, the subform
> validation is never triggered and the critical data is rarely populated.
>
> PLEASE HELP!!!!
> How can I assure user enters these two records into subform?
> --
> deb


From: Dirk Goldgar on
"Roger Carlson" <RogerCarlson(a)noemail.noemail> wrote in message
news:OkqmibuYKHA.4012(a)TK2MSFTNGP04.phx.gbl...
> Something to try. I haven't verified it.
>
> In the BeforeUpdate event of the mainform, use a DCount domain aggregate
> function to count the number of records in the table behind the subform
> which have a foreign key value that matches the main form primary key.
>
> If it is <2, cancel the form update and direct the user to enter data into
> the subform.

Unfortunately, that won't work. The main form's record must be saved before
any related records can be created in the subform.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: Roger Carlson on
That's quite true. Hadn't thought of that. Thanks.

I suppose it would have to be the AfterUpdate event and give them the option
to return to the form or delete the mainform record.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> wrote in message
news:efXdAiuYKHA.4932(a)TK2MSFTNGP02.phx.gbl...
> "Roger Carlson" <RogerCarlson(a)noemail.noemail> wrote in message
> news:OkqmibuYKHA.4012(a)TK2MSFTNGP04.phx.gbl...
>> Something to try. I haven't verified it.
>>
>> In the BeforeUpdate event of the mainform, use a DCount domain aggregate
>> function to count the number of records in the table behind the subform
>> which have a foreign key value that matches the main form primary key.
>>
>> If it is <2, cancel the form update and direct the user to enter data
>> into the subform.
>
> Unfortunately, that won't work. The main form's record must be saved
> before any related records can be created in the subform.
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>