Label on form

[EBee]

I have about 72 labels on a main form . . like below
I have a query that will contain this label records. I wanted to change the color and do special effect(Raised) when the label name is one of the records in the query.

96311 63614N 63615 635463 26346
12512 26325W 63621 262213 25366
96311 63614N 63615 635463 26346
12512 26325W 63621 262213 25366
96311 63614N 63615 635463 26346
12512 26325W 63621 262213 25366

and it will be attached to an EVENT procedure when the user click the checkbox for the zip code(the labels) on another form, i want to update the main form to do special effects like I mentioned above.

Q: What is the best way to accomplish this.

Thank you,
ET

 

[MajP]

Private Sub cmdZipCode_Click()
  Dim rs As DAO.Recordset
  Dim strLblName As String
  Set rs = Me.RecordsetClone
  'set rs = currentDb.Openrecordset("queryLabels")
  Do While Not rs.EOF
    strLblName = rs.Fields("strText") 'labels names in this field
    With Me.Controls(strLblName)
      .SpecialEffect = 1
      .BackStyle = 1
      .BackColor = vbRed
    End With
   rs.MoveNext
 Loop
end sub

 

[EBee]

Dim db As ADODB.Connection
Dim rs As New Recordset
Dim strLblName As String

Set db = CurrentProject.Connection
rs.Open "qryZipSelected", db, adOpenKeyset, adOpenDynamic

Do While Not rs.EOF
strLblName = rs.Fields("Forms!frmPreprintorder!lbl93611C") 'labels names in this field
With Me.Controls(strLblName)
.SpecialEffect = 1
.BackStyle = 1
.BackColor = vbRed
End With
rs.MoveNext
Loop
************************************************

I am getting an error. .Item can not be found. . the lables that I wanted to change the properties is in a different form.

what do I need to change?

 

[MajP]

In the query "qryZipSelected" do you have a field with the label names in it? That was my assumption. If you do what is the name of the field. It is probably not

"Forms!frmPreprintorder!lbl93611C"

I can not imagine that this refernces a field
strLblName = rs.Fields("Forms!frmPreprintorder!lbl93611C")

Also if the controls are on a different form maybe you need something like

With Forms("frmPreprintorder").Controls(strLblName)
Instead of me.

Explain the name of the field where the label names are located, and the name of the form with the controls.

 

[EBee]

I got it to work now. . thank you

Since this is a checkbox and I wanted to change the forcolor when checked and put it back the way it is when unchecked. How do you toggle back and fort depending if cheked or not

Here is what i have so far

Dim db As ADODB.Connection
Dim rs As New Recordset
Dim strLblName As String

Set db = CurrentProject.Connection
rs.Open "qryZipSelected", db, adOpenKeyset, adOpenDynamic


Do While Not rs.EOF
strLblName = rs.Fields("SubZip") 'labels names in this field
If Me.chkMail Then
With Forms("frmPreprintorder").Controls(strLblName)
.SpecialEffect = 0
.ForeColor = vblightGray

End With
rs.MoveNext
Else
With Forms("frmPreprintorder").Controls(strLblName)
.ForeColor = vbBlue
End With

rs.MoveNext

End If
Loop

 

[MajP]

Is your code working? If so could you just call the code again on the after update of the check box? What event fires the code now?