locked
Creating a Form dynamically RRS feed

  • Question

  • Hi, 

    I am trying to dynamically add a Label, Button and TextBox on a form in Microsoft Access with visual Basic and i am not getting any error and no field is added when i run the program. I am not sure what the problem might be. 

    Your help will be highly appreciated.

    'MY CODE FOR ADDING BUTTON

    Private Sub FormFooter_Click() Dim button As String Dim button As New button button.Width = 200 button.Height = 50 'button3.Font = New Font("Palatino Linotype", 9) 'button3.Location = New Point(150, 400) button.Text = "Cancel" button.Visible = True Controls.Add (button) End Sub


    Friday, July 19, 2019 9:00 AM

Answers

  • Maybe this reference will get you started:

    https://docs.microsoft.com/en-us/office/vba/api/access.application.createcontrol

    I created a Public Function for you based on this reference. If you have a general module already created or create a new module and copy the following Public Function into it, then you can Call CreateUnboundTextBox with the On Open Event for any form and the function will:

    1. Open the active form in Design View
    2. Add a new TextBox control based upon the CreateControl criteria below (change the criteria as you wish)
    3. Save the form
    4. Name the new text box "NewText" (change this as you wish and change any of the other properties as well)
    5. open the active form in form view

    Public Function CreateUnboundTextBoxControl()
    Dim CurrentForm As String
    CurrentForm = Screen.ActiveForm.Name
    DoCmd.OpenForm CurrentForm, acDesign, , , acFormEdit, acWindowNormal
    Dim NewTextControl As Control
    Set NewTextControl = CreateControl(CurrentForm, acTextBox, acDetail, , , 500, 500, 500, 500)
    DoCmd.RunCommand acCmdSave
    With NewTextControl
        .Name = "NewText"
    End With
    DoCmd.OpenForm CurrentForm
    End Function

    Monday, July 22, 2019 4:19 PM
  • Hi Daniel,

    Thank you for the response.

    I am creating a screen that has multiple Labels, Textboxes, a few Buttons and Listbox.

    The Reason why i want to create the fields dynamically is i don't want to go to the code every time when there is a new item that needs to be added. I want  the items to be added automatically.

    I have a json array that i am using to pull the items.

    Hope it makes sense.

    Violet:

    I don't know what a json array is, but even though ACCESS gives you the ability to create controls on a form or report using VBA code does not mean you should do that. I have to agree with Imb_hb and Daniel that it is a bad idea for many reasons. You do not need to "go to the code" every time a control needs to be added. Just open the form in Design mode and add it from the Design tab on the Ribbon. Just click on the type of control you want to add and then draw it on the form (or report) where you want it using your mouse.

    I would strongly encourage you to develop forms in Design mode without using VBA code to add controls. At some point you could hit the control limit that ACCESS allows for forms and reports and you would need to start all over creating your form.

    The reference I gave you is Microsoft's. It is just an example. You should NOT attempt to use it line-for-line in your project. It creates a new form each time and new controls on each form and then sets the Record Source for the form to a Record Source that you don't even have. No wonder you are getting errors. You didn't say what error you received but I suspect it was on the frm.RecordSource="Orders" line. Do you know how to step through lines of code to find the error or how to set a breakpoint?

    Since you stated this is the first time you are attempting to use ACCESS VBA code I would not attempt to create forms and controls this way. Create your forms and controls using the form wizard to get a feel for how ACCESS works first. Do not use VBA code to create forms and controls. I have been developing ACCESS projects since 1999 and have NEVER used VBA code to create forms or controls. Never. In fact the Public  Function I wrote in my reply is the first time I ever have tried to create a control on a form using VBA and I suspect most developers have had very little need to do that either. It would be only in a most extraordinary circumstance and I can't even think of one.

    Please just develop your project forms and controls using normal methods first. You will be glad you did.

    Wednesday, July 24, 2019 3:23 PM

All replies

  • I am trying to dynamically add a Label, Button and TextBox on a form in Microsoft Access with visual Basic and i am not getting any error and no field is added when i run the program. I am not sure what the problem might be.

    Hi Violet,

    An alternative could be to define the controls in design mode as small, hidden controls somewhere on the Footer.

    In the Open event of the form you then can unhide the controls, dimension (Width and Height) them to the preferred sizes, place them (Top and Left) where you want, and assign values to other properties of the control(s).

    Imb.

    Friday, July 19, 2019 10:54 AM
  • Hi, 

    I am still not certain about the response, i tried but still failing. My main goal is to hard code the controls not drag and drop them.

    Monday, July 22, 2019 7:46 AM
  • I am still not certain about the response, i tried but still failing. My main goal is to hard code the controls not drag and drop them.

    Hi Violet,

    The controls as such are "hardcoded", because they are already in the form (or Footer).

    Then appearance of the controls are tuned during opening the form. This can be done with some code in the Open event of the form.

    The values for the different control properties (e.g. vivibility, top, left, height, width, …) can be placed in a table, so by just changing the values in the table, you can modify the appearance of the control.

    Perhaps you can give an example of what you tried that did not work.

    Imb.

    Monday, July 22, 2019 8:08 AM
  • This is what i have tried and when i debug i get " Method or data member not found.." error

    Private Sub FormHeader_Click()
    Dim label As New label
            label.Width = 100
            label.Height = 50
            label.Text = "PDA Create/Maintain"
            label.Visible = True
            Controls.Add (label)
            'label .Font = New Font("Palatino Linotype", 9)
            'label.Location = New Point(150, 400)
    End Sub 

    Monday, July 22, 2019 9:11 AM
  • Hi Violet,

    The control "Label" should already be added to the form in form design, and saved.

    Then in the Open event of the form you add:

            label.Width = 100
            label.Height = 50
            label.Text = "PDA Create/Maintain"
            label.Visible = True
            label.Left = 50

    Remember, the values for Width and Height are in twips, with 1440 twips / inch of 567 twips / cm.

    You also better give the label a more meaningful name, especially when you will have more "labels".  

    Imb,

    Monday, July 22, 2019 9:47 AM
  • Hi, 

    I am now getting the logic. So isn't there a way to dynamically add the control "Label" on the Open Event of the Form?

    You are saying in form design i must drag and drop the Label and change its properties in the Open Event of the Form?

    Hope i make sense. 

    Monday, July 22, 2019 10:06 AM
  • It isn't a good idea to finally create controls.  Perhaps if you explains the why behind your request, we could offer a better approach.

    In rare occasions when the need did exist, having the controls already created, but hidden was always the way to handle the situation.  Then, at runtime, you simply make them visible and position then as required.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, July 22, 2019 1:26 PM
  • >So isn't there a way to dynamically add the control "Label" on the Open Event of the Form?

    Way always is - all forms are build dynamically,

    Do not known a version of Access and how it work with additional controls, but may suggest only to run Invalidate() (or what ever is there) on form/footer/header - you need to re-paint the form after you add control.


    Sincerely, Highly skilled coding monkey.

    Monday, July 22, 2019 2:33 PM
  • So isn't there a way to dynamically add the control "Label" on the Open Event of the Form?

    Hi Violet,

    Probably there are ways, but I never used them. In fact, I use a (generalized) form with controls already defined, and "activate" (or made visible) in the Open event of the form.

    The controls are then tuned with the proper values of their properties. So the "dragging" is done automatically with the proper values of Top, Left, Height and Width.

    In this way I make thousands of different "dynamical" forms in 100+ different applications, just by tuning the appropriate controls on one and the same form, in the way they are needed.

    In form design I have swiped all controls more or less in a corner. The are invisible and do no harm. Only on "activation" the become visible with the right dimensions on the right place. But the realization possibilities are almost unlimited.

    Imb.



    • Edited by Imb-hb Monday, July 22, 2019 2:59 PM
    Monday, July 22, 2019 2:57 PM
  • Maybe this reference will get you started:

    https://docs.microsoft.com/en-us/office/vba/api/access.application.createcontrol

    I created a Public Function for you based on this reference. If you have a general module already created or create a new module and copy the following Public Function into it, then you can Call CreateUnboundTextBox with the On Open Event for any form and the function will:

    1. Open the active form in Design View
    2. Add a new TextBox control based upon the CreateControl criteria below (change the criteria as you wish)
    3. Save the form
    4. Name the new text box "NewText" (change this as you wish and change any of the other properties as well)
    5. open the active form in form view

    Public Function CreateUnboundTextBoxControl()
    Dim CurrentForm As String
    CurrentForm = Screen.ActiveForm.Name
    DoCmd.OpenForm CurrentForm, acDesign, , , acFormEdit, acWindowNormal
    Dim NewTextControl As Control
    Set NewTextControl = CreateControl(CurrentForm, acTextBox, acDetail, , , 500, 500, 500, 500)
    DoCmd.RunCommand acCmdSave
    With NewTextControl
        .Name = "NewText"
    End With
    DoCmd.OpenForm CurrentForm
    End Function

    Monday, July 22, 2019 4:19 PM
  • Hi Lawrence,

    Thank you so much, it works!!! Really appreciate your help.

    Tuesday, July 23, 2019 8:01 AM
  • Do be careful creating/deleting controls dynamically.  A form has a lifetime maximum control count.  So by creating/deleting controls it is easy to hit that limit.

    I'd still love to have a better understanding as to why you think you need to employ this approach as it is very uncommon and normally to be avoided.  If ever your interested, explain things further and we can help you come up with a better approach.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, July 23, 2019 3:36 PM
  • Thank you so much, it works!!!

    Hi Violet,

    You can use this method to define your forms in the development environment.

    In the production environment, it is a very bad idea to go to development mode of forms, especially when there are multiple users. I am afraid it will also not function in accde applications.

    Imb.

    Tuesday, July 23, 2019 4:27 PM
  • Hi Daniel,

    Thank you for the response.

    I am creating a screen that has multiple Labels, Textboxes, a few Buttons and Listbox.

    The Reason why i want to create the fields dynamically is i don't want to go to the code every time when there is a new item that needs to be added. I want  the items to be added automatically.

    I have a json array that i am using to pull the items.

    Hope it makes sense.

    Wednesday, July 24, 2019 9:45 AM
  • Hi Lawrence, 

    Your code works, it does add a Textbox and Label but now i am facing a challenge. When i run the program it keeps on adding a new form and when i add more Labels its giving out an error.

    Its my 1st time working with Access VBA. Please see attached screen shots.

    Wednesday, July 24, 2019 10:43 AM
  • Hi Daniel,

    Thank you for the response.

    I am creating a screen that has multiple Labels, Textboxes, a few Buttons and Listbox.

    The Reason why i want to create the fields dynamically is i don't want to go to the code every time when there is a new item that needs to be added. I want  the items to be added automatically.

    I have a json array that i am using to pull the items.

    Hope it makes sense.

    Violet:

    I don't know what a json array is, but even though ACCESS gives you the ability to create controls on a form or report using VBA code does not mean you should do that. I have to agree with Imb_hb and Daniel that it is a bad idea for many reasons. You do not need to "go to the code" every time a control needs to be added. Just open the form in Design mode and add it from the Design tab on the Ribbon. Just click on the type of control you want to add and then draw it on the form (or report) where you want it using your mouse.

    I would strongly encourage you to develop forms in Design mode without using VBA code to add controls. At some point you could hit the control limit that ACCESS allows for forms and reports and you would need to start all over creating your form.

    The reference I gave you is Microsoft's. It is just an example. You should NOT attempt to use it line-for-line in your project. It creates a new form each time and new controls on each form and then sets the Record Source for the form to a Record Source that you don't even have. No wonder you are getting errors. You didn't say what error you received but I suspect it was on the frm.RecordSource="Orders" line. Do you know how to step through lines of code to find the error or how to set a breakpoint?

    Since you stated this is the first time you are attempting to use ACCESS VBA code I would not attempt to create forms and controls this way. Create your forms and controls using the form wizard to get a feel for how ACCESS works first. Do not use VBA code to create forms and controls. I have been developing ACCESS projects since 1999 and have NEVER used VBA code to create forms or controls. Never. In fact the Public  Function I wrote in my reply is the first time I ever have tried to create a control on a form using VBA and I suspect most developers have had very little need to do that either. It would be only in a most extraordinary circumstance and I can't even think of one.

    Please just develop your project forms and controls using normal methods first. You will be glad you did.

    Wednesday, July 24, 2019 3:23 PM