Testing Subform Records to make button visible

  • Thread starter Thread starter Jochen
  • Start date Start date
J

Jochen

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.
 
Back
Top