CODEWITHSUNDEEP
vbams-accessms-access-2010
TSCAmerica.com
TSCAmerica.com

Reputation: 5377

Ms Access - VBA - Create Labels Programatically with Size

I am trying to create Labels & Textboxes and assign some values to it dynamically, depending on the variable count NoOfRecords(The problem is that I don't know in advance how many controls I'll need. The number will be driven by how many records there are in a particular table). My Form name is frmDashboard

What I tried is

Set cNN = Nothing
Set rsfnum = Nothing
Dim strconnfnum As String
Set cNN = CurrentProject.Connection
Set rsfnum = New ADODB.Recordset

strconnfnum = "SELECT nz(employeename,'') as employeename from employees"
rsfnum.Open strconnfnum, cNN, adOpenKeyset, adLockOptimistic

'Number of Records in Employees tables

NoOfRecords = rsfnum.RecordCount


    For x = 1 To NoOfRecords
        Set ctrl = CreateControl("frmDashboard", acLabel, acDetail, , "", 0 + (x * 300), 0, 300, 240)
        ctrl.ControlName = "lblDynamic_control_" & x
       Controls("lblDynamic_control_" & x).Caption = x
        Set ctrl1 = CreateControl("frmDashboard", acTextBox, acDetail, , "", 0 + (x * 300), 0, 300, 240)
        ctrl1.ControlName = "txtDynamic_control_" & x  
       Controls("txtDynamic_control_" & x).Value= x
    Next x

There are 2 issues i am facing here

1) How to show label and textboxes one after another like below (Next Label and Textbox should be exactly below the top.)

enter image description here

2) The above code throws the following error

enter image description here

Upvotes: 4

Views: 4225

Answers (2)

Brad
Brad

Reputation: 12255

The error you're seeing is correct and cannot be overcome when you eventually distribute your DB as a compiled, split database.

The trick here is going to be to create all the controls you could possibly need ahead of time. You'll need to label then and have them be in order on the form. So like Text1, Text2, Label, Label2. This way you can loop through each of the textbox/label combo by their index (which will be the index of your field in your recordset).

enter image description here

Private Function ReBindControls()

    Dim rs As DAO.Recordset 'if you are using ADO then replace this with ADODB.Recordset 
    If IsNull(Combo99) Then
        Exit Function
    End If
    Set rs = CurrentDb.OpenRecordset(Combo99) ' If you are using ADO use the appropriate ADO method to open the recordset

    Dim fs As DAO.Fields 'if you are using ADO then replace this with ADODB.Fields
    Set fs = rs.Fields

    Dim f As Integer
    Dim aLabel As Label, aTextBox As TextBox

    Set Me.Recordset = rs

    For f = 0 To fs.Count - 1
        Set aLabel = Controls("Label" & f)
        aLabel.Caption = fs(f).Name
        aLabel.Visible = True

        Set aTextBox = Controls("text" & f)
        aTextBox.ControlSource = fs(f).Name
        aTextBox.Visible = True

        aLabel.Move 1 * 1440, f * 1440 / 2
        aTextBox.Move 2.5 * 1440, f * 1440 / 2
    Next f

End Function


Function clearBindings()
    Dim c As Integer
    Dim aLabel As Label, aTextBox As TextBox

    For c = 0 To maxIndexOfControls
        Set aTextBox = Controls("text" & c)
        aTextBox.ControlSource = ""
        Set aLabel = Controls("Label" & c)
        aLabel.Visible = False
        aTextBox.Visible = False
        aLabel.Move 0, 0
        aTextBox.Move 0, 0

    Next c
End Function

If you call both of these together

Private Sub Combo99_Change()
    clearBindings
    ReBindControls
End Sub

you can get these results

enter image description here enter image description here

Upvotes: 2

CuriousLilFella
CuriousLilFella

Reputation: 1

OK - disclaimer I'm a complete newbie here - but I looked into this and apparently VBA won't let you create controls (i.e. the 'set ctrl' or 'set ctrl1' commands) at runtime. Those have to be created at design time and made visible based on the dynamic input at runtime.

If there's a max value for NoOfRecords I might suggest pre-coding ALL of the possible ctrls (using a loop) at design-time and making the number that become visible/active based on the dynamic input at runtime.

As for organizing the layout of labels and textboxes, that would also be done as a part of that designing loop. As noted in the documentation below, the placement of the upper-left corner of each ctrl can be specified in the CreateControl method in the 'left' and 'top' commands (you have it currently as left = 0+(x*300), top = 0 ). Just add the necessary adjustments to x-and-y coordinates as you iterate through ctrl creation.

CreateControl method documentation: https://msdn.microsoft.com/en-us/library/office/aa221167%28v=office.11%29.aspx

Hope that helps!

Upvotes: 0

Related Questions