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

[Vaughto]

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).