2 Questions...1 function for multiple buttons, and using arrays for button colors

[ConfusedA]

This is kind of a long post, both questions are related to a similar topic of dealing with a large number of buttons.

Question 1:
I am attempting to make a macro to pull information based on a clicked button to provide information to another form. While my example below will be rudimentary, I am attempting to create so that it can be applied to 450 buttons, hence the need for it to be a macro and not simply code per button.

For example if I have 3 buttons, captioned: red, green and blue. If I click on the blue button, it will open another form with a text-box that will say 'blue'. Likewise, if I click the red button the text-box will be changed to 'red'.

In the code below the section that I need to change is: Command1.Caption. Using this data I can pull from the one button to create the value, but I need it to be based on an OnClick or something in order to pull the value from the right button.

Example:


Function Macro1()
On Error GoTo Macro1_Err

DoCmd.OpenForm "Form2", acNormal, "", "", , acNormal
Forms!Form2!Text0.Text = Forms!Form1!Label0.Caption
DoCmd.GoToControl "[Text2]"
Forms!Form2!Text2.Text = Forms!Form1!Command1.Caption

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function




Question 2:
I am trying to use a query to change button colour on the basis of values stored in a table.
What I want to do is change the button background after comparing the button name to the same variable in a table, and determining another variable.

IE:
Button1, Button2. In table: Button1, Val = Y, and Button 2, Val = Z

Pseudo Code:
Array: From Button1 to Button2
Query for Button1 against table
If Val = Y, Button1.background = red
Else if Val = Z, Button1.background = blue
Else Button1.background = black
End if


Thanks in advance, please feel free to ask more questions, I'm sure this post is probably at least half as confused as I feel doing this.

 

[Uncle Gizmo]

I blogged about handling multiple controls in 2007. Heres the Link:

Lock UnLock Controls

There are 7 videos, plus example database,

I suggest you look at Video 6 first:

Video 6 --- Demonstrates how to have your Command Buttons access the code in a Class Module through a function in the code behind the form.

However I recommend you watch all the videos in succession because you will understand the various techniques... and they sort of build on each other, it's better if you get a grounding before going on to the advanced.

 

[Uncle Gizmo]

Theres an excellent thread here: Soft-coded event handlers. by ChrisO

Which you may find interesting....

 

[Uncle Gizmo]

There is a limit to the number of controls you can have, I think its around 700...

I realise your only around 400, however it's something you probably need to keep in mind.

 

[Galaxiom]

Also consider implimenting fake buttons using textbox controls on Continuous Forms.

This way they can be directly connected to a table and Conditional Formatting can be used to set their colour and actions according to the table.

It might be possible to do it with multiple instance of a subform with a single button.