Require a child record be entered in subform?

[D. Shane Fowlkes]

Hello all!

I have a simple form which also has a subform in it where child records can
be written to a child table based on the parent record/form. I would like
to make it required that the user enter/pick at least one child record (buy
many are possible). As it is now, it's not required.

Is there some simple attribute using Access 2003 in the form or subform I
can set to make a child record on the subform be required instead of
optional?

Think of the tables/forms like shown below As my form is designed now,
someone could enter "Bob" in the form and never pick if he has one or more
certifications...which should be required. Make sense?


StaffTable:
ID | PersonName
---------------------
1 | Bob
2 | John


CertificationsTable
ID | CertName
-----------------------
1 | Juggling
2 | Yoddling


CertStaffMatch
ID | StaffID | CertID

 

[Troy]

In the BeforeUpdate of the main form, create a recordsetclone of the
subform. If there are no records, cancel the update event and tell the user
why.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs as DAO.Recordset
Set rs = Me.SubFormControlName.Form.RecordsetClone
With rs
If .EOF and .BOF then
Cancel = True
msgbox "You did not enter a Certification for this Employee"
end if
End With
rs.Close
End Sub

NOTE: Because Recordset clone is a pointer and will change automatically as
you scroll through records on the parent, you could set this to a module
level variable in the Load event, and just check it in the BeforeUpdate:

Private m_RS as DAO.Recordset
Private Sub Form_Load()
Set m_RS = Me.SubFormControlName.Form.RecordsetClone
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
With m_RS
If .EOF and .BOF then
Cancel = True
end if
End With
End Sub

Be sure to clean up by closing the recordset in the form Close event....


--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


Hello all!

I have a simple form which also has a subform in it where child records can
be written to a child table based on the parent record/form. I would like
to make it required that the user enter/pick at least one child record (buy
many are possible). As it is now, it's not required.

Is there some simple attribute using Access 2003 in the form or subform I
can set to make a child record on the subform be required instead of
optional?

Think of the tables/forms like shown below As my form is designed now,
someone could enter "Bob" in the form and never pick if he has one or more
certifications...which should be required. Make sense?


StaffTable:
ID | PersonName
---------------------
1 | Bob
2 | John


CertificationsTable
ID | CertName
-----------------------
1 | Juggling
2 | Yoddling


CertStaffMatch
ID | StaffID | CertID

 

[John Vinson]

In the BeforeUpdate of the main form, create a recordsetclone of the
subform. If there are no records, cancel the update event and tell the user
why.

Unfortunately, the main form's BeforeUpdate event will fire the
instant the user sets focus to any subform control. The mainform
record must exist in order for subform records to be allowed by
referential integrity. This is actually a vexingly difficult problem
to solve; in the past I've used a form bound to a "scratch" table and
appended the subform records in a subsequent event.


John W. Vinson[MVP]