A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #21  
Old June 3rd, 2009, 04:51 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
[email protected]
external usenet poster
 
Posts: 24
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

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 ), 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
  #22  
Old June 3rd, 2009, 05:16 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

wrote in message
...
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 ), 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.

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

(please reply to the newsgroup)

  #23  
Old June 4th, 2009, 05:08 AM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
[email protected]
external usenet poster
 
Posts: 24
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

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 he
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
  #24  
Old June 4th, 2009, 03:50 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

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

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

(please reply to the newsgroup)

  #25  
Old June 4th, 2009, 05:35 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
JessiRight77
external usenet poster
 
Posts: 7
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

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
  #26  
Old June 4th, 2009, 06:04 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
[email protected]
external usenet poster
 
Posts: 24
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

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
  #27  
Old June 4th, 2009, 06:20 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

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

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

(please reply to the newsgroup)

  #28  
Old June 4th, 2009, 06:32 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
[email protected]
external usenet poster
 
Posts: 24
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]


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


  #29  
Old June 4th, 2009, 06:38 PM posted to microsoft.public.access.forms,microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Can’t Display Number of SUBFORM Items on MAIN Form... trying to use [RecordCount]

wrote in message
...

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.



You're welcome.

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

(please reply to the newsgroup)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.