Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

[emily3377]

Hello.

I have a form (frmRecipes) that contains a subform
(frmRecipeIngredientsSubform).

I want the total number of ingredients (in the subform) to be
displayed on the main form, so I created a text box
(txtNumberIngredients), set its format to general number, and put the
following line in the Control Source:

=[frmRecipeIngredientsSubform].[Form].[Recordset].[RecordCount]

The result returns a #Name? error, however.

What am I doing wrong? Any hints would be GREATLY appreciated
(smiles).

Jessi

 

[Steve]

Recordset should be RecordsetClone.

Steve

 

[emily3377]

Thank you for your response, Steve.
I changed the Control Source line for the main form's textbox to
RecordsetClone as follows:
=[frmRecipeIngredientsSubform].[Form].[RecordsetClone].[RecordCount]

But I still get the #Name? error... so I'm still not doing something
right. Any ideas?

 

[Dirk Goldgar]

Hello.

I have a form (frmRecipes) that contains a subform
(frmRecipeIngredientsSubform).

I want the total number of ingredients (in the subform) to be
displayed on the main form, so I created a text box
(txtNumberIngredients), set its format to general number, and put the
following line in the Control Source:

=[frmRecipeIngredientsSubform].[Form].[Recordset].[RecordCount]

The result returns a #Name? error, however.

What am I doing wrong? Any hints would be GREATLY appreciated
(smiles).

Jessi

Jessi, I believe that should work if "frmRecipeIngredientsSubform" is the
name of the subform control on the main form, and provided you are using a
version of Access later than Access 97. However, it's possible that the
subform control has a different name than its Source Object form. Your
expression must use the name of the subform control, which may or may not be
the same as its Source Object. Please check the subform control to see if
you have the control's name correct.

 

[emily3377]

Thanks for the followup.

I have published a PICTURE of my form so you can see what I'm talking
about at the following site: http://docs.google.com/View?id=dfrm3z55_9s4scqsf9

While in Design view, I right-clicked on the subform object.
The Name property it says: frmRecipeIngredientsSubform.
The Source Object property says: frmRecipeIngredientsSubform

I am using Access 2007.

So, if I am understanding this correctly, I do have the correct name,
right? Also, I am using Access 2007.

Can you see what my problem is from the picture?

Thanks! (smiles)

Jessi

 

[John W. Vinson]

Thanks for the followup.

I have published a PICTURE of my form so you can see what I'm talking
about at the following site: http://docs.google.com/View?id=dfrm3z55_9s4scqsf9

While in Design view, I right-clicked on the subform object.
The Name property it says: frmRecipeIngredientsSubform.
The Source Object property says: frmRecipeIngredientsSubform

I am using Access 2007.

So, if I am understanding this correctly, I do have the correct name,
right? Also, I am using Access 2007.

Can you see what my problem is from the picture?

Thanks! (smiles)

Jessi

There are two objects involved in a subform: the Subform Control (the "box" on
the mainform containing the subform) and the form object within that control.
The name of the Subform Control - the box - is what you need in the code
syntax; what you're looking at in the image on the webpage is the wrong one,
the name of the form object within that control.

View the main form's Properties, and click the *edge* of the subform. You
should see a Name property - which might be the same as the name of the form
within the control, but need not! - an also a Master Link Field and Child Link
Field property.

 

[emily3377]

OK, sirs... I have re-published the picture so you can see what I've
clicked on to view the properties: http://docs.google.com/View?id=dfrm3z55_11cx28xtf7

I viewed the main form's properties, then clicked on the edge of the
subform... I see a box, and the name is frmRecipeIngredientsSubform.
(Please note that if I clicked the box beside the ruler in the first
picture, the Record Source is tbleRecipeIngredients, but I don't think
this is what I need, right?)

Am I still way off the mark? (sighs).

Thanks,
Jessi

 

[John W. Vinson]

OK, sirs... I have re-published the picture so you can see what I've
clicked on to view the properties: http://docs.google.com/View?id=dfrm3z55_11cx28xtf7

I viewed the main form's properties, then clicked on the edge of the
subform... I see a box, and the name is frmRecipeIngredientsSubform.
(Please note that if I clicked the box beside the ruler in the first
picture, the Record Source is tbleRecipeIngredients, but I don't think
this is what I need, right?)

Am I still way off the mark? (sighs).

Thanks,
Jessi

OK, I've missed a big part of this discussion. It sounds like you've got the
right subform reference but since I don't know the context (or the problem for
that matter!) I'm not sure what problem you're having. I tried both google
groups and www.bing.com to see, but neither turns up your prior messages.

What's going on?

 

[emily3377]

Sure! I have copied and pasted my previous question for your
review... thanks!:

I have a form (frmRecipes) that contains a subform
(frmRecipeIngredientsSubform).

I want the total number of ingredients (in the subform) to be
displayed on the main form, so I created a text box
(txtNumberIngredients), set its format to general number, and put the
following line in the Control Source:

=[frmRecipeIngredientsSubform].[Form].[Recordset].[RecordCount]

The result returns a #Name? error, however.

What am I doing wrong? Any hints would be GREATLY appreciated
(smiles).

Jessi
______________

Please note that after I printed this message, a gentleman told me to
change the command to "RecordsetClone".... which, I did, but I still
get the same #Name? error, so I am doing something else wrong I guess.
______________

Thanks so much for your patience and endurance (laughs!).

Jessi

 

[Dirk Goldgar]

Thanks for the followup.

I have published a PICTURE of my form so you can see what I'm talking
about at the following site:
http://docs.google.com/View?id=dfrm3z55_9s4scqsf9

While in Design view, I right-clicked on the subform object.
The Name property it says: frmRecipeIngredientsSubform.
The Source Object property says: frmRecipeIngredientsSubform

I am using Access 2007.

So, if I am understanding this correctly, I do have the correct name,
right?

It sure sounds like it.

Also, I am using Access 2007.

I've done this with earlier versions of Access, but haven't yet tested with
A2007. It could be a question of either of a couple of security settings:
whether VBA code is allowed, or whether you have Jet Sandbox Mode turned on
(which it is by default).

Does VBA code run in this database? Is it in a trusted location? Do you
have Sandbox Mode enabled?

I'll do some tests with Access 2007, though it's not my main development
environment.

 

[Dirk Goldgar]

Does VBA code run in this database? Is it in a trusted location? Do you
have Sandbox Mode enabled?

I'll do some tests with Access 2007, though it's not my main development
environment.

Hmm. I just built a simple test form & subform in Access 2007, in a trusted
location, with VBA enabled, and Jet Sandbox Mode disabled. And ... it gets
a #Name error just as Jessi's does. So there's something going on here that
is specific to Access 2007; maybe a bug, maybe an intentional change of
behavior.

I'll do some more investigating.

 

[emily3377]

It sure sounds like it.


I've done this with earlier versions of Access, but haven't yet tested with
A2007.  It could be a question of either of a couple of security settings:
whether VBA code is allowed, or whether you have Jet Sandbox Mode turned on
(which it is by default).

Does VBA code run in this database?  Is it in a trusted location?  Doyou
have Sandbox Mode enabled?

I'll do some tests with Access 2007, though it's not my main development
environment.

Hmmmmm....
1. The database is located in a trusted area; and
2. VBA code does work in this database; and
3. Sandbox MODE??? (grins... I had to look this one up!). I
checked the registry and my DWord is 3, which I think means that the
sandbox mode is enabled. Is that a bad thing for this type of
operation?

Also, since we're going down this road... it may be helpful to note
that this particular database was created in Access 2003, but then
converted to 2007.

Thanks so much for your input!

Jessi

 

[Dirk Goldgar]

<[Email Snipped]> wrote in message

Hmmmmm....
1. The database is located in a trusted area; and
2. VBA code does work in this database; and
3. Sandbox MODE??? (grins... I had to look this one up!).

Good going!

I checked the registry and my DWord is 3, which I think means that the
sandbox mode is enabled. Is that a bad thing for this type of operation?

Yes. Contrary to my previous post of my test results, it turns out that I
did have sandbox mode enabled when testing. When I changed the registry key
from 3 (always use sandbox mode) to 2 (use sandbox mode for non-Access
applications, but not for Access), the controlsource expression worked. So
try that with your database. I'll bet it works.

By the way, your original expression, which used the subform's Recordset
property, was fine. There's no need and no reason to change it to
RecordsetClone, though that should also work.

 

[Dirk Goldgar]

Hmm. I just built a simple test form & subform in Access 2007, in a
trusted location, with VBA enabled, and Jet Sandbox Mode disabled. And
... it gets a #Name error just as Jessi's does.

CORRECTION! That test did not have sandbox mode disabled. I was misled by
what the Trust Center seemed to be saying, and I'm not sure now if I
misinterpreted it or if it was just wrong.

Anyway, when I disabled sandbox mode, the expression worked.

 

[emily3377]

Well... you were right... disabling the "Sandbox" mode fixed the
problem (grins!).

HOWEVER (smiles)... the reason I wanted the textbox showing the number
of ingredients on this form was so that I could do a "Filter by Form"
search for those recipes with less than 5 ingredients in them. The
textbox is greyed out when I click the Filter by Form option, though
(sighs).

Can I not filter using a textbox on a form?

Thanks,
Jessi

 

[Dirk Goldgar]

Well... you were right... disabling the "Sandbox" mode fixed the
problem (grins!).
Hurrah!

HOWEVER (smiles)...

Uh oh.

the reason I wanted the textbox showing the number
of ingredients on this form was so that I could do a "Filter by Form"
search for those recipes with less than 5 ingredients in them. The
textbox is greyed out when I click the Filter by Form option, though
(sighs).

Can I not filter using a textbox on a form?

Not using an unbound or calculated text box, no. The filter has to be based
on fields in the form's recordset, and your calculated text box isn't in the
form's recordset.

If you really need to do this, and need to do it via Filter by Form, then
you could add a calculated field to the form's RecordSource query, which
would calculate the number of related records in the subform. For example,
suppose the form's recordsource was originally a table called "tblRecipes",
and the subform's recordsource is a table named "tblRecipeIngredients",
related by a common field, "fldRecipeID". Then you could change the main
form's recordsource to a query like this:

SELECT
*,
DCount("*", "tblRecipeIngredients",
"fldRecipeID=" & Nz(RecipeID, 0))
AS IngredientCount
FROM tblRecipes;

Then you could bind the text box on the main form directly to the calculated
field, [IngredientCount], and you'd be able to filter the form on that.

The only problems with this approach that I can think of offhand are:

(1) You may need to explicitly refresh the form or requery/recalculate
the text box when you add or delete ingredients using the subform. I'm not
sure about this.

and

(2) Using the DCount expression in the form's RecordSource will slow it
down. This may or may not be an issue.

However, if you want the form to be updatable, that's the only way I can
think of.

If this doesn't work out well for you, you could forget about using Filter
by Form, and build your own filter form (or a set of filtering controls in
the form header), and build and apply your own filter. Doing that, it's
easy to create a filter criterion to filter by the number of related
records, without direct reference to the subform or the calculated text box
on the main form; e.g.,

"(SELECT Count(*) FROM tblRecipeIngredients AS I " & _
"WHERE I.fldRecipeID = tblRecipes.fldRecipeID) < " & _
Me.txtFilterIngredientCount

 

[emily3377]

Welllll......... I read your post three times... and I *thought* I
understood the main points. So, I decided to try the first option by
changing the underlying recordsource to a query that creates a
calculated field, and then tying the textbox on the main form to the
calculated field.

In other words, I:

1. Changed the underlying Record Source for the MAIN form FROM
tblRecipes to the following line that I typed directly into the
Properties box beside the Record Source category:
SELECT DCount("*","tblRecipeIngredients","fldRecipeID=" & Nz
(fldRecipeID,0)) AS IngredientCount, * FROM tblRecipes;
[Please note that I changed your original code from "Nz(RecipeID,0)"
to "Nz(fldRecipeID,0)" because I assumed that to be an error after the
form asked me for the RecipeID field.]

2. Then, also on the Main Form, I created a Textbox with the
Control Source of:
=[IngredientCount]

But, I obviously didn't understand completely because the textbox
yields a "0" for every record, and the Filter By Form option is greyed
out.

I'm so sorry... in hindsight, I doubt my little Recipe database is
worth this much of your time.

Feeling guilty now,
Jessi

 

[John W. Vinson]

Anyway, when I disabled sandbox mode, the expression worked.

Thanks, Dirk - I was way off track and would never have found that!

 

[Dirk Goldgar]

Welllll......... I read your post three times... and I *thought* I
understood the main points. So, I decided to try the first option by
changing the underlying recordsource to a query that creates a
calculated field, and then tying the textbox on the main form to the
calculated field.

In other words, I:

1. Changed the underlying Record Source for the MAIN form FROM
tblRecipes to the following line that I typed directly into the
Properties box beside the Record Source category:
SELECT DCount("*","tblRecipeIngredients","fldRecipeID=" & Nz
(fldRecipeID,0)) AS IngredientCount, * FROM tblRecipes;
[Please note that I changed your original code from "Nz(RecipeID,0)"
to "Nz(fldRecipeID,0)" because I assumed that to be an error after the
form asked me for the RecipeID field.]

Yes, you were quite right to change that field name. I was only guessing at
the field and table names, anyway, but I meant that to be "fldRecipeID";
leaving off the "fld" prefix was just an oversight.

2. Then, also on the Main Form, I created a Textbox with the
Control Source of:
=[IngredientCount]

And this was your mistake. You created a calculated cobntrol instead of a
bound control . The Control Source of the text box should be just:

IngredientCount

No equal sign; and no square brackets, either, though they shouldn't
actually hurt anything. Try that and see if it works.

By the way, you are causing yourself trouble by posting to the newsgroups
with a functional, unmasked e-mail address. Spammers trawl the newsgroups
for e-mail addresses. It's a good idea to change your posting address to
something that a computer won't be able to interpret, but that a human can
figure out (if necessary). See my own posting address for an example.

 

[Dirk Goldgar]

Thanks, Dirk - I was way off track and would never have found that!

Yes, you would. <g> It looks like you came in on the middle of this thread,
without seeing the first couple of messages for some reason.