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

"Dirk Goldgar" wrote in message
...
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.

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

(please reply to the newsgroup)

On Jun 2, 9:00*pm, "Dirk Goldgar"
wrote:
wrote in message

...

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, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)

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
...
On Jun 2, 9:00 pm, "Dirk Goldgar"
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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote in message
...


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.

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

(please reply to the newsgroup)

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

wrote in message
...

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 a

(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

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

(please reply to the newsgroup)

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

On Tue, 2 Jun 2009 21:53:56 -0400, "Dirk Goldgar"
wrote:

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

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

John W. Vinson [MVP]

[email snipped] wrote in message
...
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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"John W. Vinson" wrote in message
...
On Tue, 2 Jun 2009 21:53:56 -0400, "Dirk Goldgar"
wrote:

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

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.

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

(please reply to the newsgroup)