Dynamic look up forms


I am sure some of you have thought of this before and that must be a way to do it.

What I am looking for here is a way to dynamically via VBA create forms based off a template form for all look up tables.

For example, in most of my applications I have tables for users, salutations, access levels, etc.  All those little tables that we use to populate some combobox to make user interface easier, and better yet prevent data issues.

What I am looking for is perhaps a function where I can name a table and a form template and bam a form  with all the fields for that table are created.  I am trying to automate some of the simpler tasks that are becoming time consuming and mundane. I could then just create a single form that points the user to one location to make updates to any of these small tables.

Thanks in advance.
LVL 10
Tony HungateTechnical Writer/Trainer | CISSPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Just a side note to start ...
Wherein this certainly can be done, IF ... you are planning on ever creating and MDE (ACCDE) for your MDB (ACCDB), then this will not work in that mode .... just an fyi.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
If you follow the riles of data normalization then you probably should not be creating additional tables. You should be just adding records to create each new list.

I use two table for almost all look up lists.  One table is the names of the list then second table is all the items for the lists


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't agree that normalization would dictate that you keep your lookups in a single table, but that's a point of debate.

There is no simple method available to create forms from VBA, but Access 2010 does offer the ability to do this quite easily - just select the table you want to use for the form and click the Create - Forms - Form item on the ribbon. This will create a standard form based on the selected table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
Not sure if this will help either, but you can use code like this to select a table and create a form automatically:

    DoCmd.SelectObject acTable, "Yourtable", True
    DoCmd.RunCommand acCmdNewObjectAutoForm

Tony HungateTechnical Writer/Trainer | CISSPAuthor Commented:
Thank you all for your input, it looks like I will continue plugging away with my update forms.

Tony HungateTechnical Writer/Trainer | CISSPAuthor Commented:
Thanks again to all that responded.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.