Change backcolor of 'built' control number

[supmktg]

I have a randomized table named tblRandomBoxes with the following data:

ID RandomID
1 4
2 5
3 2
4 6
5 1
6 3

I have 6 command buttons named command1,command2,command3, etc. and 6 Labels named Label1,Label2, Label3, etc. On Click of each command button, I want to lookup the ID based on the RandomID and set the backcolor of that label number to a different color.

ie) on command1 click me.Label4.backcolor = 39835

How can I 'build' the label number with the lookup? Here's the code I'm trying, but it doesn't recognize my 'built' label number:

Private Sub command1_Click()
Dim ctl As Control
ctl = "Label" & DLookup("ID", "tblRandomBoxes", "RandomID = 1")
Me.ctl.BackColor = 39835
End Sub

Thanks,

Sup

 

[llkhoutx]

Your Dlookup arguments are not correct. Check the Answer Wizard with "dlookup function" and it gives an example.

 

[supmktg]

Thanks, I added the square brackets, but the Dlookup wasn't my only problem.
I tested my Dlookup as follows:

Private Sub command1_Click()
Dim ctl As String
ctl = "Label" & DLookup("[ID]", "tblRandomBoxes", "[RandomID] = 1")
msgbox ctl
End Sub

The message box returns Label4 just as it should.

Private Sub command1_Click()
Dim ctl As Control
ctl = "Label" & DLookup("[ID]", "tblRandomBoxes", "[RandomID] = 1")
Me.ctl.BackColor = 39835
End Sub

Even with the square brackets I get the compile error 'method or data member not found'.

What else am I doing incorrectly?

Thanks,
Sup

 

[nateobot]

This worked for me:

    Dim ctl As control
    For Each ctl In Me.Controls
        If ctl.Name = "Label" & DLookup("[ID]", "tblRandomBoxes", "[RandomID] = 15") Then
            ctl.BackColor = 39835
        End If
    Next

 

[allan57]

Or you could try the following

Private Sub command1_Click()
me("Label" & DLookup("[ID]", "tblRandomBoxes", "[RandomID] = 1")).BackColor = 39835
End Sub

 

[llkhoutx]

DLookup("[ID]", "tblRandomBoxes", "[RandomID] = 1") is incorect and should be

DLookup("[ID]", "tblRandomBoxes", "[RandomID] =" & 1)

Furthermore, it's going to return an ID. I would think that that's too the wrong. Don't yoiu want a label caption be returned?

With your other code, the label is going to be 'LabelX".

the result should be stored as ctl.caption = ...

 

[supmktg]

Thank you very much!

me("Label" & DLookup("[ID]", "tblRandomBoxes", "[RandomID] = 1")).BackColor = 39835

did the trick!

I really appreciate the help,
Sup