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 HungateDirector of TrainingAsked:
Who is Participating?
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 MVPCommented:
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


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 HungateDirector of TrainingAuthor Commented:
Thank you all for your input, it looks like I will continue plugging away with my update forms.

Tony HungateDirector of TrainingAuthor Commented:
Thanks again to all that responded.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.