Solved

Access 2013 - Call subform fields

Posted on 2013-05-29
7
870 Views
Last Modified: 2013-07-28
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
0
Comment
Question by:4GvnNot4Gotn
  • 4
  • 3
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39205672
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
 

Author Comment

by:4GvnNot4Gotn
ID: 39205868
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39205891
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:4GvnNot4Gotn
ID: 39205912
It is the main form footer that was set to visible = No. It also held the sub form.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39205916
try setting the visible = yes and test your codes.
0
 

Author Comment

by:4GvnNot4Gotn
ID: 39205977
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
 

Author Closing Comment

by:4GvnNot4Gotn
ID: 39362700
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now