Some help with Do__Loop and Arrays

[Friderik]

Hi everyone!
I have a form with 6 checkboxes (getting their values from a table/query)
When the form loads, I would like the buttons right of the checkboxes to "react" to the values in the chekcboxes.
For example: If "Value1" is 0, then disable "EDIT" button and enable "ADD" button Else enable "Edit" button and disable "ADD" button.
Here is a screenshot:

I read some help files and wrote this:

Private Sub Form_Load()
 
Dim Edit As String
Dim Add As String
Dim Value As String
 
Edit = "cmd_edit1"
Add = "cmd_add1"
Value = "Value1"
 
If Me(Value) = 0 Then
Me(Edit).Enabled = False
Me(Add).Enabled = True
Else
Me(Edit).Enabled = True
Me(Add).Enabled = False
End If

The code above works as expected, but I don't think copy/pasting for the other five checkboxes and buttons is the right approach (altough it works).
I should probably use Array and store variables there and then use a Do Loop.
I figured out how to declare Arrays, but I get lost, when it comes to looping the above code for every value in the array.

Here is the array example:

Dim Edit(6) As String
Dim Add(6) As String
Dim Value(6) As String
 
Edit(1) = "cmd_edit1"
Edit(2) = "cmd_edit2"
Edit(3) = "cmd_edit3"
Edit(4) = "cmd_edit4"
Edit(5) = "cmd_edit5"
Edit(6) = "cmd_edit6"
 
Add(1) = "cmd_add1"
Add(2) = "cmd_add2"
Add(3) = "cmd_add3"
Add(4) = "cmd_add4"
Add(5) = "cmd_add5"
Add(6) = "cmd_add6"
 
Value(1) = "Value1"
Value(2) = "Value2"
Value(3) = "Value3"
Value(4) = "Value4"
Value(5) = "Value5"
Value(6) = "Value6"

Any tips, or even examples of how you would solve this are more then welcome :)

Friderik

[gershwyn]

The loop is an excellent idea, but given that your controls all have similar names, with the only difference being the number at the end, I'm not sure it's worth it to store the values in an array. I think the simplest way to accomplish what you describe is like this:

Public Function ToggleButtons()
  For i = 1 To 6
    CheckValue = Nz(Form.Controls("Value" & i).Value, 0)
    Form.Controls("cmdEdit" & i).Enabled = (CheckValue = 0)
    Form.Controls("cmdAdd" & i).Enabled = (CheckValue <> 0)
  Next
End Function
 

First we check the value of each checkbox (converting a null value to a zero, so an uninitialized checkbox is treated the same as an unchecked one.) The edit button is enabled if the checkbox is unchecked. The add button is enabled otherwise. Then I'd set it up so that the function is called as the form's OnCurrent event and from the OnAfterUpdate event of each checkbox.

[Friderik]

Hi, thanks for your fast reply.
I should mention, that the controls have different names, I just renamed them for this example. Like:

cmd_scheme_name_edit
cmd_scheme_name_add
...

Also, the checkboxes are locked, they are there just to display data.

[NeoPa]

It's unfortunate that you changed the details of the question that way Friderik. From the care you used to formulate your question though, it looks like it was a simple oversight, and you get brownie-points for the question anyway. Particularly unfortunate as Gershwyn has provided a particularly clever solution for what the problem was.

As an alternative that doesn't rely on the controls being named sequentially, you could try using the For Each ctl In Me.Controls construct instead. You would need the design to include something that identified which controls were logically linked. As we have little info as to their correct names it's hard for me to suggest an approach that fits your situation, but if there isn't something already available then you could redesign the form to ensure the linked controls are identifiable somehow.

I mentioned the question was asked well (excepting the unfortunate slip-up with the names), it also indicates good sound thinking on your part even to ask such a question, so good for you :-)

[Friderik]

Hi, thanks for the tipps and the critique.

I solved my problem first with names of the conrols as varaibles in an array. The more I looked at my solution, the more I appreciated gershwyn's approach.

Long story short. gershwyn's way is clearer and simpler. So I renamed my controls, created a public function and all is well on my form :)

I will paste my application of gershwyn idea on monday, to clarifiy things.

[NeoPa]

Excellent. A sensible approach and a good idea to post your eventual solution when you have it too :-)