Hi all,



I am new to both ASP and SQL so please forgive my question if it is 

real obvious.



I have a form which I would like to use as both an ADD and EDIT form 

for new companies to my DB.



One of the fields is a <select> that will contain a list of Industries 

which I am getting as a query from my Industry table. This is a no-

brainer for me on the ADD version of this form where there is no 

other query on the page.



When I have the EDIT version of this page, I need to query the 

Company table and the Industry table and have the data returned in a 

nested format.



ie:



I have a bunch of fields that have the Company data and then I want 

to have the select menu with the options returned from the Industry 

table and if possible, use the current value of the field in the 

Company table to pre-select that option. Then I will have more of the 

data returned from the Company table displayed.



Any assistance would be great.



Thanks

Charles

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Charles Kline" <ckline@r...>

Subject: [access_asp] Newbie form question





: When I have the EDIT version of this page, I need to query the

: Company table and the Industry table and have the data returned in a

: nested format.

:

: ie:

:

: I have a bunch of fields that have the Company data and then I want

: to have the select menu with the options returned from the Industry

: table and if possible, use the current value of the field in the

: Company table to pre-select that option. Then I will have more of the

: data returned from the Company table displayed.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Basically you want to have a recordset (or array) that contains the list of

all available options. You have another recordset which holds the data for

the Company in question.



You loop through this recordset (or array) writing out each

<option></option> element. At the same time you check to see if the ID value

is the same as the current value that is stored for the Company record in

question. The basic (messy) logic would be like this:



<%

' objRSCompanyDetails holds company details

' objRSIndustries holds all industries

%>

<select name="cboIndustry">

<%

Do While Not objRSIndustries.EOF

    With Response

        .Write("<option value=""" & objRS("IndustryID") & """")

        If CInt(objRS("IndustryID") 

CInt(objRSCompanyDetails("IndustryID")) then

            .Write(" selected")

        End If

        .Write(">" & objRS("IndustryName") & "</option>" & vbCrLf)

    End With

    objRSIndustries.Movenext

Loop

%>

</select>



Now the above is really messy, and complex to debug/read. So use standard

routines for building and maintaining form state. The following routine

would be called like so:



<% =WriteFormSelectList("cboIndustry", "", "", arrIndustries, 1, "",

objRSCompanyDetails("IndustryID"))%>



which in one line writes out all the necesary HTML for the <select> list,

and selects the appropriate element (watch for wrapping):



'------------------------------------------------------------

' WriteFormSelectList

'------------------------------------------------------------

' Returns HTML for a <select> element

' Accepts strName as string

' Accepts strID as string

' Accepts strHTMLAttributes as string - literal text for any other HTML

attributes

' Accepts form option elements as array

' Accepts array format as integer: 0 = rows/cols, 1 = cols/rows (eg from

getRows)

' Accepts optional text for first <option></option> tag

' Accepts strSelectedValue as option to be selected

'------------------------------------------------------------

Function WriteFormSelectList( _

 ByVal strName, _

 ByVal strID, _

 ByVal strHTMLAttributes, _

 ByVal arrOptions, _

 ByVal intArrayFormat, _

 ByVal strFirstOption, _

 ByVal strSelectedValue _

 )



 Dim i   ' array 1st dimension counter



 If not isArray(arrOptions) then

  Exit Function

 End If



 WriteFormSelectList = "<select name=""" & strName & """ ID=""" & strID &

""""



 If Len(strHTMLAttributes & "") > 0 then

  WriteFormSelectList = WriteFormSelectList & " " & strHTMLAttributes & """"

 End If



 WriteFormSelectList = WriteFormSelectList & ">" & vbCrLf



 If Len(strFirstOption) > 0 then

  WriteFormSelectList = WriteFormSelectList & strFirstOption & vbCrLf

 End If



 If intArrayFormat = 0 then

  For i = 0 to Ubound(arrOptions, 1)

   WriteFormSelectList = WriteFormSelectList & "<option value=""" &

arrOptions(i,0) & """>" & arrOptions(i,1) & "</option>" & vbCrLf

  Next

 Else

  For i = 0 to Ubound(arrOptions, 2)

   WriteFormSelectList = WriteFormSelectList & "<option value=""" &

arrOptions(0,i) & """>" & arrOptions(1,i) & "</option>" & vbCrLf

  Next

 End If



 WriteFormSelectList = WriteFormSelectList & "</select>" & vbCrLf



 If Len(strSelectedValue & "") > 0 then

  WriteFormSelectList = Replace(WriteFormSelectList, "value=""" &

strSelectedValue & """>", "value=""" & strSelectedValue & """ selected>")

 End If



End Function

'------------------------------------------------------------

' --- WriteFormSelectList

'------------------------------------------------------------