Link to home
Start Free TrialLog in
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!
Avatar of mbizup
Flag of Kazakhstan image

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

Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Megin


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.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<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:

Avatar of Megin


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.
Not really "Objecting", but I believe mbizup answered your question here:
(Use conditional formatting)
Can you explain why this did not work?
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.
Avatar of Megin


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.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Megin


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.


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?
Avatar of Megin


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.
Avatar of Megin


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!
Avatar of Megin


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!