Results 1 to 12 of 12
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209

    Access VBA To Dynamically Create Checkboxes With Text On Form


    Using access 2016 - how can VBA be used to create checkboxes with text on a form?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    For what purpose? This sort of modification requires opening form in design view, then switching to form view. A properly designed database should not require this sort of process.

    What are you really trying to accomplish?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    I have a database that houses a table that holds customerID, customerName, customerAccountNum, customerAppName.

    The user will select a customer Name from a combo box on a form and on the index change of the combo box, we need to display a check box with the .Caption set to customerAccountNum - customerAppName for all records in the table of the selected customer so that the user can then select the checkbox of specific Accounts & Apps and on a button press event a report will be displayed that shows further information on the selections only.

    In my head, I was throwing together this rough pseudocode (have not tested in the VBA IDE yet so possibly multiple errors)
    Code:
    Dim rs As DAO.Recordset
    dim db as DAO.database
    Dim NewCheckBox As MSForms.CheckBox
    Dim i As Integer
    
    
    set db = currentDb
    'Concatenation query goes here
    'query that concatenates account & application
    'can use same query and pass in where clause from combobox
    Set rs = db.OpenRecordset('")
    
    
    i = 1
    rs.movelast
    rs.movefirst
    
    
    do while not rs.EOF
        Set NewCheckBox = myform.designer.Controls.Add("Forms.CheckBox."&i)
        With NewCheckBox
            .Caption = rs!myField
        End With
    i = i + 1
    loop

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Still don't know what checkbox is for.

    Checkbox is a data control and does not have Caption property. Label controls have Caption. A label can be associated with data control.

    Why change label caption? Why not just display account number in a bound textbox?

    Sounds like you really just need to filter records on form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Forget your idea. You cannot use a form and at the same time modify its collection of controls. Nor would you ever modify a form this way to suit a set or subset of records. There are far better methods that don't invite frequent form design-induced corruption. Not too many records? Populate a multi select listbox. Too many for that? A continuous form with a checkbox field (although where is the point that is too many records for one over the other?). Or decide how to filter records to the desired set rather than cherry picking them, assuming that is possible.

    More info as to what's going on rather than how you see it being designed might be the way forward from here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    Quote Originally Posted by June7 View Post
    Still don't know what checkbox is for.

    Checkbox is a data control and does not have Caption property. Label controls have Caption. A label can be associated with data control.

    Why change label caption? Why not just display account number in a bound textbox?

    Sounds like you really just need to filter records on form.
    I do need to filter them - my plan was to use the checkbox as a way to allow the user to select specific Accounts. For example, Accounts 1 and 4 only.

  7. #7
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    Quote Originally Posted by Micron View Post
    Forget your idea. You cannot use a form and at the same time modify its collection of controls. Nor would you ever modify a form this way to suit a set or subset of records. There are far better methods that don't invite frequent form design-induced corruption. Not too many records? Populate a multi select listbox. Too many for that? A continuous form with a checkbox field (although where is the point that is too many records for one over the other?). Or decide how to filter records to the desired set rather than cherry picking them, assuming that is possible.

    More info as to what's going on rather than how you see it being designed might be the way forward from here.
    A multi-select list box could work.

    What I am after is to give the user the ability to select specific Accounts belonging to the selected Customer.

    At most one Customer would have a max of 20 accounts - which is what leads me to believe a multi-select list box could work for this, how would you set that up?

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You can set up a multi select list box by inserting a list box on your form and changing the MULTI SELECT property to Simple or Extended.

    A simple multi select lets you single click on an item in your list box to highlight it, click again to unhighlight it.
    An extended multi select lets you select a range of values by holding clicking on your first item then holding down your control (or shift) key and clicking and clicking another value, shift would highlight all values between the lower and upper bound, control click would highlight the specific one chosen.

    Just be aware, if you are selecting your accounts for a specific action you will have to cycle through the selected values in the list box

    i.e.

    Code:
    with me.listboxname
       for i = 1 to .listcount - 1 'depending on whether you are showing your column headers this will need to be modified
            if .selected(i) ' if the row is currently marked as selected (highlighted)
                'do what you're going to do if the account number is currently selected
            else
    
            endif
       next i
    end with

  9. #9
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    Quote Originally Posted by rpeare View Post
    You can set up a multi select list box by inserting a list box on your form and changing the MULTI SELECT property to Simple or Extended.

    A simple multi select lets you single click on an item in your list box to highlight it, click again to unhighlight it.
    An extended multi select lets you select a range of values by holding clicking on your first item then holding down your control (or shift) key and clicking and clicking another value, shift would highlight all values between the lower and upper bound, control click would highlight the specific one chosen.

    Just be aware, if you are selecting your accounts for a specific action you will have to cycle through the selected values in the list box

    i.e.

    Code:
    with me.listboxname
       for i = 1 to .listcount - 1 'depending on whether you are showing your column headers this will need to be modified
            if .selected(i) ' if the row is currently marked as selected (highlighted)
                'do what you're going to do if the account number is currently selected
            else
    
            endif
       next i
    end with
    That is perfect! Thank you so much!!

    Is it possible to set-up columns in a multi-select list box?

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    yes, there's both a number of columns and column widths property you can use. Typically the bound column is the first column (column index 0) and it is suppressed (column width 0) so you can use it as a reference for external queries/reports/etc.

  11. #11
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    Quote Originally Posted by rpeare View Post
    yes, there's both a number of columns and column widths property you can use. Typically the bound column is the first column (column index 0) and it is suppressed (column width 0) so you can use it as a reference for external queries/reports/etc.
    For the Multi-Select property, do I want it set to Simple or Extended?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-17-2017, 07:41 AM
  2. Replies: 4
    Last Post: 06-02-2015, 08:30 PM
  3. Replies: 54
    Last Post: 07-17-2013, 03:01 PM
  4. Replies: 1
    Last Post: 01-31-2013, 02:58 PM
  5. Replies: 1
    Last Post: 08-13-2010, 01:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums