must have 2 records in subform
in [Form]
|
From: John W. Vinson on 11 Nov 2009 12:51 On Wed, 11 Nov 2009 10:51:49 -0500, "Roger Carlson" <RogerCarlson(a)noemail.noemail> wrote: >Something to try. I haven't verified it. Unfortunately it won't work: the mainform's BeforeUpdate event fires the instant you set focus to the subform. It must, in order for there to be a main table record saved so that referential integrity can be maintained. Even at that, you must first create one subform record before you can create a second subform record. It's a "chicken or egg" problem - you're insisting on there being two eggs already before you even have the chicken! -- John W. Vinson [MVP]
From: deb on 11 Nov 2009 13:04 Can you give me an example of the first approach? -- deb "Dirk Goldgar" wrote: > "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: Dirk Goldgar on 11 Nov 2009 15:39 "deb" <deb(a)discussions.microsoft.com> wrote in message news:39C329D4-A1DD-43A9-B885-39B2B991A3AE(a)microsoft.com... > Can you give me an example of the first approach? Here's an example that simply requires that each main record have at least one subform record. In this example, the main table is called tMain (with primary key "ID") and the child table is called tSub (with foreign key "MainID"). Here's the code from the main form's module: '------ start of code ------ Option Compare Database Option Explicit Dim LastRecordID As Variant Private Function RequireChildRecord(Optional Unloading As Boolean) Dim GoBackID As Variant GoBackID = Null If Len(LastRecordID & vbNullString) > 0 Then If (LastRecordID <> Nz(Me.ID, 0)) Or Unloading Then If DCount("*", "tSub", "MainID=" & LastRecordID) = 0 Then If MsgBox( _ "No child record entered for record! Go Back?", _ vbExclamation + vbYesNo, _ "Subform Entry Required") _ = vbYes _ Then GoBackID = LastRecordID End If End If End If End If If Not IsNull(GoBackID) Then If Unloading Then DoCmd.CancelEvent End If Me.Recordset.FindFirst "ID=" & GoBackID Else LastRecordID = Me.ID End If End Function Private Sub Form_Current() RequireChildRecord End Sub Private Sub Form_Unload(Cancel As Integer) RequireChildRecord True End Sub '------ end of code ------ This version lets the user escape from the required entry, if they want, and doesn't offer the option of deleting the main-form's record. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: deb on 11 Nov 2009 16:04 Thank you for this!!! Does it go under main form OnCurrent?? -- deb "Dirk Goldgar" wrote: > "deb" <deb(a)discussions.microsoft.com> wrote in message > news:39C329D4-A1DD-43A9-B885-39B2B991A3AE(a)microsoft.com... > > Can you give me an example of the first approach? > > > Here's an example that simply requires that each main record have at least > one subform record. In this example, the main table is called tMain (with > primary key "ID") and the child table is called tSub (with foreign key > "MainID"). Here's the code from the main form's module: > > '------ start of code ------ > Option Compare Database > Option Explicit > > Dim LastRecordID As Variant > > Private Function RequireChildRecord(Optional Unloading As Boolean) > > Dim GoBackID As Variant > > GoBackID = Null > > If Len(LastRecordID & vbNullString) > 0 Then > If (LastRecordID <> Nz(Me.ID, 0)) Or Unloading Then > > If DCount("*", "tSub", "MainID=" & LastRecordID) = 0 Then > > If MsgBox( _ > "No child record entered for record! Go Back?", _ > vbExclamation + vbYesNo, _ > "Subform Entry Required") _ > = vbYes _ > Then > GoBackID = LastRecordID > End If > > End If > > End If > End If > > If Not IsNull(GoBackID) Then > If Unloading Then > DoCmd.CancelEvent > End If > Me.Recordset.FindFirst "ID=" & GoBackID > Else > LastRecordID = Me.ID > End If > > End Function > > > Private Sub Form_Current() > > RequireChildRecord > > End Sub > > > Private Sub Form_Unload(Cancel As Integer) > > RequireChildRecord True > > End Sub > '------ end of code ------ > > This version lets the user escape from the required entry, if they want, and > doesn't offer the option of deleting the main-form's record. > > -- > Dirk Goldgar, MS Access MVP > Access tips: www.datagnostics.com/tips.html > > (please reply to the newsgroup) >
From: Dirk Goldgar on 11 Nov 2009 16:12
"deb" <deb(a)discussions.microsoft.com> wrote in message news:CC8B8F58-B2B3-4FCC-874F-8CBEE7EBA4F4(a)microsoft.com... > Thank you for this!!! > > Does it go under main form OnCurrent?? The code I posted was for the main form, but note that it included code for both the form's Current event and its Unload event, as well as a module-level variable and a general function. If you don't already have code for the form's Current and Unload events, you can just paste the variable declaration, the general function, and the two event procedures into the General section of the form's VBA module. If you do have code for those events, you'll have to modify that code to incorporate the additional procedure code. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |