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

    Dynamic Droplist?

    JimHoover

      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?
          Patrick.Cashman

          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")

           

                    cbo_start_new_select_vendor.Items.Clear

                  For n = 0 To names.Count - 1

                      cbo_start_new_select_vendor.Items.Add(names(n))

                  Next

          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)

          conn.Open()

           

          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

           

          da.Fill(ds)

          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

          Next

           

          If idx = -1 Then

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

          Exit Function

          End If

           

          values.Clear

          For Each dr In dt.Rows

          values.Add(dr.ItemArray(idx))

          Next

           

          da = Nothing

          dt = Nothing

          ds = Nothing

          dr = nothing

          conn.Close

          '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.