asked on

Populate Text Box From Dropdown List

Hello Experts,

I have the below hta/vbscript that pulls values from a sql database and populates a dropdown list. How do I have the selection go to a text field? In other words, when the user selects from the dropdown list it should populate the text box. From searching, the closest I came was to an onchange event but I couldn't get it working. Also, can this code be used to create more than one dropdown list? Thanks in advance for your assistance!

<html>
 <head>
  <title>SQL DropDown Demo</title>
  <hta:application
    id="demo"
	 </hta>
  <script type="text/vbscript">

Option Explicit

Sub Window_OnLoad
  Dim oDD : Set oDD = document.GetElementById("Field1")
  Dim oDb : Set oDb = CreateObject("ADODB.Connection")
  oDb.Open "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=myDatabaseServer;Database=myDatabase;"
  Dim sQL : sQL     = "SELECT * FROM [myDatabase].[USERS].[All_USR]"
  Dim oRS : Set oRS = oDb.Execute(sQL)
  Dim oOpt
  Do Until oRS.EOF
      Set oOpt = oDD.document.createElement("option")
      oOpt.Text  = oRS.Fields("USER_NAME").Value
      oOpt.Value = oOpt.Text
      oDD.Options.Add oOpt
      oRS.MoveNext
  Loop
End Sub

</script>
 </head>
<body>
  <form>
   <select id="Field1"> 
   </select>
 
 // Input box to show value selected from dropdown
 <input type="text" maxlength="30" size="40"  id="Field1">
 
 
</form>
 </body>
</html>

Open in new window

Tracy

Steve Knight

Try something like this... if you move some of the repetitive bits into subroutines it helps:

Steve

<html>
 <head>
  <title>SQL DropDown Demo</title>
  <hta:application
    id="demo"
	 </hta>
  <script type="text/vbscript">

Option Explicit

Sub Window_OnLoad
 'ReadSQLData

' Also add some extra values in addition to the SQL data:

  AddOption "Field1","Extra Option 1","Value for Option 1"
  AddOption "Field1","Extra Option 2","Value for Option 2"

  AddOption "Field2","Extra Option 1","Value for Option 1"
  AddOption "Field2","Extra Option 2","Value for Option 2"

End Sub

Sub ReadSQLData
  ' Read information from SQL server and put into Field1 and Field2
  Dim oDb : Set oDb = CreateObject("ADODB.Connection")
  oDb.Open "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=myDatabaseServer;Database=myDatabase;"
  Dim sQL : sQL     = "SELECT * FROM [myDatabase].[USERS].[All_USR]"
  Dim oRS : Set oRS = oDb.Execute(sQL)
  Do Until oRS.EOF
	  AddOption "Field1",oRS.Fields("USER_NAME").Value
	  AddOption "Field2",oRS.Fields("USER_NAME").Value
      oRS.MoveNext
  Loop

End Sub

Sub AddOption(fieldname,SetText,SetValue)
' Pass in the control ID and the text and values to add to it
	  Dim oDD, oOpt
	  Set oDD = document.getElementById(fieldname)
      Set oOpt = oDD.document.createElement("option")
      oOpt.Text  = SetText
      oOpt.Value = SetValue
      oDD.Options.Add oOpt
End Sub


Sub HandleChange(fieldname,SetValue)
' Pass in the ID and selected value of the control to handle a change for
  Select Case fieldname
    Case "Field1"
    	Document.getElementById("Text1").value=SetValue
    Case "Field2"
        Document.getElementById("Text2").value=SetValue
  End Select
  
  MsgBox "You have amended " & fieldname & " to " & SetValue

End Sub

</script>
 </head>
<body>
  <form>
   Field1: <select id="Field1" onchange="HandleChange me.id,me.value">
   </select>
 <br>
   Field2: <select id="Field2" onchange="HandleChange me.id,me.value">
   </select>
<br>
 // Input box to show value selected from dropdown
Text1:  <input type="text" maxlength="30" size="40"  id="Text1">
<br>
Text2:  <input type="text" maxlength="30" size="40"  id="Text2">
 
 
</form>
 </body>
</html>

Open in new window

Steve Knight

You had before both select and text box as "Field1" so I called them Field1 and Text1. I added a second of each "Field2" and "Text2" with them both getting drop down values from SQL. Also showed adding extra values aswell as from SQL.

I have pointed the onchange event to a subroutine HandleChange which knows the field that changed and the value it changed to passed to it

So if you need to deal with more fields then you handle the change events for each in the same place.

tracyms

ASKER

Thanks Steve. The data isn't pulling in from the database, I only see your values in the dropdown - Extra Option 1 and Extra Option 2.

tracyms

ASKER

Also, the database is read only and I don't get any errors when I bring up the hta...if that helps.

Steve Knight

Sorry I left ' in this line from my test without sql:

'ReadSQLData

Remove the ' to let that subroutine run.

Steve

tracyms

ASKER

Ok thanks, did that. Now I'm getting an error Wrong number of arguments or invalid property assignment AddOption - at this line:

AddOption "Field1",oRS.Fields("USER_NAME").Value

I looked up the error and this is one I came across:

You tried to assign a value to a read-only property, or you tried to assign a value to a property for which no Property Let procedure exists.
Source - Visual Basic for Applications Reference

Steve Knight

Sorry didn't test that but without your SQL. Amend this to that and next line:

AddOption "Field1",oRS.Fields("USER_NAME").Value,,oRS.Fields("USER_NAME").Value

I.e. it is posting two values to the sub one is used as the text i.e. what you see and the other as the value which is what is returned when selected.

In this case both are the same

Steve

ASKER CERTIFIED SOLUTION

Steve Knight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tracyms

ASKER

LOL - I hope you're not driving! Thanks - that worked. I changed to this:

AddOption "Field1",oRS.Fields("USER_NAME").Value,oRS.Fields("USER_NAME").Value
AddOption "Field2",oRS.Fields("USER_FIRST_NAME").Value,oRS.Fields("USER_FIRST_NAME").Value

The only thing is the values (Extra Option 1,Extra Option 1) show at the bottom of the dropdowns instead of the top.

tracyms

ASKER

In all fairness, I didn't ask for that so not a show-stopper. You answered my question, THANK YOU! :-)

tracyms

ASKER

Thanks so much Steve!

Steve Knight

No problem, was out with kids not driving :-). If you move the ReadSQLData line below where it as the extra entries they will be at the top.

At the moment SQL reads all entries and puts them in the options one at a time then the extras are added... You can add others before and/or after or none at all - I only suggested it as quite often I want a fixed list and another option even if it just N/A say.

Glad it helped!

Steve

tracyms

ASKER

Yes, I didn't think about adding something to say "Select" for example as the first option of the dropdown until you added those extras to the code. Glad to hear about the kiddies, they are special and you get out what you put in. You're awesome - thanks again!

tracyms

ASKER

Steve,

Would it be possible to make this a dependent list? Meaning, if you select the first, it filters your selection in the second:

AddOption "Field1",oRS.Fields("USER_NAME").Value ---> Select USER_NAME
AddOption "Field2",oRS.Fields("FIRST_NAME").Value ---> Filters FIRST_NAME

Steve Knight

Suggest make a new question, I'll have a look tomorow or Monday then.... or would suggets something along the lines of using the HandleChange subroutine for "Field1" say to re-populate "Field2" drop down using different info?

If you can explain more how and what you want filtered will explain.

Steve

tracyms

ASKER

Awesome - yes, I'll open a new question as I think I have something else I'd like to add. Thanks!

tracyms

ASKER

https://www.experts-exchange.com/questions/29132963/Dependent-Drop-List-SQL-and-VBS.html#questionAdd

Have a good night, thanks!