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 |
#1
|
|||
|
|||
Code (or Design) Problem
Enter Parameter Value zCategories.Category Private Sub cboShowCategory_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE zCategories.Category = """ & Me.cboShowCategory & """;" MsgBox strSQL Me.RecordSource = strSQL End Sub |
#2
|
|||
|
|||
Code (or Design) Problem
Whoops, sorry about that, I hit the enter key by mistake before I was
ready to send. I'll repost here. When the procedure below is executed, the value of strSQL looks to be exactly what is shown in the procedure, except that Me.cboShowCategory is = "Beef" But then before Me.RecordSource = strSQL is executed, I get the following dialog box: Enter Parameter Value zCategories.Category Can someone explain whats wrong? Private Sub cboShowCategory_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE zCategories.Category = """ & Me.cboShowCategory & """;" MsgBox strSQL Me.RecordSource = strSQL End Sub |
#3
|
|||
|
|||
Code (or Design) Problem
On Sun, 7 Feb 2010 16:47:11 -0800 (PST), iamnu wrote:
Whoops, sorry about that, I hit the enter key by mistake before I was ready to send. I'll repost here. When the procedure below is executed, the value of strSQL looks to be exactly what is shown in the procedure, except that Me.cboShowCategory is = "Beef" But then before Me.RecordSource = strSQL is executed, I get the following dialog box: Enter Parameter Value zCategories.Category Can someone explain whats wrong? Private Sub cboShowCategory_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE zCategories.Category = """ & Me.cboShowCategory & """;" MsgBox strSQL Me.RecordSource = strSQL End Sub You don't have a table named zCategories in your FROM clause. Access has no idea what zCategories.Category might be, so it's prompting for it. Do you mean WHERE tblRecipeCategories.Category = ... instead? -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Code (or Design) Problem
"iamnu" wrote in message
... Whoops, sorry about that, I hit the enter key by mistake before I was ready to send. I'll repost here. When the procedure below is executed, the value of strSQL looks to be exactly what is shown in the procedure, except that Me.cboShowCategory is = "Beef" But then before Me.RecordSource = strSQL is executed, I get the following dialog box: Enter Parameter Value zCategories.Category Can someone explain whats wrong? Private Sub cboShowCategory_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE zCategories.Category = """ & Me.cboShowCategory & """;" MsgBox strSQL Me.RecordSource = strSQL End Sub I would wager that the dialog box is displayed *when* the line "Me.RecordSource = strSQL" is executed, not before that. The reason for the message is that your SQL statement refers to a table/field name, "zCategories.Category", but no table named "zCategories" is participating in the query. Hence the name "zCategories.Category" is not meaningful within the query, so Access concludes that it must be a parameter, and prompts you for the value of the parameter. Should "zCategories.Category" really be "tblRecipeCategories.Category"? That would seem reasonable, but only you can say for sure. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#5
|
|||
|
|||
Code (or Design) Problem
You both are suggesting to me that my design may be flawed. Here is
some more information that may help you to help me. tblRecipes...........Table RecipeID AutoNumber There are no other references to other tables tblRecipeCategories....Table tblCategoryID AutoNumber RecipeID Number CategoryID Number...Row Source = SELECT [zCategories]. [CategoryID], [zCategories].[Category] FROM [zCategories] zCategories..........Table CategoryID AutoNumber Category Text Relationships a tblRecipeCategoriestblRecipes tblRecipeCategorieszCategories I don't know what to do. Does this information help you? Dirk Goldgar wrote: "iamnu" wrote in message ... Whoops, sorry about that, I hit the enter key by mistake before I was ready to send. I'll repost here. When the procedure below is executed, the value of strSQL looks to be exactly what is shown in the procedure, except that Me.cboShowCategory is = "Beef" But then before Me.RecordSource = strSQL is executed, I get the following dialog box: Enter Parameter Value zCategories.Category Can someone explain whats wrong? Private Sub cboShowCategory_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE zCategories.Category = """ & Me.cboShowCategory & """;" MsgBox strSQL Me.RecordSource = strSQL End Sub I would wager that the dialog box is displayed *when* the line "Me.RecordSource = strSQL" is executed, not before that. The reason for the message is that your SQL statement refers to a table/field name, "zCategories.Category", but no table named "zCategories" is participating in the query. Hence the name "zCategories.Category" is not meaningful within the query, so Access concludes that it must be a parameter, and prompts you for the value of the parameter. Should "zCategories.Category" really be "tblRecipeCategories.Category"? That would seem reasonable, but only you can say for sure. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#6
|
|||
|
|||
Code (or Design) Problem
On Sun, 7 Feb 2010 18:19:34 -0800 (PST), iamnu wrote:
You both are suggesting to me that my design may be flawed. Here is some more information that may help you to help me. tblRecipes...........Table RecipeID AutoNumber There are no other references to other tables tblRecipeCategories....Table tblCategoryID AutoNumber RecipeID Number CategoryID Number...Row Source = SELECT [zCategories]. [CategoryID], [zCategories].[Category] FROM [zCategories] zCategories..........Table CategoryID AutoNumber Category Text Relationships a tblRecipeCategoriestblRecipes tblRecipeCategorieszCategories I don't know what to do. Does this information help you? You're another victim of the misdesigned, misleading, infuriating so-called Lookup Wizard. tblRecipeCategories in fact contains a numeric CategoryID. When you look at it, it APPEARS to contain a category name... but it doesn't, only the number! When you construct a query including the table, you need to realize what it actually contains. I think the query you want is strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE tblRecipesCategories.CategoryID = """ & _ Me.cboShowCategory & """;" assuming that the CategoryID is the Bound Column of cboShowCategory. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Code (or Design) Problem
Thanks for helping on this...
I'm still having a problem. The cboShowCategory value is now "1"; in quotes. Should it be in quotes? And I am now getting a Run-time error '2001' You canceled the previous operation. What now? John W. Vinson wrote: On Sun, 7 Feb 2010 18:19:34 -0800 (PST), iamnu wrote: You both are suggesting to me that my design may be flawed. Here is some more information that may help you to help me. tblRecipes...........Table RecipeID AutoNumber There are no other references to other tables tblRecipeCategories....Table tblCategoryID AutoNumber RecipeID Number CategoryID Number...Row Source = SELECT [zCategories]. [CategoryID], [zCategories].[Category] FROM [zCategories] zCategories..........Table CategoryID AutoNumber Category Text Relationships a tblRecipeCategoriestblRecipes tblRecipeCategorieszCategories I don't know what to do. Does this information help you? You're another victim of the misdesigned, misleading, infuriating so-called Lookup Wizard. tblRecipeCategories in fact contains a numeric CategoryID. When you look at it, it APPEARS to contain a category name... but it doesn't, only the number! When you construct a query including the table, you need to realize what it actually contains. I think the query you want is strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE tblRecipesCategories.CategoryID = """ & _ Me.cboShowCategory & """;" assuming that the CategoryID is the Bound Column of cboShowCategory. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Code (or Design) Problem
"iamnu" wrote in message
... Thanks for helping on this... I'm still having a problem. The cboShowCategory value is now "1"; in quotes. Should it be in quotes? No. Try this: strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ "INNER JOIN tblRecipeCategories ON " & _ "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ "WHERE tblRecipesCategories.CategoryID = " & _ Me.cboShowCategory & ";" -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#9
|
|||
|
|||
Code (or Design) Problem
On Feb 7, 9:00*pm, "Dirk Goldgar"
wrote: "iamnu" wrote in message ... Thanks for helping on this... I'm still having a problem. *The cboShowCategory value is now "1"; in quotes. *Should it be in quotes? No. *Try this: * * strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _ * * * * "INNER JOIN tblRecipeCategories ON " & _ * * * * "tblRecipes.RecipeID = tblRecipeCategories.RecipeID " & _ * * * * "WHERE tblRecipesCategories.CategoryID = " & _ * * * * Me.cboShowCategory & ";" -- Dirk Goldgar, MS Access MVP Access tips:www.datagnostics.com/tips.html (please reply to the newsgroup) Hurray! It works. Thank you both so much for your help. |
Thread Tools | |
Display Modes | |
|
|