Avatar of Megin
 asked on

How do I make this change only the record showing and not all?

I am using the following code to make items in my form have a grey forecolor when the status combo41 says "Off Project." The problem now is that when I change one record to "Off Project" every record in the system turns grey.

Here is the code:

Private Sub Combo41_AfterUpdate()
If Me.Combo41 = "Off Project" Then
    Me.[First Name].ForeColor = RGB(204, 204, 204)

    Me.[Last Name].ForeColor = RGB(204, 204, 204)
    Me.[E-mail Address].ForeColor = RGB(204, 204, 204)
    Me.[Non Wsdot Email Address].ForeColor = RGB(204, 204, 204)
    Me.[Phone].ForeColor = RGB(204, 204, 204)
    Me.[Mobile Phone].ForeColor = RGB(204, 204, 204)
    Me.[WSDOTOrg].ForeColor = RGB(204, 204, 204)
    Me.[WSDOT Manager].ForeColor = RGB(204, 204, 204)
    Me.[Combo39].ForeColor = RGB(204, 204, 204)
    Me.[Firm Information].ForeColor = RGB(204, 204, 204)
    Me.[Phone Number_CompanyInfo].ForeColor = RGB(204, 204, 204)
    Me.[Reports to].ForeColor = RGB(204, 204, 204)
    Me.[Assigned Admin].ForeColor = RGB(204, 204, 204)
    Me.[Text54].ForeColor = RGB(204, 204, 204)
    Me.[Text493].ForeColor = RGB(204, 204, 204)
    Me.[Office].ForeColor = RGB(204, 204, 204)
    Me.[Combo41].ForeColor = RGB(204, 204, 204)
    Me.[Direct Connect Number].ForeColor = RGB(204, 204, 204)

   Me.[First Name].ForeColor = RGB(0, 0, 0)

   Me.[Last Name].ForeColor = RGB(0, 0, 0)
   Me.[E-mail Address].ForeColor = RGB(0, 0, 0)
   Me.[Non Wsdot Email Address].ForeColor = RGB(0, 0, 0)
   Me.[Phone].ForeColor = RGB(0, 0, 0)
   Me.[Mobile Phone].ForeColor = RGB(0, 0, 0)
   Me.[WSDOTOrg].ForeColor = RGB(0, 0, 0)
   Me.[WSDOT Manager].ForeColor = RGB(0, 0, 0)
   Me.[Combo39].ForeColor = RGB(0, 0, 0)
   Me.[Firm Information].ForeColor = RGB(0, 0, 0)
   Me.[Phone Number_CompanyInfo].ForeColor = RGB(0, 0, 0)
   Me.[Reports to].ForeColor = RGB(0, 0, 0)
   Me.[Assigned Admin].ForeColor = RGB(0, 0, 0)
   Me.[Text54].ForeColor = RGB(0, 0, 0)
   Me.[Text493].ForeColor = RGB(0, 0, 0)
   Me.[Office].ForeColor = RGB(0, 0, 0)
   Me.[Combo41].ForeColor = RGB(0, 0, 0)
   Me.[Direct Connect Number].ForeColor = RGB(0, 0, 0)
End If

End Sub

Open in new window

Is this something simple, like defining the other all the other options in that box as being black with more if statements?

Thank you!
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

If you change the color of a control, that change will appear in all records (despite different records, it is only one control).  This is most apparent in continuous form view, since all records are displayed together.

To have distinct formatting in different rows, you need to use Conditional Formatting.  Right click the control in design view, and select "Conditional Formatting" -- then enter the expression for the criters and choose the colors, etc.
Jeffrey Coachman

This is why I suggested Conditional formatting in your previous question...
...But you stated that you had "Too many conditions" (more than 3)

I only see one condition here:
 Me.Combo41 = "Off Project"

I see many controls being set, ...but only one condition.
Did I miss something?

Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you for the suggestions, Jeff!

I am not sure what to do about the conditional formating. I have three conditions set in the Form Design Tools - Conditional section of access. Access won't appear to allow me any more conditions that way. The conditions that are already set change the background color based on a particular control. So, I figured I could just set up these conditions in VBA code.

Now when I change one record to say "off project" it changes all of the records to grey font. I just want the one record that says "Off Project" to be in grey font. Is there another way to do this? I have to believe that there is. But the Conditional Formatting box will not allow me to add any more conditions there.
Your help has saved me hundreds of hours of internet surfing.
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

<Now when I change one record to say "off project" it changes all of the records to grey font.>
"Now" with what code?
It is not clear what combination of VBA and conditional formatting you are currently using that is greying out "All" the records?
As mbizup stated, this is simply what continuous forms "do".

Perhaps some clarification is needed.
You want the *Selected Record Only* to be grey if it is 'off Project'?
...or do you want all records with "Off Project" to be grey?
Do you want all the fields, including the combobox, to be grey?

Am I missing something?
Why not just set the controls to be grey using one conditional formatting Option?
In other words what is "Wrong" with the attached sample db, Form1

If your goal is to grey only the "Current" (Selected) record, if it is "Off Project", then this is tricky with a continuous form.
See here for some ways to do this:


I've requested that this question be deleted for the following reason:

I never received an answer that worked and I have moved on from that issue for now.
Jeffrey Coachman

Not really "Objecting", but I believe mbizup answered your question here:
(Use conditional formatting)
Can you explain why this did not work?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

It would have been nice to get some feedback from the original poster to the last two comments.  We would have helped see this through.   The delete request seemed to be initiated to avoid the abandoned question lockout to post a new question.  

I believe the question as originally posted was addressed in the first two posts, and in the absense of the Author's reply recommend accepting http:#a38012347 and http:#a38014018.  After that point, the thread deviated a bit from what was initially asked.

Hi! Original Poster here.

I apologize for leaving everyone hanging on this question. To be honest, I got really frustrated and decided to avoid it for a while. I have tried the answers posted, but they haven't worked for me. Today I carefully when over the article referred to in ID: 38023244 and have tried it, but, being very new to VBA, I am confused and it isn't working for me. I already have three conditions listed in the Access Conditional block located in the toolbar ( I know that is probably not what it is called). It only allows three conditions to be set there.

I want to set up the rest of the conditions with vba code, but I can't figure it out. When I did attempt to set it up, all that I managed to do was set the color of all the records, instead of the ones specified in the condition.

I really didn't mean to upset anyone and, again, I apologize. In my defense, all of this coding and database work kind of makes me want to cry much of the time. I got frustrated and walked way. I shouldn't have done that. I really do appretiate all the help I have gotten here at this site.
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Thank you!

Thank you for being so understanding.
I would absolutely still be interested in seeing a sample.

And next time, if in this situation again, I will handle things differently.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.


What are your other format conditions (the ones that you have defined in Conditional Formatting - including expressions and colors/etc)?   Depending on what you've got, we might be able to consolidate things.

Also - is Access 2010 an option for you?

Sorry for my lateness here. I have been assigned to a different office for this week and unable to check my email.

Access 2010 is not yet an option for me.

My conditions that are set are based on one field of the form. There is an office field that has various locations listed. If the office field is equal to four, the background turns green. If it is equal to 5, the background turns blue. If it is equal to 6, the background turns pink.

These conditions are set for every field on the form.

I need to add more conditions, but Access only allows me three.

I got the thing to work!

I think that I was just putting the code under the wrong event. It ended up being very simple and I am sorry about having such a thick head. Here is what worked:

Option Compare Database

Private Sub Form_Current()
    If Me.Status = "Off Project" Then
        Me.FirstName.ForeColor = RGB(192, 192, 192)
        Me.LastName.ForeColor = RGB(192, 192, 192)
        Me.EmailAddress.ForeColor = RGB(192, 192, 192)
        Me.nonwsdotemailaddress.ForeColor = RGB(192, 192, 192)
        Me.Phone.ForeColor = RGB(192, 192, 192)
        Me.MobilePhone.ForeColor = RGB(192, 192, 192)
        Me.WSDOTOrg.ForeColor = RGB(192, 192, 192)
        Me.wsdotmanager.ForeColor = RGB(192, 192, 192)
        Me.Combo39.ForeColor = RGB(192, 192, 192)
        Me.FirmInformation.ForeColor = RGB(192, 192, 192)
        Me.PhoneNumber_CompanyInfo.ForeColor = RGB(192, 192, 192)
        Me.Reportsto.ForeColor = RGB(192, 192, 192)
        Me.AssignedAdmin.ForeColor = RGB(192, 192, 192)
        Me.Text54.ForeColor = RGB(192, 192, 192)
        Me.Text493.ForeColor = RGB(192, 192, 192)
        Me.Office.ForeColor = RGB(192, 192, 192)
        Me.Status.ForeColor = RGB(192, 192, 192)
        Me.DirectConnectNumber.ForeColor = RGB(192, 192, 192)
        Me.AccessCardNumber.ForeColor = RGB(192, 192, 192)
        Me.DeactivationDate.ForeColor = RGB(192, 192, 192)
        Me.FirstName.ForeColor = RGB(0, 0, 0)
        Me.LastName.ForeColor = RGB(0, 0, 0)
        Me.EmailAddress.ForeColor = RGB(0, 0, 0)
        Me.nonwsdotemailaddress.ForeColor = RGB(0, 0, 0)
        Me.Phone.ForeColor = RGB(0, 0, 0)
        Me.MobilePhone.ForeColor = RGB(0, 0, 0)
        Me.WSDOTOrg.ForeColor = RGB(0, 0, 0)
        Me.wsdotmanager.ForeColor = RGB(0, 0, 0)
        Me.Combo39.ForeColor = RGB(0, 0, 0)
        Me.FirmInformation.ForeColor = RGB(0, 0, 0)
        Me.PhoneNumber_CompanyInfo.ForeColor = RGB(0, 0, 0)
        Me.Reportsto.ForeColor = RGB(0, 0, 0)
        Me.AssignedAdmin.ForeColor = RGB(0, 0, 0)
        Me.Text54.ForeColor = RGB(0, 0, 0)
        Me.Text493.ForeColor = RGB(0, 0, 0)
        Me.Office.ForeColor = RGB(0, 0, 0)
        Me.Status.ForeColor = RGB(0, 0, 0)
        Me.DirectConnectNumber.ForeColor = RGB(0, 0, 0)
        Me.AccessCardNumber.ForeColor = RGB(0, 0, 0)
        Me.DeactivationDate.ForeColor = RGB(0, 0, 0)
    End If
    If Me.Status = "520 Team" Then
        Me.FirstName.BackColor = RGB(0, 255, 255)
        Me.LastName.BackColor = RGB(0, 255, 255)
        Me.EmailAddress.BackColor = RGB(0, 255, 255)
        Me.nonwsdotemailaddress.BackColor = RGB(0, 255, 255)
        Me.Phone.BackColor = RGB(0, 255, 255)
        Me.MobilePhone.BackColor = RGB(0, 255, 255)
        Me.WSDOTOrg.BackColor = RGB(0, 255, 255)
        Me.wsdotmanager.BackColor = RGB(0, 255, 255)
        Me.Combo39.BackColor = RGB(0, 255, 255)
        Me.FirmInformation.BackColor = RGB(0, 255, 255)
        Me.PhoneNumber_CompanyInfo.BackColor = RGB(0, 255, 255)
        Me.Reportsto.BackColor = RGB(0, 255, 255)
        Me.AssignedAdmin.BackColor = RGB(0, 255, 255)
        Me.Text54.BackColor = RGB(0, 255, 255)
        Me.Text493.BackColor = RGB(0, 255, 255)
        Me.Office.BackColor = RGB(0, 255, 255)
        Me.Status.BackColor = RGB(0, 255, 255)
        Me.DirectConnectNumber.BackColor = RGB(0, 255, 255)
        Me.AccessCardNumber.BackColor = RGB(0, 255, 255)
        Me.DeactivationDate.BackColor = RGB(0, 255, 255)
        Me.Notes.BackColor = RGB(0, 255, 255)
        Me.Floor.BackColor = RGB(0, 255, 255)
        Me.SeatNumber.BackColor = RGB(0, 255, 255)
        Me.FirstName.BackColor = RGB(255, 255, 255)
        Me.LastName.BackColor = RGB(255, 255, 255)
        Me.EmailAddress.BackColor = RGB(255, 255, 255)
        Me.nonwsdotemailaddress.BackColor = RGB(255, 255, 255)
        Me.Phone.BackColor = RGB(255, 255, 255)
        Me.MobilePhone.BackColor = RGB(255, 255, 255)
        Me.WSDOTOrg.BackColor = RGB(255, 255, 255)
        Me.wsdotmanager.BackColor = RGB(255, 255, 255)
        Me.Combo39.BackColor = RGB(255, 255, 255)
        Me.FirmInformation.BackColor = RGB(255, 255, 255)
        Me.PhoneNumber_CompanyInfo.BackColor = RGB(255, 255, 255)
        Me.Reportsto.BackColor = RGB(255, 255, 255)
        Me.AssignedAdmin.BackColor = RGB(255, 255, 255)
        Me.Text54.BackColor = RGB(255, 255, 255)
        Me.Text493.BackColor = RGB(255, 255, 255)
        Me.Office.BackColor = RGB(255, 255, 255)
        Me.Status.BackColor = RGB(255, 255, 255)
        Me.DirectConnectNumber.BackColor = RGB(255, 255, 255)
        Me.AccessCardNumber.BackColor = RGB(255, 255, 255)
        Me.DeactivationDate.BackColor = RGB(255, 255, 255)
        Me.Notes.BackColor = RGB(255, 255, 255)
        Me.Floor.BackColor = RGB(255, 255, 255)
        Me.SeatNumber.BackColor = RGB(255, 255, 255)
End If

End Sub

Open in new window

Thank you for all the help and patience!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

The grade is for everyone trying to help me with this, and not for the bulky solution I finished with. I know there are probably cleaner ways to have done this, without so much repetative code, but it ended up working and I am going to leave it at that.

Thank you, again, everyone, for helping me out with this!