Subform doesn't show any records

[HaBe1963]

Hi,

I'm having an issue with MS Access 2010 which drives me crazy.
I do have a form with some subforms. The subforms are not linked to the main form, but their recordsource queries on some fields on the mainform.
So far so good. If I change the values of the fields on the mainform the subforms are showing the correct records.

However when I reset the values on the mainform with some VBA code the issue begins.

Let's say one of my subforms has the following sourceobject: Me.[Subformulier].SourceObject = "Form A"


When I reset the mainform I use below code:

Me.[Subformulier].SourceObject = ""
 
..
execute some code
 
..
 
Me.[Subformulier].SourceObject = "Form A"

However my Subform isn't returning any records.
The funny thing is however when I open the subform it is showing exactly the records I was looking for...

Can anyone help me with this?

[HaBe1963]

OK, looks like I've found the answer elsewhere. It appeared that my mainform was bound to a query were it shouldn't have been.
Removing this bound query did the trick.
Thought I knew MS Access a bit, but appearantly there is still much to learn.

[jforbes]

By setting the SourceObject of the SubForm, you are tell MSAccess what Form to stuff into that SubForm container. If you plan to always use the same SubForm, you might find things work better by leaving the SourceObject alone and instead update the SubForm Filter to hide and show the records you are interested in.

This is an example of using this approach.

Private Sub cboSourceDrawing1_AfterUpdate()
 
    Dim sDrawingName As String
 
    sDrawingName = Nz(Me.cboSourceDrawing1.Value, "")
 
    If Len(sDrawingName) > 0 Then
        Me.Source1Sub.Form.Filter = "DocNumber='" & sDrawingName & "' AND DocType='Drawing'"
        Me.Source1Sub.Form.FilterOn = True
    Else
        Me.Source1Sub.Form.Filter = "1=2"
    End If
 
End Sub

The SubForm has had it's LinkMasterFields and LinkChildFields cleared out as that is often not needed for this approach. On the Form, when the user selects a different drawing in the cboSourceDrawing1 ComboBox, the SubForm is Filtered to only show records associated with that Drawing. If they clear out the ComboBox, the Filter is set to a permanent False condition, which will hide all the records and show nothing in the SubForm.

This is just an option, you can take it or leave it.