Subform reference to main form in code

Question

I am using a number of main forms which have subforms nested in the main forms.  I like to use a main form in single form view and the sub form in data sheet view. I would like to put a control button at the beginning of each row of the data sheet rows in the subform that when clicked will open the single form for that subform record. My problem is how to properly name the reference.  

In one of my Access reference books ti gives the way to reference Sub forms in main forms as:

forms![frnMainform]![frmSubform].forms![FieldName]

All I get is an err message stating -

MS Access can't find form called FrmSubform 

How do you reference subforms in this situation?

Thanks in advance for your help!

Gary

Answer 1

I'm almost 70 years old and memory isn't my strongest ...

That makes two of us.  Now what was I about to say?  Oh yes, I remember....

To illustrate the points Gina and Gary have made, below is a clip from one of my online demo files of a simple form with a subform on a tab page.  I've temporarily added a hidden control to the subreport's footer with a ControlSource property of =Count(*) to count the number of rows currently in the subform.  In the parent form I've added a text box which references this control to show the number in the parent form even when the subform is not visible.

1.  The subform control is selected.  Note from its properties sheet that its name property is sfcEvents.  This differs from the name of the underlying source form object, which is fsubEvents.

2.  Note that the expression used as the ControlSource of the text box in the parent form is =[sfcEvents].[Form].[txtEventCount], i.e. it references the hidden control in the subform via the Form property of the subform control.

Answer 2

A text field needs to be compared to a string value, which must be enclosed in Quotes.

The & "'" at the end concatenates a ' after the string

Note:  Unlike VBA strings, a string evaluated by the Expression Service (queries and control expressions) can be enclosed in either " (double quote) or ' (single quote).  You could get the same thing by using:

   DoCmd.OpenForm "YourFormName", , , "[SubformLinkField]=""" & Me![FieldOnForm] & """"

(When you want to quote a quote, you need to use two quotes inside the outer quotes)

That means the where condition that is used when the form is opened would end up being something like:

   [SubformLinkField]="abc"

Answer 3

Somewhere I'm sure there is an explanation but I don't know it.  I do know that you cannot search for text using the formatting for numbers and visa versa.

Answer 4

Thanks for your response! Question!  Why does make a difference between a numeric or text field? What does the &"" do on the end of the text example?

Thanks in advance for your response.

Answer 5

GaryC_777,

If you are just trying to open a Form for a particular record you can use...

If numeric...

DoCmd.OpenForm "YourFormName", , , "[SubformLinkField]=" & Me![FieldOnForm]

If text...

DoCmd.OpenForm "YourFormName", , , "[SubformLinkField]='" & Me![FieldOnForm] & "'"

If that is not what you trying to do, please clarify.