I have a membership database which has family membership, so more than one
person can be associated with one membership. The main input form has
address etc and the names of people are entered via a sub-form. My problem
is that now and again someone enters a membership without any names. I would
like to stop this action and display a message; a bit beyond my capability
at the moment!
Colin
Well, this is pretty tricky. When you open the Form and move to a new
record to enter a new family, the subform MUST be blank (as there is
no family to have members assigned) - so the situation "empty subform"
or even "no family members assigned" is in fact a legal situation.
It's even legal after a family record has been saved, since that
saving is (must be!) done before you can start entering data on the
subform; so you can't use the mainform's BeforeUpdate event to check.
I'd suggest running a "Unmatched" query in the mainform's Close event
to detect any records which violate the rules:
SELECT Family.FamilyID
FROM Families
LEFT JOIN People
ON People.FamilyID = Families.FamilyID
WHERE People.FamilyID IS NULL;
If this query returns any family ID's, those are the erroneous ones.
You could write code... ah what the heck...
Private Sub Form_Close(Cancel as Integer)
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT Family.FamilyID FROM Families LEFT JOIN People " _
& "ON People.FamilyID = Families.FamilyID " _
& "WHERE People.FamilyID IS NULL;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, vbOpenSnapshot)
If rs.RecordCount > 0 Then
' there were families with no members
MsgBox "All families must have at least one member!", vbOKOnly
Cancel = True
Me.Recordsource = strSQL ' set the Form to display the bad records
Else
Cancel = False ' no bad records, let the form close
End If
End Sub
Alternatively you could just navigate the form to the first FamilyID
in the recordset (and check for other erroneous records next time the
user tries to close).
John W. Vinson[MVP]
