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

Pis7ftw · #1 (**permalink**) 02-24-13, 16:38

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?

Sinndho · #2 (**permalink**) 02-24-13, 18:13

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

Pis7ftw · #3 (**permalink**) 02-24-13, 21:08

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

?

Sinndho · #4 (**permalink**) 02-25-13, 03:27

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

Pis7ftw · #5 (**permalink**) 02-25-13, 21:07

Wonderful! I'll have tons of questions down the road so I'll just bookmark this post. Thanks for the help!

Sinndho · #6 (**permalink**) 02-26-13, 02:20

You're welcome!

Internet Services

Web Development

Digital Marketing

Consumer Tech