Solved

code for conditional format of label

Posted on 2010-09-03
25
622 Views
Last Modified: 2013-11-28
Hello - I have a label on an access 2003 form whose on click property is set to open another form. I would like to use conditional formatting to change the color of this label based on whether a field in the popup form that it opens has any data in it... I was thinking if isnull(forms!popupform!notes) =false then (and set the format...)
I realized that you can't use conditional formatting for labels... so I guess I want to set back color - but I don't know the code for this
help!
thanks so much :)
0
Comment
Question by:jpomerantz
  • 12
  • 6
  • 3
  • +3
25 Comments
 
LVL 8

Expert Comment

by:2toria
ID: 33595135
To change the back colour of a label you can use:-

yourlabelname.backcolor = vbRed (or whatever colour you wish to choose).

Hope this helps,
Matt
0
 
LVL 33

Expert Comment

by:jppinto
ID: 33595150
You can use one of the events (in this example I use the click event of the label), something like this:

Private Sub lblName_Click()
  If  SOMETHING CODE HERE
    Me.lblName.Backcolor = vbRed
  Else
    Me.lblName.Backcolor = vbGreen
  End If
End Sub

jppinto
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33595229
Based on your comment, you want to do this "based on whether a field in the popup form that it opens has any data in it".  Do you want to do this after the popup is opened, or before.  Is the popup form linked to the form that your label is on in any way (matches the selected record on your current form)?  If not, how will you identify which color to use?

Assuming that you have an ID field on the current form, and you want to open the popup form to that record, you could use the main forms Current event to check and change the label background color, something like:

Private Sub Form_Current

    me.lblName.BackColor = iif(isnullDLOOKUP("SomeField", "yourTable", "ID = " & me.txtID)), vbGreen, vbRed)

End If
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33595760
Another issue -- using the Click event of a label is peculiar, and will probably confuse users.  I suggest using a command button instead, and modifying its caption as needed.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33595774
Or you could just make the command button visible or invisible depending on the condition.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33595823
Concur with Helen that some users will not understand the concept of clicking a label, but the size issues of labels annoy me, so I do use labels for this purpose, especially in the header where the user is likely to look form "menu" type functionality.  Use the control tip text , the labels background color (normal background, not transparent), and "Raised" special effect property to make it look less like a label and more like a button.
0
 

Author Comment

by:jpomerantz
ID: 33596783
wow! thank you all so so much for your comments... here's what I have now and it says that it can't find the form... what's wrong wtih this syntax??

Me.Label0.BackColor = IIf(IsNull(Forms!PopUp!notes), vbGreen, vbRed)
0
 

Author Comment

by:jpomerantz
ID: 33597109
oh. wait a second... you're right. I have to tell it which record!!!
I have it linked by a numerical ID. I tried this - but it still didn't work

Me.Label0.BackColor = IIf(IsNullDLOOKUP(notes, Table, "ID =" & Me.tableID), vbGreen, vbRed)

it lights up Me.tableID and says "sub or function not defined"

thanks so so much for your help!!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33597160
The syntax I recommended was:

Me.Label0.BackColor = IIf(IsNull(DLOOKUP("SomeField", "TableName", "ID =" & Me.tableID), vbGreen, vbRed)

You will need to replace "SomeField" with the name of a field in the table "TableName".  Both of these parameters must be either a string (wrapped in quotes) or  a string variable.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 total points
ID: 33597170
Oops,  That should read:

Me.Label0.BackColor = IIf(IsNull(DLOOKUP("SomeField", "TableName", "ID =" & Me.tableID)), vbGreen, vbRed)

0
 

Author Comment

by:jpomerantz
ID: 33597450
ok - thanks! this is what I've got now... (in the on current event of the form...)

Me.Label0.BackColor = IIf(IsNull(DLookup("notes", "Table", "ID =" & Me.tableID)), vbGreen, vbRed)

it's telling me an error runtime error 2001: "you cancelled the previous operation"

what's that?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33597569
Do you really have a table named "Table"?  If not, change that to the actual name of the table which contains your [Notes] and [ID] fields.

Are you sure this is in the Form_Current event?

This error usually occurs when you open a report that has no records, or close a report before printing it, or something like that.  Can you copy all of the code you are running in the Current event and post it here?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:jpomerantz
ID: 33597641
sure - here it is... (it's a sample db that I'm playing around with so that's what I have it named...)

Private Sub Form_Current()

Me.Label0.BackColor = IIf(IsNull(DLookup("notes", "Table", "ID =" & Me.tableID)), vbGreen, vbRed)

End Sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33597785
And you are getting error 2001 in the current event?

Try it with:

Me.Label0.BackColor = IIf(IsNull(DLookup("notes", "Table", "ID =" & Me.tableID)), RGB(0, 255, 0), RGB(255, 0, 0))

And make sure that the BackStyle property of the label is set to Normal, rather than Transparent.

You might also try printing the results of the DLOOKUP( ) function in the immediate window, to make sure you have that syntax correct.
0
 

Author Comment

by:jpomerantz
ID: 33597824
how do I print those results? sorry... i"m new to vba :)
did change the backstyle property... good that you thought of that :)
still getting the error with the new code though
0
 

Author Comment

by:jpomerantz
ID: 33597881
i gotta run now - will check back tomorrow
thanks so so much for your help!!
0
 

Author Comment

by:jpomerantz
ID: 33604516
any ideas for me? could there be something I'm doing wrong related to the "ID"? - when I say ID field - I'm talking about an autonumber field that's used as primary key that identifies each record - that's what i'm using to link the form to the notes popup form

thanks!
0
 

Author Comment

by:jpomerantz
ID: 33606927
thought that maybe it would help if I posted the db... hope that you have some ideas for me :)

i also wanted to mention that really I want the button to remain the ordinary gray color if the popup field is empty - and then to change color to green or red if there is something in the notes field...

thanks!!!
test-notes-button-color-change.mdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 33607497
Error 2001 usually means that something like a form open, or in this case a domain aggregate function, has failed due to a problem in the underlying SQL.

Check your table and field names in the DLookup statement for accuracy.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 200 total points
ID: 33607536
Here you go...

Your ID field in the table is actually named "TableID"

Look at this part of Fyed's code:

>>> "ID =" & Me.tableID

To use the correct field name, you need to change it to:

"TableID =" & Me.tableID

You also should include a check for new records in the code.  If this code runs on a new record (no value for TableID), you will get an error.  Using vbgreen and vbred works fine.  

This adds a check for a new record and only executes the code for existing records:


Private Sub Form_Current()

If Me.NewRecord = False then
    Me.Label0.BackColor = IIf(IsNull(DLookup("notes", "Table", "TableID =" & Me.tableID)), vbGreen, vbRed)
End IF
End Sub



(Just to be clear, this is simply building on code that fyed has already given you)
0
 

Author Comment

by:jpomerantz
ID: 33607702
amazing!!! that's exactly what I needed!!!
instead of red and green - my orriginal intent was to leave it the standard access form grey color - and then just have it change if filled etc... instead of vbRed - can i just replace with the code for the standard grey? is it vbGray? :) :)

thanks a million
0
 

Author Comment

by:jpomerantz
ID: 33616229
played around and figured it out myself :) actually used RGB(220,220,220) and it looks almost exactly like a regular command button :)
thanks so much to both of you!!

from your last comment about "building on the code that he's already given me" I'm assuming that you're saying that he should get more points for this... wish I could give both of you 500!!!

0
 

Author Closing Comment

by:jpomerantz
ID: 33616246
thanks so so much to both of you - exactly what I needed!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 33619342
Glad to help out, and good job figuring out that "vbGray" thing (which is not a nice constant like vbRed).

Another way to get a value for color you need (taking that default Gray as an example again), is to just lookup the BackColor setting of an area that has that color, like the backcolor for the detail section of your form.

Using that method:

Me.label0.BackColor = -2147483633


(or you could just toggle the backstyle property to 'Transparent')
0
 

Author Comment

by:jpomerantz
ID: 33627335
cool - I didn't know you could do that directly with the number!
thanks so so much :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
If your app took Google’s lash recently, here are the 5 most likely reasons.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now