Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Question Unanswered: Conditional Fomatting (more than 3 conditions) in Access Report?

    Ok... i just keep staring at the VBA window thinking it's all going to be clear how i do this... but for some strange reason, it's not coming to me....

    I know i need select case, but i can't figure out how to use it in this instance....

    the background... i have a payments report that has "blocks" of payment information in columns....

    txtPayment1Amt
    txtPayment1Date
    txtPayment1Status

    txtPayment2Amt
    txtPayment2Date
    txtPayment2Status

    txtPayment3Amt
    txtPayment3Date
    txtPayment3Status

    so, what i would like is for each record (row), that depending on the status of the payment, it makes the 3 fields a certain color.... so, for john smith, his 1st payment is marked as paid, so txtPayment1Amt, txtPayment1Date, & txtPayment1Status would have a background color of green, while his 2nd payment is marked as NSF so txtPayment2Amt, txtPayment2Date, & txtPayment2Status would have a background color of red... and so on for the status of payment 3.... there are actually 6 different statuses that have conditional formatting...

    Paid (Green)
    NSF (Red)
    Pending (Purple)
    Acct Frozen (Light Blue)
    Acct Closed (Light Purple)
    Stp Pymt (Orange)

    I hope that makes sense...

    I think that the event for the vba happens on the report_load().... help???? teehee


    Rachel- Compensation Analyst

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The event you're looking for is the detail section's format event (or more accurately the format event of the section containing the controls you want to format).
    Paul

  3. #3
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    hmmm... well even programming it with a very cumbersome non-select case methodology seems to make it not work per row... meaning if just one of the rows says "Paid" it makes all of them paid... here is my code... any thoughts on what i messed up?
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.txtPayment1Status = "Paid" Then
    Me.txtPayment1Date.BackColor = RGB(50, 205, 50)
    Me.txtPayment1Status.BackColor = RGB(50, 205, 50)
    Me.txtPayment1Amt.BackColor = RGB(50, 205, 50)

    ElseIf Me.txtPayment1Status = "NSF" Then
    Me.txtPayment1Date.BackColor = RGB(255, 0, 0)
    Me.txtPayment1Status.BackColor = RGB(255, 0, 0)
    Me.txtPayment1Amt.BackColor = RGB(255, 0, 0)

    ElseIf Me.txtPayment1Status = "Pending" Then
    Me.txtPayment1Date.BackColor = RGB(160, 32, 240)
    Me.txtPayment1Status.BackColor = RGB(160, 32, 240)
    Me.txtPayment1Amt.BackColor = RGB(160, 32, 240)


    ElseIf Me.txtPayment1Status = "Acct Frozen" Then
    Me.txtPayment1Date.BackColor = RGB(135, 206, 250)
    Me.txtPayment1Status.BackColor = RGB(135, 206, 250)
    Me.txtPayment1Amt.BackColor = RGB(135, 206, 250)

    ElseIf Me.txtPayment1Status = "Acct Closed" Then
    Me.txtPayment1Date.BackColor = RGB(221, 160, 221)
    Me.txtPayment1Status.BackColor = RGB(221, 160, 221)
    Me.txtPayment1Amt.BackColor = RGB(221, 160, 221)

    ElseIf Me.txtPayment1Status = "Stp Pymt" Then
    Me.txtPayment1Date.BackColor = RGB(255, 140, 0)
    Me.txtPayment1Status.BackColor = RGB(255, 140, 0)
    Me.txtPayment1Amt.BackColor = RGB(255, 140, 0)


    End If

    If Me.txtPayment2Status = "Paid" Then
    Me.txtPayment2Date.BackColor = RGB(50, 205, 50)
    Me.txtPayment2Status.BackColor = RGB(50, 205, 50)
    Me.txtPayment2Amt.BackColor = RGB(50, 205, 50)

    ElseIf Me.txtPayment2Status = "NSF" Then
    Me.txtPayment2Date.BackColor = RGB(255, 0, 0)
    Me.txtPayment2Status.BackColor = RGB(255, 0, 0)
    Me.txtPayment2Amt.BackColor = RGB(255, 0, 0)

    ElseIf Me.txtPayment2Status = "Pending" Then
    Me.txtPayment2Date.BackColor = RGB(160, 32, 240)
    Me.txtPayment2Status.BackColor = RGB(160, 32, 240)
    Me.txtPayment2Amt.BackColor = RGB(160, 32, 240)


    ElseIf Me.txtPayment2Status = "Acct Frozen" Then
    Me.txtPayment2Date.BackColor = RGB(135, 206, 250)
    Me.txtPayment2Status.BackColor = RGB(135, 206, 250)
    Me.txtPayment2Amt.BackColor = RGB(135, 206, 250)

    ElseIf Me.txtPayment2Status = "Acct Closed" Then
    Me.txtPayment2Date.BackColor = RGB(221, 160, 221)
    Me.txtPayment2Status.BackColor = RGB(221, 160, 221)
    Me.txtPayment2Amt.BackColor = RGB(221, 160, 221)

    ElseIf Me.txtPayment2Status = "Stp Pymt" Then
    Me.txtPayment2Date.BackColor = RGB(255, 140, 0)
    Me.txtPayment2Status.BackColor = RGB(255, 140, 0)
    Me.txtPayment2Amt.BackColor = RGB(255, 140, 0)


    End If


    If Me.txtPayment3Status = "Paid" Then
    Me.txtPayment3Date.BackColor = RGB(50, 205, 50)
    Me.txtPayment3Status.BackColor = RGB(50, 205, 50)
    Me.txt3PayAmt.BackColor = RGB(50, 205, 50)

    ElseIf Me.txtPayment3Status = "NSF" Then
    Me.txtPayment3Date.BackColor = RGB(255, 0, 0)
    Me.txtPayment3Status.BackColor = RGB(255, 0, 0)
    Me.txt3PayAmt.BackColor = RGB(255, 0, 0)

    ElseIf Me.txtPayment3Status = "Pending" Then
    Me.txtPayment3Date.BackColor = RGB(160, 32, 240)
    Me.txtPayment3Status.BackColor = RGB(160, 32, 240)
    Me.txt3PayAmt.BackColor = RGB(160, 32, 240)


    ElseIf Me.txtPayment3Status = "Acct Frozen" Then
    Me.txtPayment3Date.BackColor = RGB(135, 206, 250)
    Me.txtPayment3Status.BackColor = RGB(135, 206, 250)
    Me.txt3PayAmt.BackColor = RGB(135, 206, 250)

    ElseIf Me.txtPayment3Status = "Acct Closed" Then
    Me.txtPayment3Date.BackColor = RGB(221, 160, 221)
    Me.txtPayment3Status.BackColor = RGB(221, 160, 221)
    Me.txt3PayAmt.BackColor = RGB(221, 160, 221)

    ElseIf Me.txtPayment3Status = "Stp Pymt" Then
    Me.txtPayment3Date.BackColor = RGB(255, 140, 0)
    Me.txtPayment3Status.BackColor = RGB(255, 140, 0)
    Me.txt3PayAmt.BackColor = RGB(255, 140, 0)


    End If



    End Sub

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What version? If >=2007, make sure you're opening in Preview rather than Report view.
    Paul

  5. #5
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    it's in 2007 and i am looking at the preview... it's allllll greeeen (just cause ONE of the records is green)

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Typically that caused by not having an "Else" to handle other values, but you appear to have that covered. Can you post the db?
    Paul

  7. #7
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    not really... the db has waaaaaay too many confidential elements to it... SSN, banking info, etc. Dang it... whyyyyyyyyyyyyyyyy won't this woooorrrrrrrrrrrrrrrrrkkkkkkkkk???!!!!!

    Rachel

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Understandable. Set a breakpoint at the beginning of the code so you can step through it and maybe you'll see where the code is going wrong. Are you sure the status field contains text and not an ID value?
    Paul

  9. #9
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    harumph... well i don't understand why, but it appears if i do the elseif statement for EVERY value that might appear in the field, it works... i always thought that it would do nothing if you didn't specify criteria that was met... but i guess you have to spell out what to do for every iteration... weird. Thanks for your help!

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not necessarily, but you can add an Else to do what you want if none of the specified conditions are met. As it is, it wouldn't change what was previously set, so that's likely why they're all the same.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •