Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    74

    Ability for user to create a "custom" form during runtime.

    I'm trying to puzzle a way to make this work. I'm not sure it's even possible and if it is, not easily done. But anyways here it is:

    The database I've been working on is a type of auditing software. Several forms (audits) are available and the user simply runs through the fields, pretty much just answering yes or no along with some specific data. There are only eight audits at this time and usually it would never be more than 10 at one time and at least two of those audits would change.

    The idea is that all of these audits are disabled at the end of their period but the data remains for report creation. The old audits can be reinstated at any time.

    What i'm trying to puzzle out is a way that the simple user could create a new audit without having Access knowledge. I'm talking something that would probably be more suited to a Visual Studio project. The ability to add controls (with hard-coding behind the scenes to determine their position in the form), simple comboboxes to determine if the created field is required or not, some logic matters such as if one field is "Yes" then another field is disabled, the creation of a new table for that audit to hold it's data, the creation of a query to edit the data, and the creation of a report to show all of that data in a user friendly way.

    Is what I'm asking, beyond the scope of Access or just not worth the trouble, and simply creating new audits and the VBA programming behind it, SQL statements, rowsources, etc.?

    Any thoughts?
    Version: Access 2010

  2. #2
    Join Date
    Mar 2009
    Posts
    5,287
    Although this is not usual, nor easy, it can be done in Access. That's how Access Wizards work.

    Just a little example:
    Code:
    Sub CreateForm()
    
        Dim frm As Form
        Dim ctl As Control
        Dim strName As String
        
        Set frm = Application.CreateForm
        frm.RecordSource = "Tbl_Orders"
        strName = frm.Name
        DoCmd.Close acForm, strName, acSaveYes
        DoCmd.Rename "Frm_Orders", acForm, strName
        DoCmd.OpenForm "Frm_Orders", acDesign
        Set ctl = Application.CreateControl("Frm_Orders", acComboBox, acDetail, , , 500, 500, 1720, 240)
        With ctl
            .Name = "ComboOrders"
            .ColumnCount = 3
            .RowSource = "SELECT OrderId,StartDate,EndDate FROM Tbl_Orders;"
            .LimitToList = True
            .AfterUpdate = "[Event Procedure]"
        End With
        Set frm = Forms("Frm_Orders")
        With frm.Module
            .AddFromString "Private Sub ComboOrders_AfterUpdate()" & vbNewLine & _
                           "" & vbNewLine & _
                           "    Dim rst As DAO.Recordset" & vbNewLine & _
                           "" & vbNewLine & _
                           "    Set rst = Me.RecordsetClone" & vbNewLine & _
                           "    rst.FindFirst ""OrderId="" & Me.ComboOrders.Value" & vbNewLine & _
                           "    If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark" & vbNewLine & _
                           "    rst.Close" & vbNewLine & _
                           "    Set rst = Nothing" & vbNewLine & _
                           "" & vbNewLine & _
                           "End Sub" & vbNewLine
        End With
        DoCmd.Close acForm, "Frm_Orders", acSaveYes
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    74
    Makes sense to me. Also looks daunting, though I'm definitely willing to try it.

    You made the example of creating a combobox. I'm seeing a lot of variables that are needed. For example, several textboxes where you can type desired combobox values which could then be used.

    Is there a specific way to position controls on the form in vba?

    Also, I see you create the form in design mode. Then at the end,
    Code:
     DoCmd.Close acForm, "Form_Orders", acSaveYes
    takes the form out of design mode where i can then be opened with
    Code:
     DoCmd.OpenForm Frm_Orders
    ?
    Version: Access 2010

  4. #4
    Join Date
    Mar 2009
    Posts
    5,287
    1. The position of the control is determined when it is created:
    Code:
    CreateControl(FormName, ControlType, Section, Parent, ColumnName, Left, Top, Width, Height
    It can also be changed later on, when the form is in Design view:
    Code:
    ctl.Left = ..., etc.
    2. Once the form is created and saved, you can open it the usual way:
    Code:
    DoCmd.OpenForm "FormName", ...
    Have a nice day!

  5. #5
    Join Date
    Nov 2012
    Posts
    74
    Wonderful! I'll have tons of questions down the road so I'll just bookmark this post. Thanks for the help!
    Version: Access 2010

  6. #6
    Join Date
    Mar 2009
    Posts
    5,287
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •