Access 2013 - Call subform fields

I have a form that I use for collecting information about investigations (frmInvestigation_Detail)

There is a subform in the footer (footer section .visible property = No).  This form is called "sfInvestigation_Matrix"

The Parent/Child link is a field called [Investigation_ID]

Using a graphic with a transparent button, I need to evaluate the code below in the "on-click" event of the transparent button (see attached graphic - "M1" button is the transparent button)

Here is my challenge -

When I click on the button, I get an error message saying "You entered an expression that has an invalid reference to the property Form/Report".

I've verified that the subform and field names are valid and that there is a value in the sub-form field [investigation_id]

What am I missing?
.......................................

Private Sub M1_Click()
Dim intInvID As Integer

On Error GoTo M1_Click_Err

intInvID = Forms!frmInvestigation_Detail!sfInvestigation_Matrix.Form!Investigation_ID



    If (intInvID) > 0 And Not IsNull(Me.Investigation_ID) Then
        DoCmd.OpenForm "sfInvMatrix_M1", acNormal, , [Investigation_ID] = Me.Investigation_ID, , , Me.Investigation_ID
        DoEvents
    ElseIf (intInvID) = 0 Or IsNull(intInvID) And Not IsNull(Me.Investigation_ID) Then
        Me.sfInvestigation_Matrix![Investigation_ID] = Me.Investigation_ID
        DoEvents
        Me.sfInvestigation_Matrix.Requery
        DoEvents
        DoCmd.OpenForm "sfInvMatrix_M1", acNormal, , [Investigation_ID] = Me.Investigation_ID, , , Me.Investigation_ID
        DoEvents
    Else
        MsgBox "Please complete the primary investigation before attempting to complete the investigation matrix", vbOKOnly + vbInformation, "Error: Complete Primary Investigation"
        Me.InvestigatedBy.SetFocus
        DoEvents
        Exit Sub
    End If
   
       
   

M1_Click_Exit:
    Exit Sub

M1_Click_Err:
    MsgBox Error$
    Resume M1_Click_Exit

End Sub
Investigation-Matrix.png
4GvnNot4GotnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Rey Obrero (Capricorn1)Commented:
is the name of the subform control "sfInvestigation_Matrix" ?

to verify this...
open form "frmInvestigation_Detail" in design view
- select the outer edge of the subform, to access the subform control
   hit F4
   select the Other tab and look in the Name property
0
 
4GvnNot4GotnAuthor Commented:
It is named sfInvestigation_Matrix.  Check, double-checked, tripled checked.  

I think I found out what is happening but couldn't find where it is published anywhere.  

Although you can reference fields in a footer whose visible property = No, if there is a sub-form in that footer, the visible = no prevents Access from being about to call the fields in the sub-form.  

I tested this by moving the sub-form into the detail section of the form, resizing it to it's smallest possible form factor and then executing the application.  When I did that, everything runs fine.  

This is confusing and not the solution that I was expecting but it is a work around that leaves a small object (which you can't hide behind the graphic) onscreen.
0
 
Rey Obrero (Capricorn1)Commented:
ok.i didn't see that part in your original post that the visible property of the subform is set to NO..

that is where you are getting the error from..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
4GvnNot4GotnAuthor Commented:
It is the main form footer that was set to visible = No. It also held the sub form.
0
 
Rey Obrero (Capricorn1)Commented:
try setting the visible = yes and test your codes.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
4GvnNot4GotnAuthor Commented:
Already did. It works but folds the screen. You can still access individual  objects in a flyer that is hidden but you can't access a sub form and it's fields.
0
 
4GvnNot4GotnAuthor Commented:
This was the key and I found it on my own.  You have to make the subform visible but resize it to its smallest form factor (looks like a square dot).  Take off the tab stop properties so you don't accidentally tab to it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.