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

  • Thread starter Thread starter emily3377
  • Start date Start date
E

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
 
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?
 
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
Click to expand...

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.
 
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
 
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
Click to expand...

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.
 
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, 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
Click to expand...

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?
 
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
 
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?
Click to expand...

It sure sounds like it.
Also, I am using Access 2007.
Click to expand...

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 said:
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.
Click to expand...


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.
 
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.
Click to expand...

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
 
<[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!).
Click to expand...

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?
Click to expand...

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.
 
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.
Click to expand...

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.
 
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
 
Well... you were right... disabling the "Sandbox" mode fixed the
problem (grins!).
Hurrah!

HOWEVER (smiles)...
Click to expand...

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?
Click to expand...

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
 
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
 
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.]
Click to expand...

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]
Click to expand...

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.
 
John W. Vinson said:
Thanks, Dirk - I was way off track and would never have found that!
Click to expand...


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.
 
I removed the "equal sign" and brackets from the control source of my
textbox, but I still get 0s as a result. So, I have posted a new
picture for clarification - http://docs.google.com/View?id=dfrm3z55_13c4k47zcx
It shows the property sheets for the main form, the subform, and the
textbox. Did I miss a step?

btw... thank you for the information about my email address. I will
have to figure out how to mask it. When I view these posts I see
only a partial address ([email protected]), and when viewing the
account settings, Google says it masks the addresses on the web
(although they further state that they are NOT masked when they leave
the web). It won't, however, let me make any changes to the
address, so I'm not sure how you inserted the "NO... SPAM" words in
your email... but that's okay - this is not my normal everyday address
(smiles).

Jessi
 
Back
Top