Checkbox event help

[agrogers15]

I am using the status of a checkbox to change the color of a rectangular box behind a field for quick status reference.

Private Sub Permit_Received_Click()
If Me.Permit_Received = Yes Then
Me.PermitHighlight.BackColor = vbRed
Else
Me.PermitHighlight.BackColor = vbGreen
End If
End Sub

First thing I notice that I don't understand, is the color changes opposite of how I think. I originally had the colors in the opposite spot so that when I check the box it turns green but it was opposite. I then changed the initial If statement to No but it still did the same thing. So I changed the color syntax locations and it worked, but it doesn't stay in the proper back color. When I close the form and reopen it defaults back to red. How do I keep the backcolor while the checkbox is checked?

 

[My Aswer Is This]

Try this:

Assuming you want it Green if it is checked.

Private Sub Permit_Received_Click()
If Permit_Received = True Then
Permit_Received.BackColor = vbGreen
Else
Permit_Received.BackColor = vbRed
End If
End Sub

 

[xenou]

Note: when you close and reopen the form anything done in that click event is over and done. The background color will be set according to the form design.

I've never used conditional formatting in Access but if you want that to happen I think Access has conditional formatting that you can use on a control without writing your own code for it.

If you do write your own code for it, it should run whenever the form is opened as well as when a new record is displayed - i.e., anytime you view a new or another record, as well as change an existing one, you would need to have this code check your condition.

I'm a little confused by your statement "If Me.Permit_Received = Yes Then". What is the Yes there? Is that a variable or constant you have defined?

 

[Micron]

The form Current event and the click event of the checkbox would be needed for a code solution. The Current event is one of the events that will fire when the form is opened as well as if records are cycled, which should be OK for a single record form. Conditional formatting can't be used on a box/rectangle, which is what the OP is suggesting the control is. A textbox can be made to look like a rectangle and could accept CF. If this is a continuous form, there would be a whole different set of issues.

 

[agrogers15]

So why use a textbox? I don't want to actually enter anything in it. I have the rectangle positioned behind the permit received checkbox so that while the checkbox is left unchecked the rectangle is red and once checked needs to turn green. Which I have gotten to work when I FIRST check the box. However the issue is I can't get the box to stay green once I close the record and come back or reopen the database. I have tried this:

Private Sub Permit_Received_Click()
If Me.Permit_Received = True Then
Me.PermitHighlight.BackColor = vbGreen
Else
Me.PermitHighlight.BackColor = vbRed
End If
End Sub
________________

Private Sub Permit_Received_Current()
If Me.Permit_Received = True Then
Me.PermitHighlight.BackColor = vbGreen
Else
Me.PermitHighlight.BackColor = vbRed
End If
End Sub

This also doesn't keep the box green. I have to uncheck and recheck the box.

 

[xenou]

I think the latter part for the on current event needs to be in the FORM current event (not the CONTROL Current Event).

 

[agrogers15]

Ok, I think I have found another possible reason. I need a subform linked to each record and put the rectangle on that. It now stays green no matter what record I am on.

 

[agrogers15]

Ok, So I created another table and form with those checkboxes and on the subform put in this code:

Option Compare Database
___________________
Private Sub Form_Current()
If Me.LEEP_Complete = True Then
Me.LEEPHighlight.BackColor = vbGreen
Else
Me.LEEPHighlight.BackColor = vbRed
End If
End Sub
___________________

Private Sub LEEP_Complete_Click()
If Me.LEEP_Complete = True Then
Me.LEEPHighlight.BackColor = vbGreen
Else
Me.LEEPHighlight.BackColor = vbRed
End If
End Sub

I'm getting closer as now it will save the color, but now even when I move to another record the "CF" stays. Any ideas how to make the code apply to each record specifically?

 

[agrogers15]

Ok so now I'm really close! I did end up changing the rectangle to a textbox bound to a field so it would be tied to an individual record. Thanks Micron! Thanks xenou for the FORM_Current idea! Now on my mainform which is a split for there is a + box next to each record, its not until I maximize the record that it will update the subform. Any ideas on why this is?

 

[Micron]

See first four words of post #4
I have no idea what "maximize" a record on a subform means. Don't understand what a + box is either. But I didn't say bind the text box, so I don't know if that's going to help or hinder.
You're using a split form? In that case, the record half can only be a data sheet AFAIK, so that ought to throw coloured rectangles and/or text boxes right out the window.
Subforms update the record when you click off of it after changing/adding data/records, but for a split form, the current event fires when a datasheet record is entered, but not when it is left and you don't select another record (as if you clicked on the "main" form or one of it's controls.

For me, you're going to have to recap and fill in the blanks and don't use phrases not pertinent to Access (if you can help it). I speculated in post 4 as to what type of form you have - I should have asked directly.