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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|