Prev: Command Button to Use Toolbar Find
Next: access
From: Jochen on 2 Apr 2010 04:18
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?
From: Allen Browne on 2 Apr 2010 08:28
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" <Jochen(a)discussions.microsoft.com> wrote in message
news:80694570-588D-489B-88C8-3E52F91638ED(a)microsoft.com...
> 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?

 | 
Pages: 1
Prev: Command Button to Use Toolbar Find
Next: access