locked
Unable to Reference a Subforms Properties/Controls RRS feed

  • Question

  • I'm trying to alter the recordsource of a subform at runtime (something I've
    done before) and I'm just not getting why I'm getting an error - "You
    entered an expression that has an invalid reference to the property
    Form/Report." The code craps out where I've got the asterik. I snooped
    around trying to reference the controls and properties of the subform via
    the Immediate Window, but no luck. The subform is on tabctl, but its my
    understanding that that shouldn't be a problem.

    Private Sub Form_Open(Cancel As Integer)

        Dim strRecordSource As String

        'Massage the recordsource, the criteria for the subform changes based on
    its usage
    *   If Right(Me.subfrmTrailerActivityComments.Form.RecordSource, 1) = ";"
    Then
            strRecordSource =
    Left(Me.subfrmTrailerActivityComments.Form.RecordSource,
    Len(Me.subfrmTrailerActivityComments.Form.RecordSource) - 1)
        Else
            strRecordSource = Me.subfrmTrailerActivityComments.Form.RecordSource
        End If

        strRecordSource = strRecordSource & "WHERE lngTrailerActivityHeaderId =
    me.parent.lngTrailerActivityHeaderId ORDER BY lngCommentId"
        Me.subfrmTrailerActivityComments.Form.RecordSource = strRecordSource
        Me.subfrmTrailerActivityComments.Form.lstComments.RowSource =
    strRecordSource
        Me.subfrmTrailerActivityComments.Form.lstComments.Requery
        Me.subfrmTrailerActivityComments.Requery

    End Sub

    ?err.Number
     2455
    ?err.Description
    You entered an expression that has an invalid reference to the property
    Form/Report.
    ?me.controls("subfrmTrailerActivityComments").name
    subfrmTrailerActivityComments
    ?me.Controls("subfrmTrailerActivityComments").form.controls.count
    [Error]
    ?me.Controls("subfrmTrailerActivityComments").controltype
     112
    ?acSubform
     112
    ?[Forms]![frmTrailerActivity_ByShow]![subfrmTrailerActivityComments].form!lstComments.controltype
    [Error]


    David H
    Monday, May 31, 2010 5:55 PM

Answers

  • Nope the error is breaking at run-time. Its somewhat of a moot point as I'm going with an approach that places the burden on the subform and not the parent, but I'm still curious as to what's going on. I should be able to access the .RecordSource property at run-time at least but I"m not able to do so.

    With the form open, the following still produced the same error.

    Set frm = forms(0)
    ?frm.subfrmTrailerActivityComments.form.recordsource


    David H


    This works for me in a test mdb I have.

    [code]    Dim frm As Form
       
        Set frm = Forms!frmApplication_Main
        Debug.Print frm.frmDeliverables_sub.Form.RecordSource
       
        Set frm = Nothing
    [/code]

    Put a watch on frm and see what you get in the watch window.


    Bill Mosca www.ThatllDoIT.com
    • Marked as answer by Ji.Zhou Monday, June 7, 2010 3:31 AM
    Wednesday, June 2, 2010 7:16 PM

All replies

  • You may have not saved the design, or the form may be still in design view.

     

    The following syntax works, in Access 2007

     

    ? FORMS!formName!subformControlName.FORM.RecordSource

     

    All cap words are keyword to type as they are. You have to use the control name, not the form filling the source object property of the sub-form control (they generally both share the same name, though).

     

     

    Monday, May 31, 2010 7:21 PM
  • Nope the error is breaking at run-time. Its somewhat of a moot point as I'm going with an approach that places the burden on the subform and not the parent, but I'm still curious as to what's going on. I should be able to access the .RecordSource property at run-time at least but I"m not able to do so.

    With the form open, the following still produced the same error.

    Set frm = forms(0)
    ?frm.subfrmTrailerActivityComments.form.recordsource


    David H
    Monday, May 31, 2010 8:45 PM
  • Nope the error is breaking at run-time. Its somewhat of a moot point as I'm going with an approach that places the burden on the subform and not the parent, but I'm still curious as to what's going on. I should be able to access the .RecordSource property at run-time at least but I"m not able to do so.

    With the form open, the following still produced the same error.

    Set frm = forms(0)
    ?frm.subfrmTrailerActivityComments.form.recordsource


    David H


    This works for me in a test mdb I have.

    [code]    Dim frm As Form
       
        Set frm = Forms!frmApplication_Main
        Debug.Print frm.frmDeliverables_sub.Form.RecordSource
       
        Set frm = Nothing
    [/code]

    Put a watch on frm and see what you get in the watch window.


    Bill Mosca www.ThatllDoIT.com
    • Marked as answer by Ji.Zhou Monday, June 7, 2010 3:31 AM
    Wednesday, June 2, 2010 7:16 PM
  • Its just fricken odd because things do work going up the chain via code in the subform. (me.parent.name). Its now a moot point as I did shift responsibility to the subform to update itself as opposed to the parent form manipulating it.
    David H
    Thursday, June 3, 2010 12:08 AM
  • You don't mention what version (and likely not too important). Is there any possibility that the source object of the sub-form control is messed up?

     

    ?me.controls("subfrmTrailerActivityComments").name
    subfrmTrailerActivityComments?me.Controls("subfrmTrailerActivityComments").controltype
     112
    ?acSubform
     112


    The above exact behavior occurs if I drop in a sub-form control on to my form, but the control does not have a legitimate source object. The name property you display as above is simply going to display the name of the sub-form control, NOT the sub-form. In your above code snips, clearly the sub-control type of 112 exists. However, the rest of your code behavior suggests that the control does not have a legal sub-form object setting.

     

    It not clear if you're setting the sub-form source object at load time. Keep in mind that also as a general rule the data value of controls cannot be set in the forms on-open event. (it is too soon of a event, and that event has a cancel option). So, on-open is too soon to modify values of controls. You still should be able to examine the sub-form control values, but I would place your code in the forms on-load that messes with its settings, since really the on-open event is really for checking if you going to cancel the form load event. If you cancel a form open, then your setup and initializing code that belongs in the on-load event will not need to run, nor will it run if you cancel the form load.

     

    So, setup and maintains code in a form belongs in the on-load, not the on-open (it just too soon of a event to mess with things).

     

    As mentioned me.contorls("some name").name will ALWAYS work, even if you deleted the actual sub-form that the control points too.

     

    I would check the source object setting of that sub-form control. While the on-open issue is likely not necessary your problem, you code should a general rule of thumb be placed in the forms on-load event, saving the on-open event for code that could potentially cancel the form load and display of the form.

     

    Albert D. Kallal

    Edmonton, Alberta Canada

    Thursday, June 3, 2010 12:15 AM
  • Put a Debug.Print "<form name>" in the load event of the form and its sub forms to see what has loaded at the point the error occurs.  I suspect you are referencing a subform before it is loaded.
    Friday, June 4, 2010 10:45 PM
  • jPerhaps you are setting the source object for the subform using code, is it possible that there is not any source object in the subform control this early ( still in form open event)?

    I would use this statement

    Debug.Print Me.subfrmTrailerActivityComments.SourceObject

    in the line just before

    *   If Right(Me.subfrmTrailerActivityComments.Form.RecordSource, 1) = ";"
    Then
    to check .

    Otherwise check correct names for controls, or else assume some subtle corruption is happening.


    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    Monday, June 7, 2010 3:58 AM
  • I am having the same excat problem but with setting a macro to go to the subform... I know I have it right but notta...

    Bruce

     

    Monday, September 20, 2010 4:37 PM
  • "BizkitBR" wrote in message news:d2a659bc-f064-4ac9-847e-fc488b4892fa@communitybridge.codeplex.com...

    I am having the same excat problem but with setting a macro to go to the subform... I know I have it right but notta...

    Bruce

     

    You don�??t mention what version. However, as general rule, you set the focus FIRST to the sub-form control (note again, I said sub-form control, as this represents an instance of the actual form used for the sub-form and they DO NOT necessary have the same name).
     
    After you set the focus to the sub-form, then set the focus to the control in question.
     
    While in both cases we have to reference the sub-form in our code, moving focus to the control is a VERY different question as compared to referencing the value of a control (in the case of referring controls, you don�??t have to do the two step process in terms of setting the focus).
     
    So, execute two commands. The 1st one sets the focus to the sub-form control. You THEN can execute a set focus to change the focus within that sub-form.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    Monday, September 20, 2010 5:15 PM
  • "BizkitBR" wrote in message news:d2a659bc-f064-4ac9-847e-fc488b4892fa@communitybridge.codeplex.com...

    I am having the same excat problem but with setting a macro to go to the subform... I know I have it right but notta...

    Bruce

     

    You don�??t mention what version. However, as general rule, you set the focus FIRST to the sub-form control (note again, I said sub-form control, as this represents an instance of the actual form used for the sub-form and they DO NOT necessary have the same name).
     
    After you set the focus to the sub-form, then set the focus to the control in question.
     
    While in both cases we have to reference the sub-form in our code, moving focus to the control is a VERY different question as compared to referencing the value of a control (in the case of referring controls, you don�??t have to do the two step process in terms of setting the focus).
     
    So, execute two commands. The 1st one sets the focus to the sub-form control. You THEN can execute a set focus to change the focus within that sub-form.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com

    Back 4-5 months ago when I started the thread... I was working with a generic subform where I needed to alter the recordsource when the parent was loaded. Don't ask me what happened. I don't remember 4 or 5 weeks ago.

    David H
    Tuesday, September 21, 2010 12:21 AM
  • "David Holley" wrote in message news:3df2b1b3-ccbe-4c76-8b7b-b17dc82f10ea@communitybridge.codeplex.com...
    Back 4-5 months ago when I started the thread... I was working with a generic subform where I needed to alter the recordsource when the parent was loaded. Don't ask me what happened. I don't remember 4 or 5 weeks ago.

    David H
    No problem here. The other poster jumped in a a question right in the middle of this and thus "bumped" the post back into action. So, we were answering that other persons post. In fairness, the poster was likely trying to be kind and not start a new question, but I think it would have been better if a new question was started in place of hanging off of your original question.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    Tuesday, September 21, 2010 5:45 PM
  • I have definitely seen this behavior before when Access has problems when resolving the parent<->child recordset relationship. This typiclaly happens most often on 3 occasions:

    1) when initalizing/loading the form and subform

    2) when the parent form navigates to a new (empty) record

    3) when the parent/child recordsets are "jagged" and not all parents have child records, and one navigates to a parent record where no childredn yet exist.

    4) (special case here) when VBA code disconnects the subform control's subform

    In these scenarios, the entire child subform (especially if it has form.allowadditions = false set) can get set to Nothing on you.

    It is due to this that I developed the defensive coding habit of always checking on the existence of the subform before attempting to use or reference anything within it. i.e.

    dim oFrm as form_MySubFormClassNameHere

    set oFrm = Me.MysubformControl.form

    if not (oFrm is nothing) then

      'code here to reference/update the subform's controls

    Else   'optional

      'code here to handle the case that the subform is invalid

    End If

    ' do NOT forget to clean up your object variables...

    'or.. "How many time have I told you to put your toys away when you're done playing with them!??"

    set oFRM = nothing

     

    Tuesday, September 21, 2010 7:31 PM
  • I had this problem. Look at the subform in design view and you might find it has been saved with a recordsource that doesn't exist. It happens by accident when you set the recordsource dynamically and then inadvertantly save it.

    Wednesday, November 21, 2012 1:46 AM