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

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

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]

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

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)

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]

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]

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

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.