Populate comboboxes, based on (dynamic) table field names (and count)

[OTWarrior]

Hello

[Using Access 2016]

I am tying to develop a form, that will import data from Excel, and create dynamic queries based on the imported information.Each excel spreadsheet can have different values, including number of columns and fieldnames.

I have managed to work out how to import an excel spreadsheet to access and save as a table, along with changing the recordsource for the form to match the table.


I now need to have a generate button, to display each of the fields (so you can toggle them on/off) and if selected, allow the user to select from within that fields dataset from a dropdown.


A bit of a tall order I know.


Say I have 5 comboboxes and their labels.

How can I populate Label1 with the first fieldname from the table, and populate the combolist itself with unique values from the first field.

Then do this for the second field from the table and second combo/label respectively?

This is what I have so far, but it errors on comboLIST.Name, saying "error 91, object with or variable not set"



Private Sub cmdPopulate_Click()
Dim comboLIST As ComboBox
For i = 1 To 25 ' will need to be dynamic later
Dim strSQL As String
comboLIST.Name = "Combo" & i
comboLIST.RowSourceType = "Table/Query"
comboLIST.RowSource = "Select [Field & " & i & "] FROM " & LabelFile.Caption
Next i
End Sub

[OTWarrior]

As is often the case, Just typing it out I have come up with a solution


Private Sub cmdPopulate_Click()
Dim cntrl As Control
Dim j As Integer
Dim i As Integer
Dim k As Integer
Dim fieldCount As Integer
Dim tmpCTRLname As String
Dim cboArray As Variant
Dim FieldArray() As Variant
Dim chkbxArray() As Variant
'Set r = d.OpenRecordset(LabelFile.Value)
i = 0
j = 0
k = 0
tableSource = LabelFile.Value
fieldCount = CurrentDb.TableDefs(LabelFile.Value).Fields.Count
ReDim Preserve FieldArray(0 To fieldCount)

'/////add fieldnames to labels and enable checkboxes respectively
For Each cntrl In Me.Controls
'Debug.Print "-------------------"
'Debug.Print cntrl.Name & "- i = " & i
'Debug.Print tmpCTRLname
If cntrl.ControlType = acLabel Then
If Left(cntrl.Caption, 5) = "FIeld" Then
cntrl.Caption = CurrentDb.TableDefs(LabelFile.Value).Fields(i).Name
FieldArray(i) = cntrl.Caption
i = i + 1
End If
End If
If i = fieldCount Then Exit For
Next
'/////Add fields to each combobox
For Each cntrl In Me.Controls
If cntrl.ControlType = acComboBox Then
cntrl.Enabled = True
cntrl.RowSourceType = "Table/Query"

newComboSource = "SELECT [" & FieldArray(j) & "] FROM [" & tableSource & "] Group BY [" & FieldArray(j) & "]"
cntrl.RowSource = newComboSource
cboArray(j) = cntrl.Name
j = j + 1
End If
If j = fieldCount Then Exit For
Next

For Each cntrl In Me.Controls
If cntrl.ControlType = acCheckBox Then
cntrl.Enabled = True
cntrl = True
chkbxArray(k) = cntrl.Name
k = k + 1
If k = fieldCount Then Exit For
End If
Next
End Sub


Hope that helps anyone else who has a similar task