Testing Subform Records to make button visible

I have a form with a subform
The subform can contain multiple records.
I want to test all the records in the subform
If the yes/no field "appointment" is true in one or more records a button
should be made visible on the main form.

How do I test the records in a subform?

Use the Current event of the main form, to DLookup() the primary key value
of the related table in the subform where the yes/no field is true.

This kind of thing
Private Sub Form_Current()
Dim strWhere As String
Dim bAppointmentsExist as Boolean
If Not Me.NewRecord Then
strWhere = "(Appointment False) AND (ForeignID = " & Me.MainID & ")"
bAppointmentsExist = Not IsNull(DLookup("SubID", "Table2", strWhere))
End If
With Me.Command1
If .Enabled bAppointmentsExist Then
.Enabled = bAppointmentsExist
End If
End With
End Sub

Add error handling, in case the button has focus when you move record.

You may also want to use the subform's Form_AfterUpdate and
Form_AfterDelConfirm events to keep the button's state correct as records
are added to, removed from, and edited in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Jochen" wrote in message
...
I have a form with a subform
The subform can contain multiple records.
I want to test all the records in the subform
If the yes/no field "appointment" is true in one or more records a button
should be made visible on the main form.

How do I test the records in a subform?