Combo box with option group

[markdt]

Hi im trying to create a combo box with a number of areas each divided into groups similar to this:

<SELECT>
<OPTGROUP LABEL="Alkaline Metals">
<OPTION>Lithium (Li)</OPTION>
<OPTION>Sodium (Na)</OPTION>
<OPTION>Potassium (K)</OPTION>
</OPTGROUP>
<OPTGROUP LABEL="Halogens">
<OPTION>Fluorine (F)</OPTION>
<OPTION>Chlorine (Cl)</OPTION>
<OPTION>Bromine (Br)</OPTION>
</OPTGROUP>
</SELECT>

But instead of having fixed values i would like to build the list from an access db.

Any ideas?

 

[Tarwn]

I had an explanation here at first, but it was bad. Let me start with an example and then try the explanation aferwards:

'declare your database vars
Dim conn_db, rs_opts
Set conn_db = Server.CreateObject("ADODB.Connection")
conn_db.Open "a connection string here"

'retrieve data
Set rs_opts = conn_db.Execute("SELECT GroupName, OptionName FROM MyTable ORDER BY GroupName, OptionName")

'declare a temp var
Dim prev_group

'start the output loop
If Not rs_opts.EOF Then rs_opts.MoveFirst
Response.Write "<SELECT>"
Do Until rs_opts.EOF
   'if the row we are processing has a differant group then the previous row (or is first row), start new optgroup
   If rs_opts("GroupName") <> prev_group Then
      Response.Write "<OPTGROUP Label=""" & rs_opts("groupName") & """>"
      prev_group = rs_opts("GroupName")
   End If

   'output the option for this record
   Response.Write "<OPTION>" & rs_opts("OptionName") * "</OPTION>"

   'advance the recordset
   rs_opts.MoveNext

   'if this is the end of rs or next record is diff group, end the OptGroup
   If rs_opts.EOF Then
      Response.Write "</OPTGROUP>"
   Elseif rs_opts("GroupName") <> prev_group Then
      Response.Write "</OPTGROUP>"
   End If
Loop

Response.Write "</SELECT>"

Basically what your doing while you loop through tthe recordset is always outputting an Option tag. Only when the group for that option changes do you do anything. When the next Group is going to be differant (ie, bottom portion of loop) you finish the previous group. When the previous group was differant (ie, top of the loop) or on your first loop, it creates the option group.

There are several other ways to do this. Some people tend to add an if statement to the first one to handle the end tag then folow the loop with an addition output to handle the last open group. That method is a little harder to read than this one, simply because the logic starts nearer what you would think is the end of the loop.

The above ius not intended to be a working sample, but hopefully it was what you were looking for.

-T

 

[markdt]

Excellent mate,

Worked perfectly, just what i wanted.