If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
must have 2 records in subform
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 |
#2
|
|||
|
|||
must have 2 records in subform
"deb" wrote in message
... 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) |
#3
|
|||
|
|||
must have 2 records in subform
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/...UBED1=ACCESS-L "deb" wrote in message ... 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 |
#4
|
|||
|
|||
must have 2 records in subform
"Roger Carlson" wrote in message
... 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) |
#5
|
|||
|
|||
must have 2 records in subform
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/...UBED1=ACCESS-L "Dirk Goldgar" wrote in message ... "Roger Carlson" wrote in message ... 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) |
#6
|
|||
|
|||
must have 2 records in subform
On Wed, 11 Nov 2009 10:51:49 -0500, "Roger Carlson"
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] |
#7
|
|||
|
|||
must have 2 records in subform
Can you give me an example of the first approach?
-- deb "Dirk Goldgar" wrote: "deb" wrote in message ... 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) |
#8
|
|||
|
|||
must have 2 records in subform
"deb" wrote in message
... 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) |
#9
|
|||
|
|||
must have 2 records in subform
Thank you for this!!!
Does it go under main form OnCurrent?? -- deb "Dirk Goldgar" wrote: "deb" wrote in message ... 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) |
#10
|
|||
|
|||
must have 2 records in subform
"deb" wrote in message
... 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) |
Thread Tools | |
Display Modes | |
|
|