Order By doesn't work

[lmv]

Can someone tell me why the ORDER BY wouldn't work in this code
cboCategoryID value is a number. I have commented it out and then the code
works not alphabetical but when I leave it in... it returns no records. I
have tried & _ at the end of the where statement... ; at the end of the Order
By and without ; ...
And I can't figure it out.
obviously a novice
Thanks!


Private Sub cboCategoryID_AfterUpdate()
Dim ProductName As String

ProductName = "SELECT [ProductsTTL].[ProductID],
[ProductsTTL].[CategoryID], [ProductsTTL].[ProductName],
[ProductsTTL].[ProductName] " & _
"FROM ProductsTTL " & _
"WHERE [CategoryID] = " & Me.cboCategoryID.Value
' "ORDER BY ProductsTTL.ProductName;"

Me.cboProductName.RowSource = ProductName
Me.cboProductName.Requery

End Sub

 

[xRoachx]

You need a space before the ORDER BY clause. The way it reads now, there is
not a space between the category ID and the ORDER BY statement.

 

[Sprinks]

Hi, lmv.

If you're putting the WHERE and ORDER BY clauses on different lines for
readability, you will need & _ at the end of the WHERE line, the ampersand to
concatenate the ORDER BY clause to the ProductName string, and the underscore
to tell the Access compiler that the statement is being continued on the next
line.

If you wish to break up the SELECT statement into multiple lines for
readability, you'll need to do similarly, however, it's fine as it is as long
as you have it on one line. For multiple lines, it would look something like:

ProductName = "SELECT [ProductsTTL].[ProductID], " & _
"[ProductsTTL].[CategoryID],
[ProductsTTL].[ProductName], " & _
"[ProductsTTL].[ProductName] " & _
...etc.

Be careful when concatenating strings to include a space between the various
pieces. Your likely error is that your code does not insert a space between
Me.cboCategoryID.Value and the ORDER BY clause. You've also selected the
ProductName field twice, which won't cause an error, but depending on the
setting of the ColumnWidths property, may cause the field to display twice in
the drop-down list.

Hope that helps.
Sprinks

 

[Ken Sheridan]

I think you just need to insert a space before the ORDER BY clause. At
present, because the preceding line does not finish with a literal string the
value of the cboCategoryID control will be immediately followed by ORDER BY
when the expression evaluates:

ProductName = "SELECT [ProductsTTL].[ProductID]," & _
"[ProductsTTL].[CategoryID], [ProductsTTL].[ProductName]," & _
"[ProductsTTL].[ProductName] " & _
"FROM ProductsTTL " & _
"WHERE [CategoryID] = " & Me.cboCategoryID.Value & _
" ORDER BY ProductsTTL.ProductName;"

You could also do this without changing the cboProductname's RowSource
property. Just set it to the following it its properties sheet:

SELECT [ProductsTTL].[ProductID],
[ProductsTTL].[CategoryID], [ProductsTTL].[ProductName],
[ProductsTTL].[ProductName]
FROM ProductsTTL
WHERE [CategoryID] = Form!cboCategoryID
ORDER BY ProductsTTL.ProductName;"

Then you'd just need one line in the cboCategoryID control's AfterUpdate
event procedure:

Me.cboProductName.Requery

While on the subject of correlated combo boxes you might like to take alook
at my demo at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It may well be relevant in your case, as I suspect you might not have a
properly normalized table here, if you are storing both ProductID and
CategoryID as columns in the table. CategoryID would be functionally
dependent on the non-key column ProductID , hence its redundant and leaves
the door open to update anomalies, the table not being in Third Normal Form.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

No compelling reason to change the approach. The use of a parameter query
which references the other control tens to be more common in my experience,
but both methods will do the job perfectly well. I mentioned it simply for
completeness.