• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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 ?

1 Solution
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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now