Access can't find subform

[chickenhawk]

I'm using a search engine in VBA to return a new RecordSource to a SubForm, but when I attempt to apply the new SQL code as the RecordSource to the SubForm which contains the data the user wants to see, Access (VBA) returns an error that the form can't be found (even though the form is open and displayed on the screen).

Here is the code:

If Trim(txtMaterialNumber.Text) = "" Then
         MsgBox "Criteria Must Be Entered Before Performing A Search", 
          vbInformation, "Enter Criteria"        
Else
        strRecordSource = SetupOperatingSuppliesSearchQuery(True, False, 
        Forms![OperatingSupplies].Form.RecordSource = strRecordSource
End If
 
 
Forms![OperatingSupplies].Form.RecordSource = strRecordSource
 
Me.Requery

[FishVal]

Is your [OperatingSupplies] form in datasheet view?

[DonRayner]

You need to refer to the subform by refering to it's main form first

Forms!MainForm.Form!OperatingSupplies.RecordSource = strRecordSource
 

[chickenhawk]

No, the main form is a "container" form which has 3 subforms. It is layed-out in an Outlook style with some icons running down the left side, continous form data in the middle form, and then a search engine form at the top.

They are also all independent of each other (in other words, no keys or data fields tying any of the 3 together). Everything will be handled in code.

[FishVal]

Sorry, didn't pay attention to hoe you get reference to it.
Take a look at DonRayner's suggestion.
Form opened in a subform control is not been added to Forms collection.
So, it should be referenced via Form property of the subform control.

Regards,
Fish

[chickenhawk]

Don,

I tried the code you posted and I received an 'Object does not support this property or method' error (after inserting the name of my mainform in place of the code you sent first of course). I can't remember how to do this because I've been away from Access coding for so long.

[chickenhawk]

I figured out a solution:

    Dim frm As Form_OperatingSupplies
 
    Set frm = Form_OperatingSupplies
 
    frm.RecordSource = strRecordSource
 
    frm.Requery

Seems to be working perfectly. Thanks for the help though. It set me on the right path to my solution after realizing that the form needed to be dimensioned as it's own individual Object.

-Chris

[NeoPa]

Referring to Items on a Sub-Form may help with such references.

Welcome to Bytes!

[nairbsnosrap]

Chickenhawk if you ever revisit this post, your own solution sorted my similar problem which had cost me hours. Many thenks!

[NeoPa]

Always nice to hear when people find anything we have to be helpful BP.

Thanks for posting and welcome to Bytes!