Dynamic Forms, VBA, and adding fields to a form from a user experience

[Vaughto]

Hello all,

I have been doing some searches to see if there were already a solution out there but cant find one to fit my needs.

What I am trying to build is dynamic form that allows the user to add/remove fields as needed. To describe what the page is. Its an employee database that tracks all employee data (employee table) as well as certain training/thresholds they reach. The issue is that as this is being used more by my company different divisions have different training requirements. So I have two options 1) to be the person that has to adjust and customize the form for each division/section that has different requirements. 2) Develop a way for the users to be able to add columns (I have already developed a function that will allow a user to add columns to the backend without having to actually use the back end with an Alter Table function) that will then appear on the form.

Note: This is the code I worked out to add user selected columns to a table for the first part of this function.

Dim dbs As Database
Dim Ctype As String
Dim Cname As String
Ctype = Me.ColumnType
Cname = Me.Cname
On Error GoTo Err
CurrentDb.Execute "ALTER TABLE PipelineTest ADD COLUMN " & Cname & " " & Ctype & ";"
Err:
MsgBox Err.Description

Where I am stuck is being able to add an editable field to the form as needed by the users without me having to code it in. I can get a list function to grab all and even new fields to display the data. But then being able to click on that particular area to add a date into the box. With the current hard coded function the overall form is a repeating form that displays the records with filters on top and details on the employee below with the data needed for the processing in the form function that allows them to edit each field to add dates, select yes/no, or enter text as needed (similar to a spreadsheet).

I have considered doing a split form/Subform with the data showing from a query or table but the query doesnt update to show the new columns. In the sub form the fields are locked based on what was in the fields when the form was saved. I also am trying to make sure to have the maximum flexibility with fewer forms if able.

Is it even possible to use VBA to add new fields into a form programmatically that is virtually invisible to the users? If so any suggestions for coding to do this?

Update: One thing I am considering is using like 20 unbound fields on the form, a user selected form that allows them to "turn on/Turn off" fields and using that table based on the user selections to connect the unbound fields to the data via VBA.

Also sorry if this doesnt quite make sense.