OpenForm for those parent records having chidren

[kalltim]

Access novice needs help! I am sure there is a way to do this. I am wanting to open a form (docmd.openform) where I see only those parent records which has a matching child record. In plain english I am wanting to see only those students [student info] who have come in for an advising appointment [student advising]


My two tables are [student info] and [student advising].
Tables are related in a one [student info] to many [student advising] relationship. Both tables are linked on field [student id]

[Advising Appointment] form has a subform (the many side of the relationship) of [Student Advising subform]

This is what I have tried so far to no avail-

Private Sub Command1_Click()
On Error GoTo SearchRout_Err
‘the check box (check20) gets checked if I want to see only students who have been in for advising
If Check20 = True Then
DoCmd.OpenForm "Advising Appointment", acNormal, "", "[Student Advising subform]![Student Advising]![Student ID] Is Not Null"
Else
DoCmd.OpenForm "Advising Appointment", acNormal, ""

End If
SearchRout_Exit:
Exit Sub
SearchRout_Err:
MsgBox Error$
Resume SearchRout_Exit
End Sub

[June7]

One way is to have a field constructed in the main form RecordSource with an expression that checks the appointment table for records, like:

HasAppt: DLookup("StudentID","Appointment","StudentID=" & [StudentID])

Then use that field as criteria in the WHERE clause:
DoCmd.OpenForm "Advising Appointment", acNormal, "", "HasAppt Is Not Null"

[kalltim]

One way is to have a field constructed in the main form RecordSource with an expression that checks the appointment table for records, like:

HasAppt: DLookup("StudentID","Appointment","StudentID=" & [StudentID])

Then use that field as criteria in the WHERE clause:
DoCmd.OpenForm "Advising Appointment", acNormal, "", "HasAppt Is Not Null"

Okay- I think I understand your solution. In my master form I need to create a field that looks at the subform and checks to see if a matching student ID is found. If it finds one then the new field gets updated with the ID and if not it results in a Null.

Then I do my openform request, testing on this new field.

So essentially I can only filter records based on a parent field and not a child field?

Will give it a whirl.

Thanks for your input!

[June7]

Child cannot command the parent. The subform 'piggy-backs'. So, yes, filter the main form and subform will synchronize.

Technically, the DLookup is not checking the subform because it isn't even open yet. The check is directly on the table.