Semi-easy challenge addressing subform from form

[JohnGunn]

Here's a 'simple' problem (simple in the sense that it's confined to a few lines of code). I have an option button group on the parent form the user can click on to determine what records to show on the subform.

So when s/he clicks the option button, I want to include some code to update the RecordSource of the subform. Sounds easy. In the following code from the Click even of the main form;s option button, I get an error saying "" when I get to the critical line setting the subform;s recordsource.

Any ideas about what I'm missing here? Something about the link fields? I'm loosely following an example from page 210 of F Scott Barker's Access 2002 Power Programming book.

' code from click event of the main form option buttons
Dim strSQL As String

If Me.opgShow = 1 Then
' want full recordsource in subform
strSQL = "SELECT [list of fields] FROM [join condition]"
[Forms]![mainformName]![subformName].[Form].[RecordSource] = strSQL
Else
' want reduced recordsource in subform
strSQL = [list of fields] FROM [join condition] WHERE [expression limiting # rcds in subform]"
[Forms]![mainformName]![subformName].[Form].[RecordSource] = strSQL
End If

TIA

 

[pdx_man]

I'm guessing it is not as simple as this ...

Dim strSQL As String

If Me.opgShow = 1 Then
' want full recordsource in subform
strSQL = "SELECT [list of fields] FROM [join condition]"
[Forms]![mainformName]![subformName].[Form].[RecordSource] = strSQL
Else
' want reduced recordsource in subform
strSQL = "SELECT [list of fields] FROM [join condition] WHERE [expression limiting # rcds in subform]"
[Forms]![mainformName]![subformName].[Form].[RecordSource] = strSQL
End If

Have you tried to use a filter and then turn the filter on?

Is this your actual code?

 

[JohnGunn]

Yes it's my actual code, except for the parts where I abbreviated the fields list and join conditions.

All I want to do is to update the RecordSource of the subform from code executing in the main or parent form. I have designed a number of forms in which that form;s recordsource is updated on the fly from code and these forms work fine.

The addressing scheme I got from Scott Barker's book seems correct. For example, I can display the subform's name using the same syntax: [Forms]![TE-FOmainCombo]![TE-FOsubform].[Form].Name by displaying the name in the immediate window.
But Access is giving me the error message "Run time error 424, Object required" when I run the code I posted...

 

[JohnGunn]

Here's the solution. First let me mention that I DID go looking through past posts for the correct syntax, but a re-try with a better choice of keywords is what lead to the answer.

The post that filled me in is named "Subform: Refreshing Record Display After Delete". The correct syntax for accessing the RecordSource of the subform from the main or parent form is:

'
' code that does work
'
Me.TE_FOsubform.Form.RecordSource = strSQL

'
' code that DOES NOT work
'
[Forms]![TE-FOmain]![TE-FOsubform].[Form].[RecordSource] = strSQL

Of course there may be other ways to refer to it. But Sorrells in the post I mentioned above specifically instructed the guy with the problem NOT to use the bang operator and the brackets. This is a topic that many newcomers are struggling with. I, for one, thought the "!" always works and that the "." only works in some contexts. Looks like I need to study a bit more about how to address objects.

Hope this helps you guys. The effect is quite cool. You click the option button on the main form and the subform's contents expand and shrink in an instant.

 

[RichMorrison]

John,

This is just a guess. The statement

<<
' code that DOES NOT work
'
[Forms]![TE-FOmain]![TE-FOsubform].[Form].[RecordSource] = strSQL
>>

puts brackets around Form and RecordSource which are properties . I think that makes Access think they are names of a field or control. Try it without the brackets around Form and RecordSource.

RichM

 

[JohnGunn]

Dear Rich,

Thanks for clarifying why the original syntax I tried didn't work.

Taking the brackets off .Form and .Recordsource did indeed make it go.

After a lot of frustration for the first few weeks of using Access (because I wished for all the goodies I got used to in Visual FoxPro) I am now seeing how to be productive in Access, with the occasional newbie snag, of course. I really appreciate the help all you people offer to guys like us!

 

[RichMorrison]

John,

Glad it worked. Not all of my suggestions do.

The various object-model syntaxex are confusing, but in most cases of forms, controls, and other Access objects the bang("!") indicates a member of a collection and the dot(".") indicates a property of an object or collection.

RichM