4GvnNot4Gotn
asked on
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_Det ail!sfInve stigation_ Matrix.For m!Investig ation_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! [Investiga tion_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
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_Det
If (intInvID) > 0 And Not IsNull(Me.Investigation_ID
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
Me.sfInvestigation_Matrix!
DoEvents
Me.sfInvestigation_Matrix.
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
ASKER
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.
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.
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..
that is where you are getting the error from..
ASKER
It is the main form footer that was set to visible = No. It also held the sub form.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
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