Change Text Color after DLookup 1

mikelev · Sep 9, 2005

My users are required to enter records for 15 predefined equipment records daily. I have included 15 unbound labels with these equipment numbers on the form. As a record is entered for todays date and saved I would like the appropriate label (label186) to change from red to green.

This will provide an easy method to ensure all equipment record have had entry every day, and the reports will contain "all" the data.

Table: SOFTCOUNT
INSERTDATE (date/time)
TABLENUM (text)

I tried the following with no luck.

Code:

If (DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM=" & Me!TABLENUM)) Then
me.label186.forecolor = 25600
End If

The following day they would all appear red again until a record is entered for todays date.

It's a never ending learning cycle.........

Cheers,

PHV · Sep 9, 2005

If DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM='" & Me!TABLENUM & "' AND INSERTDATE=#" & Date & "#") = Me!TABLENUM Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mikelev · Sep 9, 2005

Thanks for the post PHV.

I tried placing the following in the OnCurrent event:

Code:

If DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM='" & Me!TABLENUM & "' AND INSERTDATE=#" & DATE & "#") = Me!TABLENUM Then
Me.Label186.ForeColor = 25600
Me.Label187.ForeColor = 25600
Me.Label188.ForeColor = 25600
Me.Label189.ForeColor = 25600
Me.Label190.ForeColor = 25600
Me.Label191.ForeColor = 25600
Me.Label192.ForeColor = 25600
Me.Label193.ForeColor = 25600
Me.Label194.ForeColor = 25600
Me.Label195.ForeColor = 25600
Me.Label196.ForeColor = 25600
Me.Label197.ForeColor = 25600
Me.Label198.ForeColor = 25600
Me.Label199.ForeColor = 25600
Me.Label200.ForeColor = 25600
Me.Label201.ForeColor = 25600
Me.Label202.ForeColor = 25600
Else
Me.Label186.ForeColor = 255
Me.Label187.ForeColor = 255
Me.Label188.ForeColor = 255
Me.Label189.ForeColor = 255
Me.Label190.ForeColor = 255
Me.Label191.ForeColor = 255
Me.Label192.ForeColor = 255
Me.Label193.ForeColor = 255
Me.Label194.ForeColor = 255
Me.Label195.ForeColor = 255
Me.Label196.ForeColor = 255
Me.Label197.ForeColor = 255
Me.Label198.ForeColor = 255
Me.Label199.ForeColor = 255
Me.Label200.ForeColor = 255
Me.Label201.ForeColor = 255
Me.Label202.ForeColor = 255
End If

And all labels show green on form open, even though no records exist for today. Then when I click the insert button they all turn red. After saving the record all labels turn green.

I was hoping the colors would be static. Meaning on form open (begining of day) they would all be red, then when record is saved for TABLENUM #1 only label186 would turn and stay green until the next day. The same for label187 after TABLENUM #2 is entered and saved. At midnight they all would return to red.

Im sure the code you posted is fine, but I messed up somewhere?

Once again thanks for your help.

PHV · Sep 9, 2005

You really didn't realize that your code set ALL the 17 labels to the same color ?
You wanted somethung like this ?
For i = 1 To 17
If DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM='#" & i & "' AND INSERTDATE=#" & Date & "#") = ("#" & i) Then
Me("Label" & (i + 185)).ForeColor = 25600
Else
Me("Label" & (i + 185)).ForeColor = 255
End If
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mikelev · Sep 9, 2005

Now that you mention it, I should have seen the obvious.

I pasted the code you supplied in the OnCurrent and nothing happens at all, the labels just stay red.

Forgive my ignorance.

Code:

Private Sub Form_Current()
For i = 1 To 17
If DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM='#" & i & "' AND INSERTDATE=#" & DATE & "#") = ("#" & i) Then
    Me("Label" & (i + 185)).ForeColor = 25600
  Else
    Me("Label" & (i + 185)).ForeColor = 255
End If
Next i
End Sub

PHV · Sep 9, 2005

What are the REAL values of SOFTCOUNT.TABLENUM in the database ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mikelev · Sep 9, 2005

SOFTCOUNT.TABLENUM
1
2
3
4
5
8
9
10
11
12
P1
P2
P3
P4
P5

SOFTCOUNT.INSERTDATE
9/9/2005 8:45:00 AM
9/9/2005 10:16:46 AM

PHV · Sep 9, 2005

And what about this ?
Private Sub Form_Current()
t = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "P1", "P2", "P3", "P4", "P5")
For i = 0 To 16
If DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM='" & t(i) & "' AND Int(INSERTDATE)=#" & Date & "#") = ("#" & i) Then
Me("Label" & (i + 186)).ForeColor = 25600
Else
Me("Label" & (i + 186)).ForeColor = 255
End If
Next i
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mikelev · Sep 9, 2005

Entered 2 records with no results (they stayed red). I also verified the spelling of the table name and column names in my DB.

SOFTCOUNT.TABLENUM SOFTCOUNT.INSERTDATE
2 9/2005 9:05:19 AM
3 9/2005 9:05:21 AM

Code:

t = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "P1", "P2", "P3", "P4", "P5")
For i = 0 To 16
 If DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM='" & t(i) & "' AND Int(INSERTDATE)=#" & DATE & "#") = ("#" & i) Then
    Me("Label" & (i + 186)).ForeColor = 25600
  Else
    Me("Label" & (i + 186)).ForeColor = 255
  End If
Next i

PHV · Sep 9, 2005

OOps, sorry for the typo.
Replace this:
= ("#" & i) Then
By this:
= t(i) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mikelev · Sep 9, 2005

heres what I have:

Private Sub Form_Current()
t = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "P1", "P2", "P3", "P4", "P5")
For i = 0 To 16
If DLookup("TABLENUM", "SOFTCOUNT", "TABLENUM='" & t(i) & "' AND Int(INSERTDATE)=#" & DATE & "#") = t(i) Then
Me("Label" & (i + 186)).ForeColor = 25600
Else
Me("Label" & (i + 186)).ForeColor = 255
End If
Next i
End Sub

I added a few more records but no label change.

Your patience should be commended

PHV · Sep 9, 2005

Perhaps this ?
If DCount("TABLENUM", "SOFTCOUNT", "TABLENUM='" & t(i) & "' AND Int(INSERTDATE)=Int(Date())) > 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mikelev · Sep 9, 2005

Expected List seperator, I tried a few closing paren's but no luck. Sorry for dragging this out.

PHV · Sep 9, 2005

OOps, my bad:
If DCount("TABLENUM", "SOFTCOUNT", "TABLENUM='" & t(i) & "' AND Int(INSERTDATE)=Int(Date())") > 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mikelev · Sep 9, 2005

AMAZING!!!!

Where do I send the check?

As always PHV many thanks, it's people like you that help us guys out here floundering around make things work.

Hats off, and a star!

mikelev · Sep 9, 2005

Ok just a little modification. The Array you prvided includes "6" & "7" which I do not have a TABLENUM for. So for whatever reason "8" & "9" (label191 & label192) do not change colors, but all the rest work fine.

Anything we can de here?

PHV · Sep 9, 2005

Private Sub Form_Current()
t = Array("1", "2", "3", "4", "5", "8", "9", "10", "11", "12", "P1", "P2", "P3", "P4", "P5")
For i = 0 To 14

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Change Text Color after DLookup 1

mikelev

Technical User

PHV

MIS

mikelev

Technical User

PHV

MIS

mikelev

Technical User

PHV

MIS

mikelev

Technical User

PHV

MIS

mikelev

Technical User

PHV

MIS

mikelev

Technical User

PHV

MIS

mikelev

Technical User

PHV

MIS

mikelev

Technical User

mikelev

Technical User

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor