none
how to add labels dynamically to a form

    Question

  • Tools: MS Access 2007

    Environment: Windows Vista

    Problem:

    The following code works fine. However, I would like to add a command line, which generates n label objects of which each label stores the individual date. for example, after selecting 12/31/2012 from picker date clicking on submit button, it generates 7 dates in one label as follows: 1/1/2013, 1/2/2013, 1/3/2013,...1/7/2013. The dates are listed on top of each one.

    Private Sub cmd_Click()

    Dim mylbls(7) As Label lbl.Caption = "" Dim I As Integer I = 0 For I = 7 To 1 Step -1 lbl.Caption = DateAdd("d", I, Format(Me.DTPicker.Value, "dd/mmm/yy")) & " " & lbl.Caption Next End Sub


    The desired outcome is to display  1/1/2013 in label1, 1/2/2013 in labels, 1/3/2013 ....and 1/7/2013 in label7

    in horizontal order. I am curious to see VBA code.  





    • Edited by Sandra VO Thursday, December 13, 2012 11:37 PM
    Wednesday, December 12, 2012 11:58 PM

Answers

  • sandra V O

    In Access it does depend on the definition of ‘add labels dynamically’.

    It can be done but it is not recommended for two very good reasons:
    1.) The controls which are added may eventually exceed the number of controls which can be added over the life of the Form.
    2.) Adding controls dynamically implies going into design mode and that can’t be done in an MDE file.

    Usually what is done is to create the controls before hand and control the properties of those controls under program control.

    For a small number of controls they can be created manually but it does become tedious for a large numbers of controls. I mean pick a number; but 50 to 700 becomes tedious.

    Therefore, it becomes desirable to be able to create the controls required before they are used and import those controls to the Form which will use them. The creation of those controls can be done automatically in design view and once imported to the user Form can be used without going into design view of the user Form at run time.

    If you get a particular bunch of controls wrong then simply create a new bunch with the appropriate default parameters.

    So, to create a bunch of controls for import into another Form:-

    Option Explicit
    Option Compare Text
    
    
    Private Sub MakeSomeControls()
    
        ' Control Type examples: acRectangle, _
                                 acTextBox, _
                                 acLabel, _
                                 acCheckBox
        
        ' Example run...
        MakeControls ControlType:=acLabel, _
                     NumberOfControls:=500, _
                     ControlLeft:=1000, _
                     ControlTop:=1000, _
                     ControlWidth:=200, _
                     ControlHeight:=200, _
                     GroupName:="Obj_", _
                     GroupNameBase:=0
    
    End Sub
    
    
    Private Sub MakeControls(ByVal ControlType As Long, _
                             ByVal NumberOfControls As Long, _
                             ByVal ControlLeft As Long, _
                             ByVal ControlTop As Long, _
                             ByVal ControlWidth As Long, _
                             ByVal ControlHeight, _
                             ByVal GroupName As String, _
                             ByVal GroupNameBase As Long)
    
        Dim frmNewForm      As Access.Form
        Dim ctlNewControl   As Access.Control
        Dim lngControlIndex As Long
    
        If NumberOfControls > 750 Then
            MsgBox "Hmmm, not looking good, too many controls."
        Else
            ' Create a new Form, it forces the created controls to start at 0.
            Set frmNewForm = CreateForm
    
            ' Create the controls.
            ' The controls are in Z order of first created at back, last at front.
            ' Let most of the properties default, they can be mass changed later.
            For lngControlIndex = 0 To NumberOfControls - 1
                Set ctlNewControl = CreateControl(frmNewForm.Name, _
                                        ControlType, _
                                        acDetail, _
                                        "", _
                                        "", _
                                        ControlLeft, _
                                        ControlTop, _
                                        ControlWidth, _
                                        ControlHeight)
            Next lngControlIndex
    
            ' Normally default the name to starting at index 0, it fits arrays better.
            ' For some controls, starting at index 1, Day of year???, may be better.
            ' Rename the controls using the passed GroupName and GroupNameBase index.
            For lngControlIndex = 0 To NumberOfControls - 1
                With frmNewForm.Controls(lngControlIndex)
                    .Name = GroupName & CStr(lngControlIndex + GroupNameBase)
                    .BorderStyle = 1
    
                '    Add extra stuff as required.
                '   .ForeColor = vbWhite
                '   .BackColor = vbRed
                '   .BackStyle = 1
                End With
            Next lngControlIndex
    
            DoCmd.Close acForm, frmNewForm.Name, acSaveYes
        End If
    
    End Sub
    
    

     

    Or if you prefer an Access 2003 download:-

    https://skydrive.live.com/#cid=55AF3BDE0AE0E03A&id=55AF3BDE0AE0E03A!442


    Chris.

         

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive




    Thursday, December 20, 2012 4:35 AM

All replies

  • Tools: MS Access 2007

    Environment: Windows Vista

    Problem:

    The following code works fine. However, I would like to add a command line, which generates n label objects of which each label stores the individual date. for example, after selecting 12/31/2012 from picker date clicking on submit button, it generates 7 dates in one label as follows: 1/1/2013, 1/2/2013, 1/3/2013,...1/7/2013. The dates are listed on top of each one.

    Private Sub cmd_Click()

    Dim mylbls(7) As Label lbl.Caption = "" Dim I As Integer I = 0 For I = 7 To 1 Step -1 lbl.Caption = DateAdd("d", I, Format(Me.DTPicker.Value, "dd/mmm/yy")) & " " & lbl.Caption Next End Sub


    The desired outcome is to display  1/1/2013 in label1, 1/2/2013 in labels, 1/3/2013 ....and 1/7/2013 in label7

    in horizontal order. I am curious to see VBA code.  



    Hi Sandra,

    If your labels are named "Label1", "Label2", etc., then yould could do something like:

    For I = 1 To 7
      Me("Label" & I).Caption = DateAdd("d", I, Format(Me.DTPicker.Value, "dd/mmm/yy"))
    Next

    Imb.

    Thursday, December 13, 2012 10:34 AM
  • Thanks for the response. It did not work; MS office Access can not find the field referred to in your expression. I want to generate label as objects such as label1, label2, label3,....label7. Each label has properties. for example, I can assign the value to label6.caption = "Hi World". 


    • Edited by Sandra VO Thursday, December 13, 2012 4:36 PM
    Thursday, December 13, 2012 4:23 PM
  • Thanks for the response. It did not work; MS office Access can not find the field referred to in your expression. I want to generate label as objects such as label1, label2, label3,....label7. Each label has properties. for example, I can assign the value to label6.caption = "Hi World". 


    Hi Sandra,

    Strange, because this is how I assign values to all the label captions in all my forms.

    What are the names of the Labels? By the way, a field is something else than a label.

    Imb.

     

    Thursday, December 13, 2012 4:48 PM
  • I am trying to create 7 labels as form control in in the form. 

    The following code might give you an illustration of what am trying to achieve. (does not work)

    Private Sub cmd_Click()
    Dim mylbls(6) As Label
    
    lbl.Caption = ""
    mylbls(0).Caption = ""
    
    Dim I As Integer
    I = 0
    For I = 7 To 1 Step -1
    lbl.Caption = DateAdd("d", I, Format(Me.DTPicker.Value, "dd/mmm/yy")) & " " & lbl.Caption
    
    mylbls(I).Caption = Now()
    
    Next
    
    End Sub

    Thursday, December 13, 2012 5:12 PM
  • I am trying to create 7 labels as form control in in the form. 

    The following code might give you an illustration of what am trying to achieve. (does not work)

    Private Sub cmd_Click()
    Dim mylbls(6) As Label
    
    lbl.Caption = ""
    mylbls(0).Caption = ""
    
    Dim I As Integer
    I = 0
    For I = 7 To 1 Step -1
    lbl.Caption = DateAdd("d", I, Format(Me.DTPicker.Value, "dd/mmm/yy")) & " " & lbl.Caption
    
    mylbls(I).Caption = Now()
    
    Next
    
    End Sub

    Hi Sandra,

    What you have is a label, named  "lbl". At least, you can assign a value to its caption.

    Further you declare an array of labels, but these labels are not connected to the form. They are only known in memory within the Sub Cmd_click.

    So you better create the labels Label1, Label2, etc. on your form, eventually couple them to the controls of the fields, and then assign a caption value as I did in my example.

    Imb.

    Thursday, December 13, 2012 5:32 PM
  • I can not see what your saying. I feel am in the dark. If you could adjust/add code line , It would help me to learn and others to do so. I also not interested in creating manually label1, label2...I want to create dynamic labels to store data while am running loop statement or reading from dataset record in the DB.

    • Edited by Sandra VO Thursday, December 13, 2012 6:25 PM
    Thursday, December 13, 2012 6:08 PM
  • I can not see what your saying. I feel am in the dark. If you could adjust/add code line , It would help me to learn and others to do so. I also not interested in creating manually label1, label2...I want to create dynamic labels to store data while am running loop statement or reading from dataset record in the DB.

    Hi Sandra,

    Probably we talk about two differents things.

    For me a Label is a control on a form, that you can use alone or in conjunction with a textbox. In the latter case the textbox holds the value of a field, and the label caption can be used to display the fieldname.

    As far as I know adding labels dynamically to a form in runtime is not possible.

    Imb.

    Thursday, December 13, 2012 9:13 PM
  • Hello Sandra, Imb-hb.

    The process of building a name for each new control to add is similar to what was already shown. To add new controls, you use the Add method of the form's Controls (or another control that can act as a container, e.g. Frame) property. Here is a snippet of code that I use in another program. I use this in a sub that calls the form, so it gets added during the form.Load process. If you're going to do this after the form is loaded, you'll need to call Form.Repaint(). This is actually from AutoCAD, so you might see some objects that don't apply. Just focus on the loops that add the controls. Also note the syntax for the Controls.Add method. See the developer docs for more info on that method.

        If cnt > 0 Then
            Set colTextEnts = New TextEnts
            colTextEnts.Add_SS ss
            'do form set up
            iTop = 8
            'set collection to form properties
            frm.TextItems = colTextEnts
            frm.lblCount = cnt
            Set ctrls = frm.Frame1.Controls
            'since items on form are numbered beginning with 1,
            'make list start at 1
            For i = 1 To cnt
                Set objText = colTextEnts.Item(i)
                'create labels
                Set lbl = ctrls.Add("Forms.Label.1", "lbl" & i)
                With lbl
                    .Caption = "Item " & i & ":"
                    .Font = fnt1
                    .Height = 11.25
                    .Left = 7.5
                    .TextAlign = fmTextAlignLeft
                    .top = iTop
                    .Width = 42
                End With
                'create text boxes
                Set tbx = ctrls.Add("Forms.TextBox.1", "tbx" & i)
                With tbx
                    .Font = fnt2
                    .Height = 15.75
                    .Left = 56
                    .TextAlign = fmTextAlignLeft
                    .Text = objText.TextString
                    .top = iTop
                    .Width = 180
                    iTop = iTop + 18
                    If i = 1 Then Set tbx1 = tbx
                End With
            Next i


    Ed



    • Edited by Ed57gmc Thursday, December 13, 2012 10:36 PM
    • Proposed as answer by Dummy yoyoModerator Wednesday, December 19, 2012 7:19 AM
    Thursday, December 13, 2012 10:27 PM
  • The process of building a name for each new control to add is similar to what was already shown. To add new controls, you use the Add method of the form's Controls (or another control that can act as a container, e.g. Frame) property. Here is a snippet of code that I use in another program. I use this in a sub that calls the form, so it gets added during the form.Load process. This is actually from AutoCAD, so you might see some objects that don't apply. Just focus on the loops that add the controls. Also note the syntax for the Controls.Add method. See the developer docs for more info on that method.

    Hi Ed,

    Thank you for your answer, but most for the refreshing of my memory. I now remember that I have been experimenting with the addition of controls dynamically.

    I use generalised forms. That means that every continuous form in every application is exactly the same. The difference in appearence of the forms is because of the position and dimensions of the controls and its ControlSource.

    Dynamically adding controls seemed the solution for this way of workig. But also dynamically adding all the code for all kinds of events was just one step to far. That is why I decided at that time not to add controls dynamically, but use static pre-defined (hidden) controls and defined generalised code for the events, and activate the controls when needed. This approach still works great for me. And then you forget sometimes all the other possiblities.

    Imb.

    Thursday, December 13, 2012 11:07 PM
  • Hi Ed, Thanks for taking the time to share snippet code with us. I am curious to see your code running in access file. therefore, I wonder if there is a chance to share an access file which contains your code. It will help me to see how the commands have been used, named, and passed. Thanks
    Thursday, December 13, 2012 11:45 PM
  • It wouldn't vary much from what I already gave you. The meat is in the For loop.

    The steps are:

    1. Add the label.
    2. Modify the label's properties.
    3. Repaint the form.

    In the code above, I also add textboxes within the same loop.


    Ed

    Friday, December 14, 2012 12:22 AM
  • Someone gave me this code to produce the captions shown in the image below -- 

    The Toggle buttons are numbered Toggle3 through Toggle14 to show 12 months.

    Private Sub Form_Current()

      Dim intLoop As Integer
      For intLoop = 0 To 11
        Me.Controls("Toggle" & intLoop + 3).Caption = _
          Format(DateAdd("m", -intLoop, Date), "mmmm yyyy")
      Next intLoop
    End Sub



    • Edited by KARL DEWEY Friday, December 14, 2012 4:52 AM
    Friday, December 14, 2012 12:59 AM
  • Someone gave me this code to produce the captions shown in the image below -- 

    The Toggle buttons are numbered Toggle3 through Toggle14 to show 12 months.

    Private Sub Form_Current()

      Dim intLoop As Integer
      For intLoop = 0 To 11
        Me.Controls("Toggle" & intLoop + 3).Caption = _
          Format(DateAdd("m", -intLoop, Date), "mmmm yyyy")
      Next intLoop
    End Sub

    Hi Karl,

    This is the static way, as I already suggested. But apparently the OP wants the dynamical way, in the line of Ed advice.

    Imb.

    Friday, December 14, 2012 8:07 AM
  • What I posted is dynamic in the it uses the date to create labels.  The same could be use with some other input such as an unbound text box on a form.

    They wanted 7 days of labels.  So enter first date in the text box and let it do the talking.

    Wednesday, December 19, 2012 10:12 PM
  • What I posted is dynamic in the it uses the date to create labels.

    Not really; it doesn't create the controls, it assigns values to pre-existing controls.  What the OP apparently wants to do is add new controls to the form's Controls collection and assign a value to their caption property of each.  Perhaps she has a cogent rationale for this, but if so I can't imagine what it might be.  As the number of controls is a constant seven, I'd have thought it would make more sense simply to set the Visible value of pre-existing controls to False by default and make them visible and assign a value to the Caption property at runtime.  If necessary their position and size can also be amended at runtime.

    Ken Sheridan, Stafford, England

    Wednesday, December 19, 2012 11:16 PM
  • sandra V O

    In Access it does depend on the definition of ‘add labels dynamically’.

    It can be done but it is not recommended for two very good reasons:
    1.) The controls which are added may eventually exceed the number of controls which can be added over the life of the Form.
    2.) Adding controls dynamically implies going into design mode and that can’t be done in an MDE file.

    Usually what is done is to create the controls before hand and control the properties of those controls under program control.

    For a small number of controls they can be created manually but it does become tedious for a large numbers of controls. I mean pick a number; but 50 to 700 becomes tedious.

    Therefore, it becomes desirable to be able to create the controls required before they are used and import those controls to the Form which will use them. The creation of those controls can be done automatically in design view and once imported to the user Form can be used without going into design view of the user Form at run time.

    If you get a particular bunch of controls wrong then simply create a new bunch with the appropriate default parameters.

    So, to create a bunch of controls for import into another Form:-

    Option Explicit
    Option Compare Text
    
    
    Private Sub MakeSomeControls()
    
        ' Control Type examples: acRectangle, _
                                 acTextBox, _
                                 acLabel, _
                                 acCheckBox
        
        ' Example run...
        MakeControls ControlType:=acLabel, _
                     NumberOfControls:=500, _
                     ControlLeft:=1000, _
                     ControlTop:=1000, _
                     ControlWidth:=200, _
                     ControlHeight:=200, _
                     GroupName:="Obj_", _
                     GroupNameBase:=0
    
    End Sub
    
    
    Private Sub MakeControls(ByVal ControlType As Long, _
                             ByVal NumberOfControls As Long, _
                             ByVal ControlLeft As Long, _
                             ByVal ControlTop As Long, _
                             ByVal ControlWidth As Long, _
                             ByVal ControlHeight, _
                             ByVal GroupName As String, _
                             ByVal GroupNameBase As Long)
    
        Dim frmNewForm      As Access.Form
        Dim ctlNewControl   As Access.Control
        Dim lngControlIndex As Long
    
        If NumberOfControls > 750 Then
            MsgBox "Hmmm, not looking good, too many controls."
        Else
            ' Create a new Form, it forces the created controls to start at 0.
            Set frmNewForm = CreateForm
    
            ' Create the controls.
            ' The controls are in Z order of first created at back, last at front.
            ' Let most of the properties default, they can be mass changed later.
            For lngControlIndex = 0 To NumberOfControls - 1
                Set ctlNewControl = CreateControl(frmNewForm.Name, _
                                        ControlType, _
                                        acDetail, _
                                        "", _
                                        "", _
                                        ControlLeft, _
                                        ControlTop, _
                                        ControlWidth, _
                                        ControlHeight)
            Next lngControlIndex
    
            ' Normally default the name to starting at index 0, it fits arrays better.
            ' For some controls, starting at index 1, Day of year???, may be better.
            ' Rename the controls using the passed GroupName and GroupNameBase index.
            For lngControlIndex = 0 To NumberOfControls - 1
                With frmNewForm.Controls(lngControlIndex)
                    .Name = GroupName & CStr(lngControlIndex + GroupNameBase)
                    .BorderStyle = 1
    
                '    Add extra stuff as required.
                '   .ForeColor = vbWhite
                '   .BackColor = vbRed
                '   .BackStyle = 1
                End With
            Next lngControlIndex
    
            DoCmd.Close acForm, frmNewForm.Name, acSaveYes
        End If
    
    End Sub
    
    

     

    Or if you prefer an Access 2003 download:-

    https://skydrive.live.com/#cid=55AF3BDE0AE0E03A&id=55AF3BDE0AE0E03A!442


    Chris.

         

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive




    Thursday, December 20, 2012 4:35 AM
  • sandra V O

    In Access it does depend on the definition of ‘add labels dynamically’.

    It can be done but it is not recommended for two very good reasons:
    1.) The controls which are added may eventually exceed the number of controls which can be added over the life of the Form.
    2.) Adding controls dynamically implies going into design mode and that can’t be done in an MDE file.

    Usually what is done is to create the controls before hand and control the properties of those controls under program control.

    For a small number of controls they can be created manually but it does become tedious for a large numbers of controls. I mean pick a number; but 50 to 700 becomes tedious.

    Therefore, it becomes desirable to be able to create the controls required before they are used and import those controls to the Form which will use them. The creation of those controls can be done automatically in design view and once imported to the user Form can be used without going into design view of the user Form at run time.

    If you get a particular bunch of controls wrong then simply create a new bunch with the appropriate default parameters.

    So, to create a bunch of controls for import into another Form:-

    Option Explicit
    Option Compare Text
    
    
    Private Sub MakeSomeControls()
    
        ' Control Type examples: acRectangle, _
                                 acTextBox, _
                                 acLabel, _
                                 acCheckBox
        
        ' Example run...
        MakeControls ControlType:=acLabel, _
                     NumberOfControls:=500, _
                     Control                 Control                 ControlWidth:=200, _
                     ControlHeight:=200, _
                     GroupName:="Obj_", _
                     GroupNameBase:=0
    
    End Sub
    
    
    Private Sub MakeControls(ByVal ControlType As Long, _
                             ByVal NumberOfControls As Long, _
                             ByVal ControlLeft As Long, _
                             ByVal ControlTop As Long, _
                             ByVal ControlWidth As Long, _
                             ByVal ControlHeight, _
                             ByVal GroupName As String, _
                             ByVal GroupNameBase As Long)
    
        Dim frmNewForm      As Access.Form
        Dim ctlNewControl   As Access.Control
        Dim lngControlIndex As Long
    
        If NumberOfControls > 750 Then
            MsgBox "Hmmm, not looking good, too many controls."
        Else
            ' Create a new Form, it forces the created controls to start at 0.
            Set frmNewForm = CreateForm
    
            ' Create the controls.
            ' The controls are in Z order of first created at back, last at front.
            ' Let most of the properties default, they can be mass changed later.
            For lngControlIndex = 0 To NumberOfControls - 1
                Set ctlNewControl = CreateControl(frmNewForm.Name, _
                                        ControlType, _
                                        acDetail, _
                                        "", _
                                        "", _
                                        ControlLeft, _
                                        ControlTop, _
                                        ControlWidth, _
                                        ControlHeight)
            Next lngControlIndex
    
            ' Normally default the name to starting at index 0, it fits arrays better.
            ' For some controls, starting at index 1, Day of year???, may be better.
            ' Rename the controls using the passed GroupName and GroupNameBase index.
            For lngControlIndex = 0 To NumberOfControls - 1
                With frmNewForm.Controls(lngControlIndex)
                    .Name = GroupName & CStr(lngControlIndex + GroupNameBase)
                    .BorderStyle = 1
    
                '    Add extra stuff as required.
                '   .ForeColor = vbWhite
                '   .BackColor = vbRed
                '   .BackStyle = 1
                End With
            Next lngControlIndex
    
            DoCmd.Close acForm, frmNewForm.Name, acSaveYes
        End If
    
    End Sub
    

     

    Or if you prefer an Access 2003 download:-

    https://skydrive.live.com/#cid=55AF3BDE0AE0E03A&id=55AF3BDE0AE0E03A!442


    Chris.

         

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive




    I am currently working on this issue. My goal is to generate report as below.

    The following snap shot is a schedule for people who are training programming languages such as ASP.Net. Each day has 7 cubs. Each cube represent a room. The cube consist of fields, which is designed to show  class number, teacher name, subing teacher and the time frame of each class in that


    • Edited by Sandra VO Wednesday, January 09, 2013 6:59 PM
    Friday, December 21, 2012 8:07 PM
  • The following snap shot is a schedule for people who are training programming languages such as ASP.Net. Each day has 7 cubs. Each cube represent a room. The cube consist of fields, which is designed to show  class number, teacher name, subing teacher and the time frame of each class in that

    Hi Sandra,

    This kind of problems I have solved succesfully with one main form that contains many instances of the same subform. The subform contains the part Class/T1/T2/S/08:30/09:15/10:00, and is repeated "any time" on the main form.

    Think about it.

    Imb.

    • Marked as answer by Dummy yoyoModerator Thursday, December 27, 2012 6:33 AM
    • Unmarked as answer by Sandra VO Wednesday, January 09, 2013 7:01 PM
    Friday, December 21, 2012 8:56 PM
  • The following snap shot is a schedule for people who are training programming languages such as ASP.Net. Each day has 7 cubs. Each cube represent a room. The cube consist of fields, which is designed to show  class number, teacher name, subbing teacher and the time frame of each class in that

    Hi Sandra,

    This kind of problems I have solved successfully with one main form that contains many instances of the same subform. The subform contains the part Class/T1/T2/S/08:30/09:15/10:00, and is repeated "any time" on the main form.

    Think about it.

    Imb.

    Imb. Happy new years. May your 2013 full of Access achievements ;) . I am curious to look at the code that you have written, which similar to my problem. People could benefit from this as well. I look forward to hearing from you

    Wednesday, January 09, 2013 6:58 PM
  • Imb. Happy new years. May your 2013 full of Access achievements ;) . I am curious to look at the code that you have written, which similar to my problem. People could benefit from this as well. I look forward to hearing from you

    Hi Sandra,

    Thank you, may your 2013 become even better than that.

    On this moment there is not much code to share. But you can make an example yourself.

    Make a subform that has the structure for one day.
    Further make a main form, with on that form 7 controls for the seven days of the week. Only then you need some code to address which date belongs to what subform control, but this code is very specific for your own situation.

    Imb.

    Wednesday, January 09, 2013 8:50 PM