Create Form from DAO ?

Don't ask me why, but I would like to create a form based on a specified table from code. Is this possible and how ?

Who is Participating?
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.

Have you tried the wizards?  They aren't perfect but they do take care of some of the more mundae tasks of alignment and control-source setting.  In code this would be very hard to do because there are so many different kinds of tables.

More details please.  Do you want to store the properties of the form in a table and then create the form dynamically?  I've heard of that being done before.

You cannot create form or report objects in DAO. The DAO object model does not include these objects. The objects are created from the Access Application.
Here is some sample code from Access 2000 help:
Sub NewForm()
    Dim frm As Form
    ' Create form based on Customers form.
    Set frm = CreateForm( , "Customers")
    ' Set RecordSource property to Customers table.
    frm.RecordSource = "Customers"
End Sub


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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mputterAuthor Commented:
O.K. Let's get open:

I want to make a tool to split unnormalized tables. The idea I'm following is this:

1. Have a form with a combo to select the table to split. The combo's rowsource is dynamically set to contain all available tablenames in the db.

2. The creation of a subform, based on the selected table, which shows the data of the table. This subform needs therefore be created on-the-fly. The subform control's source on the mainform also has to be set dynamically from code.

3. A combo to select the fieldname that contains the criterium for splitting.

4. A combo to select the data-value of this field which acts as the criterium to extract the records from this table and put them in a new one.

My question is related to step 2.
The createform sounds good, but contains only part of the process. The actions performed when dragging a table on a form would be nice to be performed ultimately from code: Automatic creation of a subform control on the main form, automatic creation of the subform with all data-fields in it, and show its data! Sounds like a lot of work to do in code...

If someone knows an available tool to do this otherwise ....

I'll have to study this a bit more but I'm wondering that you don't use the wizards like cjswimmer suggested.

I'm not necessarily talking about the menus from the Forms tab - Design, but using the code that runs that wizard and make develop your own routine which does what you want.  Sure beats the hell out of trying to write something like this from scratch.

All of the built-in wizards are available in MDA format from Microsoft.  The code is wide open and you can use it for your own use.  They are also a good, if not intense, way to learn more about some of the behind the scenes programming in Access.

I also believe that there is a way to create the simulation of running the form design wizard from code, which would also seem to do what you want.

If you want to explore this more, just say so and I'll see what I can come up with for you.  Of course, you will not be able to do this (run the wizards) in an MDE, I think.

This code should do it.
Private Sub cboSelectTable_AfterUpdate()
    Dim ctlDefault              As Control          'default control on the form that is being modified
    Dim fldTable                As Field            'a field in the table that is being analyzed
    Dim tdefTest2               As TableDef         'table def of table that is being anlayzed
    Dim frmTableDisplay         As Form             'form that is being modified to display fields of analyzed table
    Dim ctlNew                  As Control          'new control on form
    Dim ctlolder                As Control          'old control on form
    Dim intcount                As Integer          'counter for loops
    Dim intcontrols             As Integer          'number of controls on form
    On Error GoTo ErrorHandler
    Set tdefTest2 = DBEngine(0).Databases(0).TableDefs(Me.cboSelectTable)
    ''''set source object to a dummy form so that we can redesign the real subform
    Me.fsubTabledisplay.SourceObject = "fsubtestit"
    DoCmd.OpenForm "fsubTableDisplay", acDesign, , , , acHidden
    Set frmTableDisplay = Forms("fsubTableDisplay")
    '''''Delete all controls on subform
   With frmTableDisplay.Controls
   '''''For each should have worked but had trouble so I bagged it
        intcontrols = .Count - 1
        For intcount = 0 To intcontrols
             strName = .Item(0).Name ''''NB everytime I delete an item the index of the items left changes
                                     ''''so just delete the zero item every time
             DeleteControl frmTableDisplay.Name, strName
        Next intcount
   End With
   DoCmd.Close acForm, frmTableDisplay.Name, acSaveYes 'close and save redesigned subform
                                                        'note this is done because of potential dup names
    ' Create new text box for each field in table and bind it to that field
    DoCmd.OpenForm "fsubTableDisplay", acDesign, , , , acHidden
    Set frmTableDisplay = Forms("fsubTableDisplay")
    frmTableDisplay.RecordSource = tdefTest2.Name
    For Each fldTable In tdefTest2.Fields
        Set ctlNew = Nothing
        Set ctlNew = CreateControl(frmTableDisplay.Name, acTextBox, acDetail, , fldTable.Name)
        ctlNew.Name = fldTable.Name
    Next fldTable
    DoCmd.Close acForm, frmTableDisplay.Name, acSaveYes 'close and save redesigned subform
    Me.fsubTabledisplay.SourceObject = "fsubTableDisplay"
    Exit Sub
    MsgBox "oops " & Err.Description & " the error number is " & Err.Number
    Resume Next
End Sub
Several things to note:
1) this code works in access 97
2) It is commented enough to be self explanatory I hope, but maybe not - if there is something obscure please let me know
3) I basically did not have to create a form in code; rather I created two totally blank forms (fsubtestit and fsubTableDisplay) to start with and added and deleted controls as necessary.
(Performance wise this is a bit better)
4)I ran this code off the after update event of a combo box that was filled with the names of the tables that were in the database
5)Jim I could be wrong but I think in Access97 the wizards are wrapped as mde's and so the code is not wide open.
However I think the book Access Developeres handbook has some of the code

In regards to your comment(5), the Access wizards are available on MSN in .mda files.  You can take them apart, mess with the code, add your own captions to the forms, etc.

Thank you Wes for your information. I was pretty sure that the mda files were not delivered with Access but I was unaware that they were available on MSN. Again thanks for your info
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.