Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

code for conditional format of label

Posted on 2010-09-03
25
Medium Priority
?
664 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 49

Expert Comment

by:Dale Fye
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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 49

Accepted Solution

by:
Dale Fye earned 1200 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 49

Expert Comment

by:Dale Fye
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
 

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 49

Expert Comment

by:Dale Fye
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 800 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you are like me and like multiple layers of protection, read on!
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

782 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