How to create controls at run time Access VB?

Question

How can you create controls at run time with VB code in Microsoft Access? after some digging I found that this is possible with the CreateControl function. The problem is every random forum I find online has some code similar to this:

Private Sub Button_Click()
    Call AddLabel
End Sub

Function AddLabel()
    Set ctl = CreateControl("MyForm", acLabel, acDetail, "", "", 0, 0, 100, 100)
    With ctl
       .name = "myTextBox"
       .Caption = "Hello World!"
       .Height = 50
       .Width = 100
       .FontSize = 11
       .Visible = True
    End With
End Function

but this code appears to not create a visible label.

In my case I'm just trying to learn how to get this to work. So I created a blank form with a button that when clicked will create a label that says "Hello world!". What I'm expecting to happen is a textbox will display in the top left of the form when the button is clicked. I'm curious if anyone could help show me a simple example of some code that actually works.

Before anyone says I can create a label and hide it then change its visibilty property, I know. But I'd like to know how to create controls dynamically and getting this simple example to actually work will greatly help my understanding.

Answer 1

The documentation you need is here (these are specifically for Access VBA):

• Application.CreateControl Method (Office 2007)
• Application.CreateControl Method (Office 2003)

According to the documentatin, there are some big limitations to this feature:

• Limited to 754 controls over the lifetime of the form (this is not reset by deleting them, so you are likely to run into this limit quickly)
• Must be done in Design view (so it can't be done in mde/accde)
• It is questionable how it will perform in a multi-user environment.

Because of these limitations, it is inadvisable, unless you are using to design forms initially.

Duplicate Question: How do you dynamically create controls on a MS Access form?

In response to the OP's suggestion, here is my test code which was able to add 40 controls and repeat the process 50 times without exceeding the 754 limit (I reused 40 names in my test).

Caveat 1 This is inadvisable because it can only be done in design view which will not work in an mde/accde.

Caveat 2: It is questionable how it will perform in a multi-user environment.

This code is from a form with two buttons. It opens a second form named "Form2"

Option Compare Database
Option Explicit

Private Const FORM_NAME As String = "Form2"
Private m_nCounter As Long

Private Sub cmdCreate_Click()
    runDynamicForm
End Sub

Private Sub cmdRepeat_Click()

    Dim n As Long

    m_nCounter = 0

    For n = 0 To 50
        runDynamicForm
        DoEvents
        DoCmd.Close acForm, FORM_NAME, acSaveNo
        DoEvents
    Next 'n

    MsgBox m_nCounter

End Sub

Private Sub runDynamicForm()

    Const DYNAMIC_TAG As String = "dynamic"

    Dim n As Long
    Dim frm As Form
    Dim ctl As Access.Control

    On Error GoTo EH

    Application.Echo False

    DoCmd.OpenForm FORM_NAME, acDesign
    Set frm = Application.Forms(FORM_NAME)

    For n = frm.Controls.Count - 1 To 0 Step -1
        Set ctl = frm.Controls(n)
        If ctl.Tag = DYNAMIC_TAG Then
            Application.DeleteControl FORM_NAME, ctl.Name
        End If
    Next 'n

    For n = 1 To 20

        With Application.CreateControl(FORM_NAME, acLabel, acDetail, , , 400, n * 300, 1500, 300)

            .Name = "lbl" & n
            .Caption = "Question " & n
            .Visible = True
            .Tag = DYNAMIC_TAG

        End With

        With Application.CreateControl(FORM_NAME, acTextBox, acDetail, , , 2000, n * 300, 3000, 300)

            .Name = "txt" & n
            .Visible = True
            .TabIndex = n - 1
            .Tag = DYNAMIC_TAG

        End With

        m_nCounter = m_nCounter + 2

    Next 'n

    DoCmd.Close acForm, FORM_NAME, acSaveYes

    DoCmd.OpenForm FORM_NAME, acNormal

    GoTo FINISH

EH:
    With Err
        MsgBox "Error:" & vbTab & .Number & vbCrLf _
            & "Source" & vbTab & .Source & vbCrLf _
            & .Description
    End With

FINISH:

    Application.Echo True

End Sub

Answer 2

I took that upove code and simplified it as it was long winded, and turned it into a a sample code for my own future use. Hope it helps someone in the future.

Public Sub runDynamicCreateControls()
    Dim FormName As String
    Dim NumControls As Integer
    Dim n As Long
    Dim ctl As Access.Control
    Dim ctlname As String
    On Error GoTo EH
    Application.Echo False
    FormName = "createcontrolF" 'Input Name of Form
    NumControls = 20 'input number of controls
    ctlname = "txt" 'input control name
    DoCmd.OpenForm FormName, acDesign
    For n = 1 To NumControls
        With Application.CreateControl(FormName, acTextBox, acDetail, , , 1000,1000, 1100, 600)
            .Name = ctlname & "_" & n
            .Visible = True
            .Tag = n
        End With
    Next 'n
    DoCmd.Close acForm, FormName, acSaveYes
    DoCmd.OpenForm FormName, acNormal
    GoTo FINISH
EH:
    With Err
        MsgBox "Error:" & vbTab & .Number & vbCrLf _
            & "Source" & vbTab & .Source & vbCrLf _
            & .Description
    End With
FINISH:
    Application.Echo True
End Sub

Answer 3

Whenever I attempt to run your code I get the runtime error of:

Run-time error '6062':
You must be in Design or Layout View to create or delete controls.

Based off of that information it seems like dynamically creating controls in runtime isn't going to be possible.

Answer 4

You might be only missing DoCmd.Restore, here is an example on how to dynamically create form, data bind it, and create controls, all in runtime.

Sub NewControls()
    Dim frm As Form
    Dim ctlLabel As Control, ctlText As Control
    Dim intDataX As Integer, intDataY As Integer
    Dim intLabelX As Integer, intLabelY As Integer

    ' Create new form with Orders table as its record source.
    Set frm = CreateForm
    frm.RecordSource = "Orders"
    ' Set positioning values for new controls.
    intLabelX = 100
    intLabelY = 100
    intDataX = 1000
    intDataY = 100
    ' Create unbound default-size text box in detail section.
    Set ctlText = CreateControl(frm.Name, acTextBox, , "", "", _
        intDataX, intDataY)
    ' Create child label control for text box.
    Set ctlLabel = CreateControl(frm.Name, acLabel, , _
         ctlText.Name, "NewLabel", intLabelX, intLabelY)
    ' Restore form.
    DoCmd.Restore
End Sub