change label backcolor based on stored data in table

[bnotamoslema]

Hi all,
i've just designed a program using MS Access , this program act as risk calulator ,when someone choose Risk Potential from listbox so risk solution appears in text box ,also one of matrix blocks colored by "8388736" (this matrix consists of 25 label numbered through 1 to 25 ), i've stord data in table called risk_soln ,this table has potental_code, risk_sloution and the label_number of the matrix to be highlited with the "8388736" color, so i've created Recordset to retrive the specified data , the risk solution displayed in the white text box when clicking the button, but when dealing with the label_number it's string data stored in the table ؟؟ how to convert it to label control , i don't want to have 25 if statements to set the required label to be colored , i want to have it dynamically through the recordset

HERE'S THE CODE

Private Sub Command2_Click()
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim ctl As Control

Set db = CurrentDb()

LSQL = "select risk_soln,label_no from risk_soln where pot_code=" & pot
Set Lrs = db.OpenRecordset(LSQL)
Me.risk_soln = Lrs!risk_soln
Set ctl = Nothing
Set ctl.Name = Lrs!label_no
ctl.BackColor = 8388736

End Sub

I GOT ERROR

 

[PHV]

Say the labels are named myLabel1 thru myLabel25:

Set ctl = Me.Controls("myLabel" & Lrs!label_no)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya bnotamoslema . . .

You can assign the backcolor without going thru the trouble of setting a control object:

[blue]   Me("LabelName" & Lrs!label_no).BackColor = 8388736[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I am assuming that you may have to unselect values as well. Because if you go to the next record then you just keep hiliting records. This then would require you to loop all the labels and "un color" those not selected.

I would put a number (1 to 25) in the tag property of each label (or you can use the name property as already discussed) . You said the table stores this number as strings. So in the forms current event and/or other events pass the selected number to the procedure. If there is a numeric tag and it is the selected value then it gets one color, if it is a numeric tag and not the selected value it gets the default color

Public Sub HiliteLabel(lblNumber As String)
  On Error GoTo errlbl
  Dim ctl As Access.Control
  For Each ctl In Me.Controls
     Debug.Print ctl.Name
     If ctl.ControlType = acLabel Then
        If ctl.Tag = lblNumber Then
           'selected color
           ctl.BackColor = vbRed
         ElseIf IsNumeric(ctl.Tag) Then
           'default color 
           ctl.BackColor = vbGreen
         End If
     End If
  Next ctl
  Exit Sub
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Sub