Help with Checkboxes and Color Highlights (1 Viewer)

SusieQin · Apr 26, 2012

To my understanding you can't change the background or foreground of a checkbox upon click so I need an alternative.

In my form I have three check boxes, what I'd like to be able to do is if two or more boxes are checked that it will change the color of the name field to green.

Is this possible? If so how?

Beetle · Apr 26, 2012

You could just add the values of the check boxes. A Yes/No (boolean) field will be -1 for True (Yes) or 0 for False (No), so using the Abs (absolute value) function, in code it would look like;

Code:

If Abs(Me.Check1 + Me.Check2 + Me.Check3) >= 2 Then
    Me.YourTextBox.BackColor = vbGreen
Else
    Me.YourTextBox.BackColor = vbWhite
End If

Or in Conditional Formatting (on the text box) it would be;

Expression is Abs([check1] + [check2] + [check3])>=2

with the back color of the text box set appropriately.

missinglinq · Apr 26, 2012

Something like this:

Code:

Private Sub Check1_AfterUpdate()
 If Abs(Nz(Me.Check1, 0) + Nz(Me.Check2, 0) + Nz(Me.Check3, 0)) > 1 Then
  Me.NameTextbox.BackColor = vbGreen
 Else
  Me.NameTextbox.BackColor = vbWhite
 End If
End Sub

You'll need the same code to be in the Check2_AfterUpdate, Check3_AfterUpdate and, to have the formatting appropriate, when moving from Record to Record, in the Form_Current event, as well.

Linq ;0)>

missinglinq · Apr 26, 2012

Didn't mean to step on Beetle's toes! Started answering this question then my 'Otto' had to go outside to commune with nature and I didn't check before sending my reply!

I think you have to use Nz() as I did, to convert Nulls to Zeros, because anything plus a Null is Null.

So If one of the Checkboxes was Null and the other two were ticked, the sum of the three would still be Null, and no formatting would occur.

Also, while you cannot change the BackColor of a Checkbox, you change the BackColor of the Label attached to the Checkbox. You just have to remember to change the Label's BackStyle Property from the Default of Transparent to Normal.

Linq ;0)>

Beetle · Apr 26, 2012

I think you have to use Nz() as I did, to convert Nulls to Zeros, because anything plus a Null is Null.

Good point. Thanks for covering for me as I was being a bit lazy not checking for Null.

missinglinq · Apr 26, 2012

Glad to help! Know you're always busy, what with avoiding Sergeant Snorkel and all!

SusieQin · Apr 27, 2012

Hey thanks guys! I'm not at work today, but first thing Monday I will try this. Thank You!

SusieQin · May 7, 2012

Sorry to take so long coming back. Um, I know pretty much nothing about VBA... but this is what I put in.

Code:

Private Sub TD__Travel_Report_or_Travel_Expense_Reinbursement_Report__AfterUpdate()
If Abs(Nz(TD__Travel_Report_or_Travel_Expense_Reinbursement_Report__, 0) + Nz(CC__Certificate_of_Completion__, 0) + Nz(PSA__Prgram_Syllabus_or_Agendra_, 0)) > 1 Then
  Staff_.BackColor = vbGreen
 Else
  Staff_BackColor = vbWhite
 End If
End Sub

This is what I have and of course it's not working. If anyone doesn't mind stepping me through the process a little bit that would be fantastic.

My boss wants to be able to click two-three of these boxes and then it highlights the persons name in green. It's in a database view, but she has a problem being able to see what's checked, so we thought this would be a more eye grabbing way.

I hope that makes sense. :x

missinglinq · May 7, 2012

I've got to ell you, this is the most bizarre naming convention I've ever seen used in Access, especially using Single-Underscores in some places and Double-Underscores in others, and is bound to lead to errors, because in scanning code it's difficult to differentiate between single and double Underscores. And what in the world is the point in ending names with hanging Underscores?

One example is here

Staff_.BackColor = vbGreen
Else
Staff_BackColor = vbWhite

Assuming that the target Control here is named

Staff_

ending with a Single Underscore, it should be

Staff_.BackColor = vbGreen
Else
Staff_.BackColor = vbWhite

with the Dot between the Underscore and BackColor in the Else Clause.

Linq ;0)>

SusieQin · May 10, 2012

The naming was something Access was doing. I was using the name to be the same as the control source and when I went into VBA it converted it with all the dashes.

Anyways, I renamed everything to be more fitting. I am still getting nothing. No errors and no updates. I've played with a few things and still nada. Does the fact I am using this form in the datasheet view have anything to do with my colors not showing up?

Here is the code. I added the "Me.name" this time. I've tried it with and without. I'm not sure if it's suppose to be there or if it was an example you guys were giving.

Code:

Private Sub CC_AfterUpdate()
If Abs(Nz(Me.TD, 0) + Nz(Me.CC, 0) + Nz(Me.PSA, 0)) > 1 Then
Me.Staff.BackColor = vbGreen
Else
Me.Staff.BackColor = vbWhite
End If
End Sub
Private Sub PSA_AfterUpdate()
If Abs(Nz(Me.TD, 0) + Nz(Me.CC, 0) + Nz(Me.PSA, 0)) > 1 Then
Me.Staff.BackColor = vbGreen
Else
Me.Staff.BackColor = vbWhite
End If
End Sub
Private Sub TD_AfterUpdate()
If Abs(Nz(Me.TD, 0) + Nz(Me.CC, 0) + Nz(Me.PSA, 0)) > 1 Then
Me.Staff.BackColor = vbGreen
Else
Me.Staff.BackColor = vbWhite
End If
End Sub

BTW I appreciate the help. This is my first time with VBA

missinglinq · May 10, 2012

SusieQin said:
The naming was something Access was doing. I was using the name to be the same as the control source and when I went into VBA it converted it with all the dashes.

Access does that with the UnderScores if you have Spaces within your names. Spaces in names are a big nono with the Access Gnomes!

To do this kind of a thing in Datasheet View, you're going to have to use Conditional Formatting, from the Format Menu. Doing this kind of formatting through code, in a Datasheet or Continuous View Form, will result in All Records being formatted according to the data in the Current Record!

In Form Design View

Right-Click on the Staff Textbox
Click on Conditional Formatting
Under Condition1, use the dropdown to select Expression Is
In the box to the right enter Abs(Nz([TD], 0) + Nz([CC], 0) + Nz([PSA], 0)) > 1
Now use the BackColor Icon (the Paint Bucket) to select Green. You might also select Bold for the text as colors other than the standard white make normal weight text hard to read.
Click on OK

Not that when using Conditional Formatting, from the Menu, you must omit the Me. and enclose the Control Name with Square Brackets. Another quirk of Access!

BTW, if this were a Single View Form, your current code should have worked fine, assuming that all names were correct.

Linq ;0)>

SusieQin · May 10, 2012

Thank You, Thank You, Thank You! I appreciate the tips and the conditional formatting did exactly what I needed it to do! I've been a user of Access for a long time, but I have never built an access database before. It's been an interesting experience!

Help with Checkboxes and Color Highlights (1 Viewer)

SusieQin

Registered User.

Beetle

Duly Registered Boozer

missinglinq

AWF VIP

missinglinq

AWF VIP

Beetle

Duly Registered Boozer

missinglinq

AWF VIP

SusieQin

Registered User.

SusieQin

Registered User.

missinglinq

AWF VIP

SusieQin

Registered User.

missinglinq

AWF VIP

SusieQin

Registered User.

Similar threads

Users who are viewing this thread