Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Question Unanswered: Keeping Backcolor in field on Current Form?

    When I am moving from record to record, it is not setting the backcolor based on my criteria. Am I doing something wrong?

    Private Sub Form_Current()
    If Me.EVENT_TYPE = "BIRTH" And Me.TOTAL1 > 300 And Not Me.TOTAL1 = "n/a" Then
    Me.TOTAL1.BackColor = vbYellow
    Else: Me.TOTAL1.BackColor = 12632256
    End If

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This looks like a job for.... Conditional Formatting

    In form view, goto Format--> Conditional Formatting
    and use the wizard

    It allows you to change the format of one field, based on the
    value(s) of other fields.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Oct 2006
    Posts
    110
    Yes, that would work if I only had 1 condition, I have 3 different scenerios on this 1 field, so conditional formatting wouldnt work for me.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    There's an "Add" button in the wizard that let's you set up to 3 conditions.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Oct 2006
    Posts
    110
    Thanks, but conditional formatting is not really what im looking for.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    picky, picky....

    Ok, have you tried stepping through your code to see why it is not doing what you expect?
    Inspiration Through Fermentation

  7. #7
    Join Date
    Oct 2006
    Posts
    110
    Yes and I believe it is happening with On Current.. because when I find a record that meets the criteria, and sets the backcolor to yellow, then as I cycle though records using the back/forth record button, all my data in my total1 field is yellow. It's like once it sets the backcolor it applies it to every record, and doesnt check the code as I move from each record.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Well, this doesn't look right

    Else: Me.TOTAL1.BackColor = 12632256

    It should be

    Else
    Me.TOTAL1.BackColor = 12632256

    I've never seen the ":" used like that before. I'm surprised it compiles.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Oct 2006
    Posts
    110
    vba adds the : if I put Me.NUM_REP.BackColor = 12632256 on the same line as the else .

  10. #10
    Join Date
    Oct 2006
    Posts
    110
    I am just going to add this, and it will fix it. I thought I could just use a else...

    If Me.Combo18.Value = "BIRTH" And Me.TOTAL1 <= 300 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = 12632256
    If Me.Combo18.Value = "DEATH" And Me.TOTAL1 <= 150 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = 12632256
    If Me.Combo18.Value = "FETAL DEATH" And Me.TOTAL1 <= 50 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = 12632256

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Those conditions, as well as the name of one of your fields, are completely different than what was in your original post. *sigh*
    Inspiration Through Fermentation

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since I didn't notice that you were actually comparing only 2 fields, and not 3.

    Your problem lies in Total1 sometimes containing a number, and sometimes text. Total1 cannot be a number less than 300, and equal to "n/a" at the same time.
    Inspiration Through Fermentation

  13. #13
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by cmays637
    I am just going to add this, and it will fix it. I thought I could just use a else...

    If Me.Combo18.Value = "BIRTH" And Me.TOTAL1 <= 300 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = 12632256
    If Me.Combo18.Value = "DEATH" And Me.TOTAL1 <= 150 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = 12632256
    If Me.Combo18.Value = "FETAL DEATH" And Me.TOTAL1 <= 50 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = 12632256
    What I did in one of my databases is put the Code in the AfterUpdate of the PrimaryKey for that field/record:

    Code:
    Private Sub ToolID_AfterUpdate()
    
        If ToolStatus = "Out" Then
        ToolID.BackColor = 12632256
        End If
    End Sub
    When a Tool's Status was OUT, being checked out, what would happen then was the complete LINE for that or any tool with the OUT status would become GRAY. The entire line/record. That was all and it works very well. I didn't do an ELSE because once they checked back IN the status was no longer OUT or "1" and the Requery took it back to normal.

    have a nice one
    BUD

  14. #14
    Join Date
    Oct 2006
    Posts
    110
    Redneckgeek - Sorry I was getting confused there with my fields.. That can happen when your frazzled..

    Thanks, you caught my mistake, I dont need the "And Not Me.TOTAL1 = "n/a" when I am using my backcolor as gray (12632256) since I already have that for the greater than scenerios.

    This will work for what I need.I was just thinking of a shorter way..like using the else..

    Thanks for all your help.
    Chris


    If Me.EVENT_TYPE = "BIRTH" And Me.TOTAL1 > 300 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = vbYellow
    If Me.EVENT_TYPE = "DEATH" And Me.TOTAL1 > 150 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = vbYellow
    If Me.EVENT_TYPE = "FETAL DEATH" And Me.TOTAL1 > 50 And Not Me.TOTAL1 = "n/a" Then Me.TOTAL1.BackColor = vbYellow


    If Me.EVENT_TYPE = "BIRTH" And Me.TOTAL1 <= 300 Then Me.TOTAL1.BackColor = 12632256
    If Me.EVENT_TYPE = "DEATH" And Me.TOTAL1 <= 150 Then Me.TOTAL1.BackColor = 12632256
    If Me.EVENT_TYPE = "FETAL DEATH" And Me.TOTAL1 <= 50 Then Me.TOTAL1.BackColor = 12632256

Posting Permissions

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