must have 2 records in subform
in [Form]
|
From: deb on 11 Nov 2009 10:30 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 11 Nov 2009 10:48 "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 11 Nov 2009 10:51 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 11 Nov 2009 11:03 "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 11 Nov 2009 11:09
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) > |