Dropw down list

Help

Scenario is this:

User selects Product #1 to update, this takes him to a form which populates fields for that particular product. i.e product name, price etc (from products table)

This page should also have a dropdown list for the categories (popluated from the categories table) but I want the category for the chosen product to be selected by default. (i hope its clear)

eg if user selects jaguar XK8 to update, the relevant category that should be selected should be sports car (but the dropdown list should also display the rest of the categories (luxury, economy etc)

below is my sql query

strSQL1 = "SELECT tblProducts.PID, tblCategory.Category, tblCategory.CategoryID " &_
"FROM tblCategory INNER JOIN tblProducts ON tblCategory.Category = tblProducts.Category"

and then my scrip to populate the dropw down list:
<%
Response.Write "<SELECT Name=""Category"">"
Response.Write "<OPTION Value="" ""></OPTION>"

' Loop through the recordset and populate the dropdown
while not objRS1.eof
if objRS1.fields("CategoryID") = selected then
response.write("<OPTION VALUE=" & objRS1.fields("CategoryID") & " selected>")
response.write(objRS1.fields("Category") & "</OPTION>")
else
response.write("<OPTION VALUE=" & objRS1.fields("CategoryID") & ">")
response.write(objRS1.fields("Category") & "</OPTION>")
end if
objRS1.MoveNext
wend

'Close the Selection box
Response.write "</select>"

%>

Compare objRS1.fields("Category") to the form selction. If they are the same, add selected to the option. You can do it with a simple if statement in your code.

what you want to do is a query statement to lookup all the information on the product - store that info in local variables, so lets say your Category ID is 5 for "Sports Car"

so in your while not loop you simply place

another shorter way would be: