Subform does not respond to requery

[NickvdM]

Greeting to all,

I have a situation where I have a parent form, containing a subform. These are basically based on the same table, although I am using a query as the datasource for the subform because it must only display records (from the same table as the parent form,) relevant to the current record in the parent form, if any such records exist. I have tried a couple of things and read some forums and FAQs, but none seem to solve my problem. I've been struggling with this for days now! :'-(

Below is the VBA code I've tried which I though would work. (Some statements are comments. The others are VBA statements which I personally have the idea SHOULD work but doesn't otherwise I wouldn't be posting this.)

If Me.ysnHasComponents Then
' Forms![sfrmPathologyTest].SetFocus (Runs the GotFocus even in subform, but doesn't work
' sfrmPathologyTest.Requery
' sfrmPathologyTests.Form.Refresh
' Me.sfrmPathologyTests.Form.RecordSource = "SELECT * FROM tblPathology WHERE txtIsComponentOf" & _
' "=" & [Forms]![frmPathologyTests]![cboIsComponentOf] & ";" ' This SQL statement format and syntax may be wrong; I've given up on it.


' THESE FOLLOWING TWO STATEMENTS ARE WHAT I BELIEVE SHOULD WORK, BUT IT DOESN'T
sfrmPathologyTests.Form.Requery
sfrmPathologyTests.Visible = True
Else
sfrmPathologyTests.Visible = False
End If

I realise similar questions may have been asked before but the proposed solutions aren't solving my problem.

[Welshgasman]

Just set master & child link fields

[Gicu]

I agree that is the easiest as no code is required but if you want to also hide the subform you need to add code to the Current event of the main form (no need to include the check to the boolean field ysnHasComponents, just check if the subform's recordset has any records):

Me.sfrmPathologyTests.Form.RecordSource = "SELECT * FROM tblPathology WHERE txtIsComponentOf = " & Me.cboIsComponentOf  'choose this line if txtIsComponent is numeric 


Me.sfrmPathologyTests.Form.RecordSource = "SELECT * FROM tblPathology WHERE txtIsComponentOf = '" & Me.cboIsComponentOf & "'"  'choose this line if txtIsComponent is text


Me.sfrmPathologyTests.Visible=Me.sfrmPathologyTests.Form.Recordset.RecordCount>0

Cheers,