1 Reply Latest reply on Feb 22, 2018 9:02 AM by Patrick.Cashman

    Dynamic Droplist?


      Using IDE, and VBScript:

      Looking for a way to populate a Droplist, dynamically, from an Oracle database.

      Based on the pick made from the Droplist, the rest of the fields would then be populated.

      Can the "List Choices" dialog be updated, automatically and dynamically?

      Any suggestions?


      More detail:

      Ideally the form would come up, and the droplist would be populated, dynamically, with a list of projects.

      That list of projects is added to every day and changes as a result.

      Once a project is chosen, the information for that project is loaded into the form in other fields.

      The source database for the project and project information is Oracle.

        • 1. Re: Dynamic Droplist?

          If I am loading items into a combobox (drop down list), I clear the combobox when the form is loaded (or any other time I want to re-populate it) then add items from some other source.  The source could be many things.  In your case it appears that you want to execute a query to a database, retrieve a set of values from the database, and use them to fill the combobox.  Here is an example of doing that:


          The subroutine that clears and loads the combobox:

          Sub load_vendor_combobox()

          Dim names As New ArrayList

          Dim n As Integer


                    names = read_field_values_in_table("company_name", "vendor")



                  For n = 0 To names.Count - 1



          End Sub

          The read_field_values_in_table function, which queries the database and returns the arraylist of values:

          Public Function read_field_values_in_table(field_name As String, table As String) As ArrayList

          Dim i, idx As Integer

          Dim dr As Data.DataRow

          Dim values As New ArrayList

          'Dim conn As OleDbConnection  (this, and several other variables used here, are declared globally elsewhere.)


          conn = New OleDbConnection(con_str)



          sql = "select * from " & table & " where " & field_name & " IS NOT NULL ORDER BY " & field_name & ";"


          da = New OleDbDataAdapter(sql, conn)


          Dim ds As New Data.DataSet

          Dim dt As New Data.DataTable



          dt = ds.Tables(0)


          idx = -1

          For i = 0 To dt.Columns.Count -1

          If dt.Columns(i).ColumnName = field_name Then

          idx = i

          End If



          If idx = -1 Then

          MsgBox(field_name & " is not found in table " & table & ".")

          Exit Function

          End If



          For Each dr In dt.Rows




          da = Nothing

          dt = Nothing

          ds = Nothing

          dr = nothing


          'conn = Nothing

                  Return values

          End Function


          This example is in vb.net.  Different than the IDE but I hope it gives you some ideas and the basic process you'd follow. Your database connection string will change depending on the database you use and where it's located.