AnsweredAssumed Answered

Dynamic Droplist?

Question asked by JimHoover on Feb 21, 2018
Latest reply on Feb 22, 2018 by Patrick.Cashman

Like • Show 0 Likes0
Comment • 1

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.

No one else has this question

Outcomes

1 Reply

Patrick.Cashman Feb 22, 2018 9:02 AM
Mark Correct
Correct Answer
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.
Like • Show 3 Likes3
Actions

Dynamic Droplist?

Outcomes

Related Content

Recommended Content