Dependent Listboxes

You can use dependent listboxes for example to let a user choose a rep in a reps listbox and then have only the customers for that rep be shown in the customers listbox.

You can see an example of this on the ASP Web database demo on my site www.bullschmidt.com/login.asp and then go to the Invoices dialog and choose a rep in the listbox and notice that the page is posted to itself, the customers listbox then gets the focus, and the customers listbox only contains the customers for that rep.

I'd suggest having the onchange event for the first listbox (called RepID) use JavaScript to submit the page (assuming the page is being posted back to itself anyway) and then if the page is a post, show the extra info in the second listbox.

Example:
<select name="RepID" size="1" onchange="RefreshPg('CustID');">

And the SQL for the second listbox (called CustID) would be something like this:
strSQL = "SELECT CustID "
strSQL = strSQL & "FROM tblCust "
strSQL = strSQL & "WHERE (1=1) "
If Request.Form("RepID") <> "" Then
strSQL = strSQL & "AND (CustUserID=" & Chr(39) & Request.Form("RepID") & Chr(39) & ") "
End If
strSQL = strSQL & "ORDER BY CustID"

And on the form have a hidden field which will contain the name of the field to be given the focus when the page is reopened.

Example:
<input type="hidden" name="FocusedFldName" value="<%= Request.Form("FocusedFldName") %>">

And somewhere on the page:
<% ' Set focus.
If Request.Form("FocusedFldName") <> "" Then
' Set focus based on FocusedFldName.
%>
<script type="text/javascript">document.frmMain.<%= Request.Form("FocusedFldName") %>.focus();</script>
<% Else
' Set focus.
%>
<script type="text/javascript">document.frmMain.RepID.focus();</script>
<% End If %>

And here's the JavaScript function to submit the page for this purpose:

function RefreshPg(pstrFldName) {
// Purpose: Refresh pg. to update other fld(s) based on selection.
// Remarks: Used by listbox's onchange.
// Assumes existence of document.frmMain.FocusedFldName hidden fld.

// Set focused fld for when come back.
document.frmMain.FocusedFldName.value = pstrFldName;

// Msg.
alert("Refreshing page to update other field(s) based on your selection.");

// Submit pg to itself to refresh other combo based on this combo.
document.frmMain.submit();
}

And here are some final notes of clarification.

FocusedFldName is the name of a hidden field on the form. It is usually blank but after the user changes the parent listbox (called RepID) JavaScript code puts in the name of the child listbox (called CustID) into the hidden field (called FocusedFldName) on the form.

Then when the page is reopened JavaScript sets the focus on the name of the field (i.e. the name of the child listbox) contained in the hidden field. Thus the focus is set on the CustID field (instead of the RepID field which gets the focus when the form is FIRST opened).

And if the RepID field in the database is a numeric field then the Chr(39) stuff is not needed so this:
strSQL = strSQL & "AND (CustUserID=" & Chr(39) & Request.Form("RepID") & Chr(39) & ") "
Should be changed to this:
strSQL = strSQL & "AND (CustUserID=" & Request.Form("RepID") & ") "

Best regards,
J. Paul Schmidt
www.Bullschmidt.com - Freelance ASP Web Developer
www.Bullschmidt.com/DevTip.asp - ASP Web Developer Tips