How to refer to a subform's properties from a main form

[Gordon Padwick]

I’m still having a problem with using a combo box in a main form to select records to be displayed in a subform. I’ve tried code suggested in several sources, but it doesn’t work.
In “Filter records based on a combo box selection in Access 2004/XP/2000/97,” Microsoft provides an example of a database that has two forms: frmCustomers (main form) and frmCustomers_su b (subform). Microsoft suggests this code for a main form to set a filter for a subform’s recordset:

   Dim LSQL As String
   LSQL = “select * from Customers”
   LSQL = LSQL & “ where CustomerID = ‘” & cboSelected & “’”
   Form_frmCustomers-sub.RecordSource = LSQL

The example works. However, it doesn’t work when I attempt to use similar code in my application. The problem in my application occurs in the fourth line of the code that appears to refer to a form named Form_frmCustome rs_sub. There’s no form with that name in Microsoft’s database. I assume this way of referring to a subform is a special syntax for subforms. However, I cannot find any explanation of this format in Access Help, in on-line sources, or in any of the Access books I have.
By setting a breakpoint in the code, I’ve tried several ways to access a subform’s properties in the Immediate window. When I use:
Form_Book_Subfo rm.RecordSource
I get the error message ‘Run-time error 424. Object required.’
The format that works to access the RecordSource property of a subform named Book Subform is:
Me.Book_Subform .Form.RecordSou rce
After setting the property in the Immediate window, I can query the property (also in the Immediate window) to confirm that it has been correctly set. After incorporating the same format in the VBA code, and making a selection in the combo box on the main form, I still don’t see the correct records displayed in the subform, even after I requery the subform.
I’ll appreciate any help in these matters.
Gordon Padwick

[Stewart Ross]

Hi. When you refer to a subform that is on a main form you cannot use its own name without the main form reference - so you can't use form_book_subfo rm on its own, for instance.

You pointed out that me.book_subform .form worked - and this is just shorthand for

Forms!Name_Of_Your_Main_Form!Name_Of_Your_Subform_On_The_Main_form.Form

What this all means is that to change the recordsource of your subform you must either do so from the main form itself, in which case you can use a statement like Me.SubformName. Form.Recordsour ce = <some sql>, or you must specify the name of the main form and qualify it to include the name of the subform control as named on the main form itself.

This last point is important; if you have a subform called, say, frmBook_Subform , and you include it on the main form as a control called Book_Subform, it is the control as named on the main form that you must reference, e.g.

Forms!Book_Subform.Form.Recordsource = <some sql statement>

You don't mention what the name of your main form is, so replace it in the example below if you want to set a different recordsource programmaticall y for your subform:

forms!YourMainFormName!Book_SubForm.Recordsource = LSQL

We have an Insights article on how to refer to items on a subform, which I've linked here for you.

-Stewart