1

I am using Microsoft Access at the moment.

I am trying to populate a number of combo boxes based on another combo box value. For example the first combo box is called cmb_BoxValue where it has a number of 1-20 and the remaining combo box will have the name cmb_Box1 until cmb_Box20. Once the user has selected the number of combo box required, using an AfterUpdate sub it will allow visibility = True based on the value. If the selected value is 3, cmb_Box1, cmb_Box2 and cmb_Box3 Visible = True. I managed to do it using a select case like below:

Private Sub cmb_BoxValue_AfterUpdate()
 Dim Size1 As Integer
 Size1 = Me.cmb_BoxValue.Value

 Select Case Me.cmb_BoxValue
  Case 1
  Me.cmb_boxBox1 = True
  etc...

But I feel like this is very repetitive and not the most ideal solution. I feel like a for loop is the ideal solution but I am not sure how to approach it. Thank you

DON_DON
  • 99
  • 5
  • Does this answer your question? [How to loop through all controls in a form, including controls in a subform - Access 2007](https://stackoverflow.com/questions/3344649/how-to-loop-through-all-controls-in-a-form-including-controls-in-a-subform-ac) – June7 Nov 12 '21 at 00:28
  • Setting visibility with VBA will affect ALL instances of control, not just current record. Could use Conditional Formatting to set enabled/disabled. Looping through controls is a common topic and more than one way to accomplish. – June7 Nov 12 '21 at 00:28
  • What is the Size1 variable used for? Your code doesn't show setting Visible property. – June7 Nov 12 '21 at 01:36

1 Answers1

1

Since comboboxes have similar names with number suffix, could dynamically build combobox name in an incrementing index loop, with a test if index is <= cmb_BoxValue input to set Visible property:

For x = 1 to 20
    Me.Controls("cmb_Box" & x).Visible = x <= Me.cmb_BoxValue
Next
June7
  • 19,874
  • 8
  • 24
  • 34