How do I create Access VB Runtime controls?
How do I create controls at runtime with VB code in Microsoft Access? after some digging, I found that this is possible with a function CreateControl
. The problem is that every random forum I have found on the internet has code similar to this one:
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 doesn't create a visible label.
In my case, I'm just trying to figure out how to get this to work. So I created an empty form with a button that, when clicked, will create a shortcut that says "Hello world!" What I expect this textbox will be displayed in the upper left corner of the form when the button is clicked. I am curious if anyone can help show me a simple example of code that actually works.
Before anyone says that I can create a shortcut and hide it then change its visibilty property, I know. But I would like to know how to create controls dynamically and get this simple example to actually work will greatly help my understanding.
source to share
The required documentation is here (this is specifically for Access VBA):
According to the doc, there are some big limitations for this feature:
- Limited to 754 form lifetime controls (this is not reset by deleting them, so you will most likely run into this limit quickly)
- Must be done in design mode (so it cannot be done in mde / accde)
- There is no doubt that it will work in a multi-user environment.
Due to these limitations, it is not practical if you are not using forms natively to design.
Recurring question: How do you dynamically create controls in an MS Access form?
In response to the OP's suggestion, here is my test code that was able to add 40 controls and repeat the process 50 times without exceeding the 754 limit (I used 40 names in my test).
Caveat 1 This is impractical because it can only be done in a design that won't work in 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
source to share
I took this upove code and simplified it as it was long and turned it into sample code for my own future use. Hope this 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
source to share
You might be missing DoCmd.Restore
, here is an example of how to dynamically create a form, bind data and create controls at 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
source to share