• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

code for conditional format of label

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
jpomerantz
Asked:
jpomerantz
  • 12
  • 6
  • 3
  • +3
2 Solutions
 
2toriaCommented:
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
 
jppintoCommented:
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
 
Dale FyeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Helen FeddemaCommented:
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
 
Helen FeddemaCommented:
Or you could just make the command button visible or invisible depending on the condition.
0
 
Dale FyeCommented:
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
 
jpomerantzAuthor Commented:
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
 
jpomerantzAuthor Commented:
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
 
Dale FyeCommented:
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
 
Dale FyeCommented:
Oops,  That should read:

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

0
 
jpomerantzAuthor Commented:
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
 
Dale FyeCommented:
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
 
jpomerantzAuthor Commented:
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
 
Dale FyeCommented:
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
 
jpomerantzAuthor Commented:
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
 
jpomerantzAuthor Commented:
i gotta run now - will check back tomorrow
thanks so so much for your help!!
0
 
jpomerantzAuthor Commented:
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
 
jpomerantzAuthor Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
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
 
jpomerantzAuthor Commented:
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
 
jpomerantzAuthor Commented:
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
 
jpomerantzAuthor Commented:
thanks so so much to both of you - exactly what I needed!
0
 
mbizupCommented:
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
 
jpomerantzAuthor Commented:
cool - I didn't know you could do that directly with the number!
thanks so so much :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 6
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now