Code (or Design) Problem

[iamnu]

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

 

[iamnu]

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

 

[John W. Vinson]

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?

 

[Dirk Goldgar]

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.

 

[iamnu]

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 are:
tblRecipeCategories>tblRecipes
tblRecipeCategories>zCategories

I don't know what to do. Does this information help you?

 

[John W. Vinson]

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 are:
tblRecipeCategories>tblRecipes
tblRecipeCategories>zCategories

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.

 

[iamnu]

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?

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 are:
tblRecipeCategories>tblRecipes
tblRecipeCategories>zCategories

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.

 

[Dirk Goldgar]

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 & ";"

 

[iamnu]

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.