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

"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)

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

"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)

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)

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]

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)

"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)

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)

"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)