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

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.

Code:

---

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.

I am not sure that I understand what you mean. You talk about different training requirements. Does this means that employees have to follow trainings depending on the departement they work for? In that case your data model should support that requirements without the need to add extra fields.

I do not believe Access is very suitable for what you want. That's really a dead end.

We have to annotate what dates they have accomplished certain thresholds or it could be a passed/failed or a score %. But this is across a large number of different customers and requirements. So some divisions have requirements for some items but not the same items as a different division. So like the customer service folks in one division would be required to accomplish CSR Training, while a tech in another division would not. The manager of each division needs to be able to track/input that data for their division. Some of the training might be exactly the same and some not. Some are unique to a division or even to a project being worked. Right now I have to get with each division and project manager and hard code their views in to meet their requirements. If those change I have to update the client for their division. Which means I have to maintain different clients for different divisions and keep each one separate but the data is all going into one spot for the business management (Corporate) side to be able to easily see statistics across all divisions.

I am trying to be able to set up one client across all the divisions that I can easily manage without having to maintain separate clients while still providing customization for the users. The way I envision it they can select the items that are the same across divisions that they need...and not see items that they do not need, or if a new training becomes required they can easily add that without me needing to hard code it.

Does that clear it up?

Quote:

---

Originally Posted by Vaughto

Does that clear it up?

---

Yes, a bit.
I still believe the solution should be in your data model. You could make tables like
- employee
- division
- training
Also several tables to store related data, like
- employee-division (who works where)
- division-training (what is required where)
- employee-training (who did what)

Quote:

---

Originally Posted by xps350

Yes, a bit.
I still believe the solution should be in your data model. You could make tables like
- employee
- division
- training
Also several tables to store related data, like
- employee-division (who works where)
- division-training (what is required where)
- employee-training (who did what)

---

Yeah I already have the tables. Thats not the issue. The issue is having a form/Table that can be changed dynamicly by the user without having to edit the back-end and/or edit the form. The users are not access smart and I dont want them editing or modifying the forms. I do however need a solution that allows them to add a column to a table (already solved) that then appears in a form for them to add/edit data in. I already have the form. I already have the tables that are connected correctly and even the queries. I can make it work if I make the data into a list box, however the list box does not allow them to edit the data in a particular section without having either another pop up or placing all the data sets in a different section of the form. Having the data sets show up as a query (to use the linked data of employee -> training) doesnt solve the problem either.

I think I have come to a solution of placing 20 un-bound text boxes on the form that can be dynamicly changed through a user preferences form and table. This allows the user to select up to 20 columns they want to see/edit from the form. Then using VBA to reference the table that the userpreferences are stored in to change the controls of the un-bound text boxes on the training form.

So basicly on load
dlookup- user preferences table and filter to username this pulls the
yes/no for display or do not (no sets size of field to 0)
user selected fields they want displayed in what order through a combo-box that pulls the column headings from the training table.
VBA to change the settings on the training forms to match the user selected preferences.

This way a user can de-select data they no longer need, add new column(s) if needed, and have it displayed without any coding from me allowing me to have one "developer view" that I push out to all the divisions without having to spend hours customizing it to each division/program.

Once I have it all coded and put together/running I will post my table structure and coding and examples of the forms to demonstrate. I came here first to see if anyone had any simpler solutions through just VBA vs where I think it needs to go.

Yes...I know. Access is not normally used for this. Right now my company is using this solution due to the cost effectiveness (they already have the different functions we are using) and it only costs them to pay my hours spent. I am pushing for an actual budget and team (as it gets bigger and more complicated) to move this out of access/sharepointlists to SQL/Python front end. Until then I am still supporting multiple divisions and data part time and need to develop in a way that is most efficient for my time. Rather than spending hours customizing I can place the customization part on the users if designed right. That function can then be carried into the actual project that it needs to move to in the future.

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.

Code:

---

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

---

if your db is intented for Multi-user, be warned that creating a New field on the table, Access will Actually Locked the table (or even the db) first before it can perform this operation.
if somebody has a form/query or the table itself is opened by someone, most likely you'll get Runtime error.

in real db world this is a no, no.

If this were a real database world I (a wanna-be, self taught, database designer) wouldnt be using access to build a database supporting several multi-million dollar contracts. Alas, I am working with what I can within the scope I have been provided. I am pushing for a budget and a team as this thing goes from a one-team small build to a much larger scope that Access just cannot support in the near future.


For now, the back-end is hosted on the companies sharepoint as lists and the front end that the internal users are using is Access. Unfortunately I cannot find another way to provide enough flexibility for the users to add their own columns other than what I have provided. I could limit that function to myself (to prevent any error issues) which is an option I may choose if it becomes a problem. Its still a faster solution than going in and hard coding each section/division separately based on their needs.

For my time and purposes, creating the column within the back end is the less important part, and the dynamic form restructure based on user preferences is so much more important. in previous versions for every different program/division I had to hard code/change the form manually and push out an update for the users. The program was initially designed to only support one program so, as more programs came on board, I had to customize the forms to each program which proved to be a drain on me as the designer. The second part of the code allows the user to select up to 20 different fields (or less) to view, the order in which they want to view them, and it saves it to that users profile. Allowing different users to have different data sets (or the same) without me having to go into the form, re-arrange, code it to that user etc. That is why I was looking for dynamic forms. In design view it looks a bit wonky as it has alot of unbound fields that are waiting for the VBA to change them. It may only save me 30 mins here and there. But as more and more programs are coming to this database as a solution over their spreadsheets that adds up.

Believe me I know I am way out of my depth and even pushing access pretty far. Myself and my boss are both pushing for expanding the budget and moving to a team of a few software engineers in an SQL(esque) with a python/html front end. That would provide much more flexibility and has been in every briefing I give on the database. That, however, is going to probably be a 2-3 year goal as anything we build from scratch has to go through a long process of HR/Security/Cyber and testing. Access and the database I have built really is just a band aide for a need within the company that could be implemented in a short term. Since the software and platforms we use are already in existence within the companies authorized use programs and approved for use by our security and cyber security folks. The problem is the band-aide is only capable of so much and will eventually fail.

I am actually quite proud of how well it is holding up so far with few relative issues or complaints and alot of praise both from users and VPs above (which I am trying to leverage for more funding).

even if you are not using ms access, still the principle holds.
modifying a table at runtime is risk.
it might corrupt your db and make is unusable.
just a fore warning. vp's wont be happy.