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

[emil...@gmail.com]

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

<emil...@gmail.com> wrote in message
news:93ece27d-eae8-4a68...@s21g2000vbb.googlegroups.com...

[emil...@gmail.com]

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]

<[email snipped]> wrote in message
news:93ece27d-eae8-4a68...@s21g2000vbb.googlegroups.com...

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

[emil...@gmail.com]

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]

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.

--

John W. Vinson [MVP]

[emil...@gmail.com]

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, 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?

[emil...@gmail.com]

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]

<emil...@gmail.com> wrote in message
news:d7eb0358-e24f-4165...@r33g2000yqn.googlegroups.com...

> 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]

"Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in message
news:u6fcka%234JH...@TK2MSFTNGP04.phx.gbl...

> 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.

[emil...@gmail.com]

On Jun 2, 9:00 pm, "Dirk Goldgar"
<d...@NOdataSPAMgnostics.com.invalid> wrote:
> <emily3...@gmail.com> 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!). 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
news:27ddcfbe-4a35-4ed1...@y17g2000yqn.googlegroups.com...

On Jun 2, 9:00 pm, "Dirk Goldgar"
<d...@NOdataSPAMgnostics.com.invalid> wrote:
>>
>> Does VBA code run in this database? Is it in a trusted location? Do you
>> have Sandbox Mode enabled?
>

> 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]

"Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in message

news:uymSrh%234JH...@TK2MSFTNGP06.phx.gbl...

>
> 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.

[emil...@gmail.com]

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]

<emil...@gmail.com> wrote in message
news:026e3a43-48ce-4d36...@j32g2000yqh.googlegroups.com...

>
> 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

[emil...@gmail.com]

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]

On Tue, 2 Jun 2009 21:53:56 -0400, "Dirk Goldgar"
<d...@NOdataSPAMgnostics.com.invalid> wrote:

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

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

[Dirk Goldgar]

<[email snipped]> wrote in message

news:3cf3336e-6d24-4d65...@r33g2000yqn.googlegroups.com...

> 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]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:hgrb25hgp6j8c40kt...@4ax.com...

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.

[emil...@gmail.com]

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 (emily3...@gmail.com), 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

[Dirk Goldgar]

<emily[...snipped...]@gmail.com> wrote in message
news:4bda3182-48e6-4152...@b1g2000vbc.googlegroups.com...

>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?

I don't see anything wrong there, but I may have made a mistake myself
somewhere in my suggested SQL for the recordsource. Did I get the field and
table names correct? What happens when you open the recordsource query
directly as a datasheet? Note: I don't mean opening the form as a
datasheet, but rather clicking the build button (caption "...") next to the
Record Source property to edit the query, and then in the query designer,
flipping into datasheet view. Does that give an error? Does it result in a
0 for the IngredientCount field, for all records?

I'm assuming that there are in fact child records in tblRecipeIngredients
for the records in tblRecipes. If not, of course you would get zeros in the
IngredientCount field.

> 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 (emily3...@gmail.com), 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...

You're working through Google Groups? I don't know if/how you can do it
when using their web interface. I use Windows Mail's newsreader function
instead, which lets me set this property.

> but that's okay - this is not my normal everyday address

Good; then you won't mind when you have to throw it away.

[emil...@gmail.com]

> Did I get the field and table names correct?  

I have posted a picture of my database relationships for your
reference because I am not sure which fields your SQL query needs
since it references BOTH tblRecipes and tblRecipeIngredients, and both
tables contain a recipe ID field. It is called "fldRI_RecipeID" in
tblRecipeIngredients and the one in tblRecipes is called
"fldRecipeID." You can view the database table relationships here:
http://docs.google.com/View?id=dfrm3z55_15fjbcbvdz

> What happens when you open the recordsource query directly as a datasheet?  Does that give an error?  

I did this, and the field in datasheet view contained all 0s.
**BUT**.... then I closed and re-opened the database. This time,
when I opened the form the "Number of Ingredients" textbox results in
#Error, and a separate message box persists that says: "The expression
you entered as a query parameter produced this error: 'Object or class
does not support the set of events'"

>I'm assuming that there are in fact child records in tblRecipeIngredients for the records in tblRecipes.  

Yes.

I am not sure why it didn't give the error message at first. Maybe
it is because I had not yet closed and reopened the database????

Jessi

[Dirk Goldgar]

<emily[snipped]@gmail.com> wrote in message
news:3672df80-abe3-492d...@b9g2000yqm.googlegroups.com...

>> Did I get the field and table names correct?
>
> I have posted a picture of my database relationships for your reference
> because I am not sure which fields your SQL query needs since it
> references BOTH tblRecipes and tblRecipeIngredients, and both tables
> contain a recipe ID field.

Thank you, that's very helpful.

> It is called "fldRI_RecipeID" in tblRecipeIngredients and the one in
> tblRecipes is called "fldRecipeID."

And that is the source of the problem; or at least, *a* problem. The SQL
of the query should be amended to:

SELECT
DCount("*","tblRecipeIngredients",

"fldRI_RecipeID=" & Nz(fldRecipeID,0))

AS IngredientCount,
*
FROM tblRecipes;

That is to say, return all fields of all records in tblRecipes, plus a
calculated field for each record which is a count of all records in
tblRecipeIngredients where fldRI_RecipeID in that table equals fldRecipeID
in tblRecipes.

>> What happens when you open the recordsource query directly as a
>> datasheet? Does that give an error?
>
> I did this, and the field in datasheet view contained all 0s. **BUT**....
> then I closed and re-opened the database. This time, when I opened the
> form the "Number of Ingredients" textbox results in #Error, and a separate
> message box persists that says: "The expression you entered as a query
> parameter produced this error: 'Object or class does not support the set
> of events'"

That's kind of an odd error message for you to get, but Access 2007 seems to
have changed some of the standard error messages generated by particular
sorts of errors. The referemce to a "query parameter" is a clue that Access
doesn't recognize one of the names in the DCount() expression, since any
unrecognized name in a query is assumed to be a parameter.

> I am not sure why it didn't give the error message at first. Maybe it is
> because I had not yet closed and reopened the database????

I don't know, either, but try the revised query above, first checking to
see if it works when opened directly as a datasheet.

[JessiRight77]

Hurray!!! You are too cool... this fixed the problem with the error
message. And.... it also lets me do a "Filter by Form" search!

One more minor question: Do you know whether there is any way to
specify this textbox as a number field though? When I try to filter
recipes by those with less than 5 ingredients (ie., <5), it also
returns anything more than 9 because it sorts the "1" in the numbers
10, 11, 12, etc. in front of 5. I looked on the "Data" tab of the
textbook, but the only type field I saw was between plain text and
rich text.

Just out of curiosity (to help me understand in the future)... which
part of your query instructed to return ALL records of tblRecipes...
the sole asterisk (*)???

THANKS!!!

Jessi

[emil...@gmail.com]

[Dirk Goldgar]

<emily[...]@gmail.com> wrote in message
news:d0445ecd-fc14-4ee8...@q14g2000vbn.googlegroups.com...

> Hurray!!! You are too cool... this fixed the problem with the error
> message. And.... it also lets me do a "Filter by Form" search!

Excellent!

> One more minor question: Do you know whether there is any way to
> specify this textbox as a number field though? When I try to filter
> recipes by those with less than 5 ingredients (ie., <5), it also
> returns anything more than 9 because it sorts the "1" in the numbers
> 10, 11, 12, etc. in front of 5. I looked on the "Data" tab of the
> textbook, but the only type field I saw was between plain text and
> rich text.

I'm guessing that's because the query engine doesn't know what type of value
will be returned by DCount(), so it uses the default type of Text. Let's
try forcing the value to a Long, and see if that helps:

SELECT
CLng(DCount("*","tblRecipeIngredients",
"fldRI_RecipeID=" & Nz(fldRecipeID,0)))

AS IngredientCount,
*
FROM tblRecipes;

> Just out of curiosity (to help me understand in the future)... which

> part of your query instructed to return ALL records of tblRecipes...
> the sole asterisk (*)???

No, the asterisk tells it to return all fields of each record. The
instruction to return all records is implied by the absence of a WHERE
clause, which would restrict the records returned. No WHERE clause = return
all records.

[emil...@gmail.com]

OK... forcing a conversion to a Long value worked! I cannot thank
you enough... I really wanted to be able to save money by using fewer
ingredients, and I just couldn't get the filter to work (smiles).

Thanks SOOOO much! I have learned a few things.

Jessi

[Dirk Goldgar]

<emily[...]@gmail.com> wrote in message

news:b774cc86-c782-4f8f...@u10g2000vbd.googlegroups.com...

You're welcome.