Web Database Concept
     How We Work Together
     Web Database Sample
     Classic ASP Design Tips
     Bar Chart Tool
     Web Design Resources
     Access Database Sample

Classic ASP Design Tips
Dependent Listboxes
These are opinions of J. Paul Schmidt, MBA.
No warranties are either expressed or implied.
To Use Paul's Services: Paul@Bullschmidt.com

Dynamically show the results in a listbox based on what was chosen in another listbox.

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.

View "Dependent Listboxes" Sample

Download "Dependent Listboxes" Sample

To streamline code on the main page and easily allow many possibilities the above sample uses a custom ASP class called objjpsvbBuildOption which resides in an included page to actually fill the listboxes. But the same concept here also applies no matter what code is used to fill the listboxes.

I'd suggest having the onchange event for the first listbox (called CustUserID) 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.

<select name="CustUserID" size="1" onchange="jpsjsRefreshPg('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("CustUserID") <> "" Then
    strSQL = strSQL & "AND (CustUserID=" & Chr(39) & Request.Form("CustUserID") & 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.

<input type="hidden" name="FocusedFldName" value="">

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

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

function jpsjsRefreshPg(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.

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 CustUserID) 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 CustUserID field which gets the focus when the form is FIRST opened).

And if the CustUserID 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("CustUserID") & Chr(39) & ") "

Should be changed to this:
strSQL = strSQL & "AND (CustUserID=" & Request.Form("CustUserID") & ") "

And to see something like this in action you can do the following:
- Go to www.bullschmidt.com/login.asp (the ASP Web database demo's login page)
- Click the Continue button
- On the Main Menu click the Customers Edit button
- On the Customers Edit Search Dialog's Customer listbox notice that all the customers are shown
- In the Rep listbox choose a rep
- Also notice that the focus has switched to the Customer listbox

To Use Paul's Services:

Let's put some data on the Web!
Copyright © 2000-2018
J. Paul Schmidt, MBA
Freelance Web and Database Developer
All Rights Reserved

Privacy Policy