"John" <jr****@yahoo.com> wrote in message
news:f8**************************@posting.google.c om...
I currently have a list box that contains regions in the US
(Northeast, Midwest, South, etc.). I am retrieving this data from my
Region table(see below). Users have the ability to select a region as
part of a search. The results were too broad so we had to make an
Office table.
Region Table
RegionCd varchar(2)
RefionDesc varchar(50)
Office Table
OfficeId Int
OfficeName varchar(50)
RegionCd varchar(2)
What I want to do is load a listbox so that it has the region first
and then all the related offices below it:
Midwest
Chicago
Des Moines
Northeast
Boston
New York
...
The problem here would be that the region code is a two character
varchar code and the office is an integer. How can I combine in one
list box to perform my search?
At first, I thought you wanted an OPTGROUP:
http://www.w3.org/TR/html4/interact/forms.html#h-17.6
But I assume you want to be able to pass EITHER the region or the office in
your search.
As for combining them... I assume you are asking how to combine them with a
SQL statement? Something like this:
SELECT
Region.RegionCd, Region.RegionDesc, Office.OfficeId, Office.OfficeName
FROM
Region
INNER JOIN
Office
ON
Region.RegionCd = Office.RegionCd
ORDER BY
Region.RegionDesc, Office.OfficeName
This would give you a recordset like:
1, Midwest, 1, Chicago
1, Midwest, 3, Des Moines
2, Northeast, 2, Boston
2, Northeast, 4, New York
Use those values as the values for your select options, but perhaps prepend
something to each that identifies weather it's an OfficeID or a RegionCd.
For example:
<select name="foo">
<option name="R1">Midwest</option>
<option name="O1">Chicago</option>
<option name="O3">Des Moines</option>
<option name="R2">Northeast</option>
<option name="O2">Boston</option>
<option name="O4">New York</option>
</select>
Then on the page that this gets submitted to, take the value of "foo", strip
off the first character to see whether it's R for Region or O for Office,
and then use the rest of the value for performing the search as needed.
Hope this helps.
Regards,
Peter Foti
