How to create controls at run time Access VB?
Solution 1
The documentation you need is here (these are specifically for Access VBA):
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
Solution 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
Related videos on Youtube
user3657834
Updated on September 15, 2022Comments
-
user3657834 over 1 year
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.
-
Ňɏssa Pøngjǣrdenlarp almost 9 yearsaccess-vba <> VB.NET
-
-
user3657834 almost 9 yearsBummer. The controls really need to be added at run time in "Form View" I have a table that has questions. And on this form I'd like the form to dynamically adjust to the number of questions required. I currently have this deigned where controls are hidden and then made visible then needed. But It would be ideal to only have the correct number of controls as needed.
-
user3657834 almost 9 yearswould a workaround be to somehow quickly change the form into design view, create the controls, then quickly change back into form view? Do such VB commands exist?
-
Don Jewett almost 9 yearsThanks HansUp. Corrected.
-
Don Jewett almost 9 years@user3657834, I would guess this would result in some flashing onscreen (which you probably could get around), but real trouble is the control limit. I might play around with this to see if you use the same names each time it still increments toward your limit, but it sounds unlikely. Probably better to have a bunch of invisible controls and arrange those dynamically each time. This gets around a bunch of issues.
-
Don Jewett almost 9 years@user3657834 I was able to get it working. See updated answer.
-
PurTahan almost 5 years
DoCmd.OpenForm FormName, acDesign
has error. please help me.Property not found
3270