In case anyone ever needs a solution similar. I have broken this down into multiple parts that now allows a user to create, name, and determine type of a column from a form into a table.
A second form that allows a user to select up to 20 fields to view on the "pipeline" form which is the hiring pipeline with all the different steps for hiring into each division. Creating user controlled views on the pipeline form which makes the form dynamic in a way that keeps my backend/user customization on my side to a minimum. So the user can choose any of the columns in the table to view (even newly created ones) up to 20 fields total.
Both those forms effect the third form which is the actual pipeline form. The code for the pipeline form is as follows.
on the Pipeline form what I have done is used a series of unbound fields that are then connected to the correct location and field based on the user preferences using VBA. Below is the code I used to accomplish this. This way the user can create new columns, select the columns and order viewed in, and use those preferences to modify the data all without me having to go in and code anything new or different to allow for a new column or change a column type. The below code may not be elegant and I probably should have used a select case vs if/then/else but it does work for what I am trying to accomplish.
If anyone wants a full run down with examples please let me know.
Dim Username As String
Dim Ptype As String
Dim X As String
'On Error GoTo err
X = 1
Do Until X >= 21
If DLookup("[Visible " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'") = True Then
If DLookup("[Field Type " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'") = "acTextBox" Then
'Text box changes
Controls("L" & X).Caption = DLookup("[Field Name " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("p" & X).ControlSource = DLookup("[Field Name " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("L" & X).Width = DLookup("[Field length " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("P" & X).Width = DLookup("[Field length " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
'Checkbox Changes
Controls("LC" & X).Caption = ""
Controls("C" & X).ControlSource = ""
Controls("LC" & X).Width = 0
Controls("C" & X).Width = 0
Controls("ELC" & X).Width = 0
'Combo Box Changes
Controls("LCB" & X).Caption = ""
Controls("CB" & X).ControlSource = ""
Controls("LCB" & X).Width = 0
Controls("CB" & X).Width = 0
ElseIf DLookup("[Field Type " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'") = "acCheckBox" Then
'Checkbox Changes
Controls("LC" & X).Caption = DLookup("[Field Name " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("C" & X).ControlSource = DLookup("[Field Name " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("LC" & X).Width = DLookup("[Field length " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("C" & X).Width = DLookup("[Field length " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("ELC" & X).Width = DLookup("[Field length " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("ELC" & X).GridlineStyleRight = 0
Controls("C" & X).GridlineStyleLeft = 0
'Text Box Changes
Controls("L" & X).Caption = ""
Controls("p" & X).ControlSource = ""
Controls("L" & X).Width = 0
Controls("P" & X).Width = 0
'Combobox changes
Controls("LCB" & X).Caption = ""
Controls("CB" & X).ControlSource = ""
Controls("LCB" & X).Width = 0
Controls("CB" & X).Width = 0
ElseIf DLookup("[Field Type " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'") = "acComboBox" Then
'Combo Box Changes
Controls("LCB" & X).Caption = DLookup("[Field Name " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("CB" & X).ControlSource = DLookup("[Field Name " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("LCB" & X).Width = DLookup("[Field length " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
Controls("CB" & X).Width = DLookup("[Field length " & X & "]", "UserPreferences", "Username ='" & Environ("username") & "'")
'text field changes
Controls("L" & X).Caption = ""
Controls("p" & X).ControlSource = ""
Controls("L" & X).Width = 0
Controls("P" & X).Width = 0
'checkbox Changes
Controls("LC" & X).Caption = ""
Controls("C" & X).ControlSource = ""
Controls("LC" & X).Width = 0
Controls("C" & X).Width = 0
Controls("ELC" & X).Width = 0
End If
Else
'Combo box changes
Controls("LCB" & X).Caption = ""
Controls("CB" & X).ControlSource = ""
Controls("LCB" & X).Width = 0
Controls("CB" & X).Width = 0
Controls("LCB" & X).Visible = no
Controls("CB" & X).Visible = no
Controls("LCB" & X).Visible = no
Controls("CB" & X).Visible = no
Controls("LCB" & X).GridlineStyleLeft = 0
Controls("CB" & X).GridlineStyleLeft = 0
Controls("LCB" & X).GridlineStyleRight = 0
Controls("CB" & X).GridlineStyleRight = 0
'Text Box changes
Controls("L" & X).Caption = ""
Controls("p" & X).ControlSource = ""
Controls("L" & X).Width = 0
Controls("P" & X).Width = 0
Controls("L" & X).Visible = no
Controls("P" & X).Visible = no
Controls("L" & X).GridlineStyleLeft = 0
Controls("P" & X).GridlineStyleLeft = 0
Controls("L" & X).GridlineStyleRight = 0
Controls("P" & X).GridlineStyleRight = 0
'checkbox Changes
Controls("LC" & X).Caption = ""
Controls("C" & X).ControlSource = ""
Controls("LC" & X).Width = 0
Controls("C" & X).Width = 0
Controls("ELC" & X).Width = 0
Controls("LC" & X).Visible = no
Controls("C" & X).Visible = no
Controls("ELC" & X).GridlineStyleLeft = 0
Controls("LC" & X).GridlineStyleLeft = 0
Controls("C" & X).GridlineStyleLeft = 0
Controls("ELC" & X).GridlineStyleRight = 0
Controls("LC" & X).GridlineStyleRight = 0
Controls("C" & X).GridlineStyleRight = 0
'Controls("ELC" & X).Visible = no
End If
X = X + 1
Loop