Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating combo box values using VBA loop (Access 97 SR-2)

    I have 64 small combo boxes on a form and I need to be able to click a button that clears the contents of all of them to nothing.

    I've tried using a Do...Loop to achieve this but my various attempts don't work, I need something along the lines of:

    Do until Combo64
    Me.Combo1.DefaultValue = ""
    Move to next Combo box...
    Loop

    I'm sure this must be possible as I don't really want to have 64 lines of Me.Combo#.DefaultValue = ""

    Thanks in advance for any help on this.

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating combo box values using VBA loop (Access 97 SR-2)

    Something along these lines should do the trick for you. Good luck!
    <pre>Dim ctl As Control

    For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Then
    ctl.RowSource = ""
    End If
    Next</pre>

    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating combo box values using VBA loop (Access 97 SR-2)

    Shane, I changed the .RowSource bit in your code to .DefaultValue and it works fine but now I have another problem - I've added 2 more combo boxes on the same form which are separate from the other 64 and these are getting reset too. Is there a way of making that code reset only the 64 combo boxes (named Combo1 through to Combo64) while leaving the other 2 as they are? Thanks =)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updating combo box values using VBA loop (Access 97 SR-2)

    Shane's code did what you said you wanted to do, cleared the comboboxes. You apparently wanted to do something else, since all clearing the default value does is not bring up a default value ... ever. Why were you using default values in the first place?

    A form with 64 comboboxes has to be awfully busy. What exactly are you trying to do with it? There are ways to do what you describe, but you haven't given us all the information. Are these unbound or bound comboboxes? What are you using them to do? When do you want to clear them, at form open or some other time? It's quite possible to build an array of controls and reset only the controls in that array. I've attached a demo database that shows several different methods of simulating control arrays in Access 97. The controls in the demo are checkboxes, but the same approach will work for any kind of controls.
    Attached Files Attached Files
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •