-
1. Re: Dynamic Droplist?
Patrick.Cashman Feb 22, 2018 9:02 AM (in response to JimHoover)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.