Change backcolor of field on subform (datasheet) according specific condition

Question

hi all

For event of subform i create one code as below:

Private Sub Form_Open(Cancel As Integer)
If Me.Status = "Overdue" Then
Status.BackColor = vbRed

End If
End Sub

But i can not see any change when open form. If I use conditional format, the result is OK but this case i want to use VBA because following i will have some combined  condition to control some other fields

Answer 1

Me.Status.Backcolor=vbRed should work. You forgot to include Me.

Answer 2

I tried but it not work.

Answer 3

Hi,

Rather than the Open event, try using the form's Current event.

Just a thought...

Answer 4

For a datasheet or continuous form, where you want some rows to be formatted specially and others not, conditional formatting is the only option.  Any approach where you set the BackColor of a control in VBA will result in all rows having that back color.

How many combined conditions do you need distinct formatting for?  Conditional formatting can probably handle it, so long as the format properties in question are those that CF will manage, but you may possibly have to use VBA to set the format conditions, rather than doing it through the user interface.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Private Sub Form_Open(Cancel As Integer)

Hi Nghi,

In the Open event the RecordSet may not yet be loaded. You could try this code in the Load event.

Imb.

Answer 6

It does not work Imb

Answer 7

Yes Drik, I just want some row that meet condition will be formatted, combine condition as below:

If me.Status ="overdue" then

Me.Status.backcolor = vbred

else

if me.filed1 <> "A" and me.field2 < now() then

me.Filed2.backcolor = vbred

...

end if

end if

But i only try one condition firstly but it does not work. I don't know why. even I try on form (single form) but the result is the same, nothing change. hic Please help me.

Answer 8

Hi,

If you tried using the Current event and it didn't work, then you could try stepping through your code to make sure you're evaluating the correct condition for the format you want to apply.

Just my 2 cents...

Answer 9

Are you trying with the actual Conditional Formatting feature, or are you still trying to do this with VBA?  Your example is in VBA, which will never work for what I understand you want to do.  But the logic of your VBA example could easily be replicated with conditional formatting.

If you're trying to use the actual Conditional Formatting feature, please show your real conditional formatting rules so we can figure out what's wrong.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 10

Hi Drik

You can see my form, if Status ="overdue" then Status.backcolor = Red. we no longer to care following condition. But in case if Status <> " Overdue" then we will consider next condition: status1, status2...If Status1="A" --> no care to DueDate1 we will pass to Status2 and DueDate2. But if Status1 <>"A" and DueDate1 < Now() then Duedate1.backcolor = Red. It is same with next condition. How to set them in conditional formatting. Please help me.

Answer 11

Hi,

Just to make sure you saw my posts and they didn't help, let me ask you for a confirmation. Did you try your code using the Current event?

Answer 12

Note:  it is generally a bad idea to have repeating groups of fields, like "Status1", "Status2", and "Status3", and "DueDate1, "DueDate2", "DueDare3".  That has nothing directly to do with your formatting problem, but you should be aware of the issue, as it forces increasingly complex, eventually unmaintainable, logic.

With regard to the conditional formatting problem, I repeat my previous question:  are you trying the actual Conditional Formatting feature that is built into Access, or are you still trying to use VBA (which will not work for a datasheet or continuous form).  If you are actually trying to use the built-in conditional formatting feature, don't show me your form; show me the conditional formatting rules that you have set up for the controls in question.

You can do this in VBA only for the current record on a form -- if it's a continuous form or datasheet, all the records on the form will take on the formatting that you assign for the current record, and that is not what you want.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 13

Yes sir, but it not work so far

Answer 14

Dear Dirk

Actually i don't have any idea when set conditional formatting rules for many condition like this. I just success when set only one condition. For instance, if value="overdue" then backcolor = red in the same field, but I don't know how to link  multi condition from 2 or 3 fields. do you have any idea?

Answer 15

Yes sir, but it not work so far

Okay, thanks. Are you able to share a copy of your db, so we can better understand why it doesn't work?

Answer 16

It does not work Imb

Hi Nghi,

A complete other thought.

Instead of conditional formatting to highlight the "Overdue" records, you can modify the RecordSource of the form to select the relevant records with Status = "Overdue". In that case it is not a problem to set the Backcolor of the controls to vbRed.

In case of a different Status, modify the RecordSource again to display all records with that different Status

It is not necessary anymore for the user to "filter" the selection "Overdue" (with of the red backcolor), because all displayed records are "Overdue".

In fact this is the way I normally work. Users have a number of buttons to set "their selection".

Imb.

Answer 17

Hi Guy

here is my db Click

Answer 18

Dear Sir

Do you have any idea for my points?

Answer 19

I downloaded your db and was able to make this code work for the On Current Event in the FrmSubCardfilterforAll form:

If Me.Status = "Overdue" Then
    Me.IDCard.BackColor = vbRed
Else
    Me.IDCard.BackColor = vbWhite
End If

ONLY if the form is opened in Form View. So set the forms Default View to Single Form and set the forms Navigation Buttons to 'Yes'. As you move from record-to-record, the Background color will change from White to Red if the 'Status' is "Overdue".

It will not work if the form is opened in Datasheet View or Continous View.