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

  • 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

    Tuesday, October 23, 2018 7:30 AM

Answers

  • 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.

    • Marked as answer by Nghi Trinh Monday, November 5, 2018 3:04 PM
    Friday, November 2, 2018 4:12 PM

All replies

  • Me.Status.Backcolor=vbRed should work. You forgot to include Me.
    Tuesday, October 23, 2018 1:07 PM
  • I tried but it not work.
    Tuesday, October 23, 2018 2:26 PM
  • Hi,

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

    Just a thought...

    Tuesday, October 23, 2018 2:35 PM
  • 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

    Tuesday, October 23, 2018 2:47 PM
  • 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.

    Tuesday, October 23, 2018 3:54 PM
  • It does not work Imb
    Wednesday, October 24, 2018 5:46 AM
  • 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.

    Wednesday, October 24, 2018 5:54 AM
  • 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...

    Wednesday, October 24, 2018 3:57 PM
  • 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

    Wednesday, October 24, 2018 4:39 PM
  • 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.


    Friday, October 26, 2018 5:37 AM
  • 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?

    Friday, October 26, 2018 3:27 PM
  • 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

    Sunday, October 28, 2018 8:13 PM
  • Yes sir, but it not work so far
    Monday, October 29, 2018 2:29 AM
  • 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?

    Monday, October 29, 2018 2:36 AM
  • 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?
    Monday, October 29, 2018 3:25 PM
  • 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.

    Monday, October 29, 2018 6:19 PM
  • Hi Guy

    here is my db Click

    Tuesday, October 30, 2018 2:50 AM
  • Dear Sir

    Do you have any idea for my points?

    Friday, November 2, 2018 1:53 AM
  • 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.

    • Marked as answer by Nghi Trinh Monday, November 5, 2018 3:04 PM
    Friday, November 2, 2018 4:12 PM