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

Megin
Megin used Ask the Experts™
on
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)
  
    
   
Else

   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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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 CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
This is why I suggested Conditional formatting in your previous question...
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27730810.html#a38008370
...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?

Jeff
MIS Liason
Most Valuable Expert 2012
Commented:
<Off Topic, and no points wanted>
Not really applicable here as mbizup has already answered your question...

But fust as an FYI, ..whenever you need to do the same thing to many controls, you can "group" all similar controls together via code.
Typically the "tag" property of each control is set to the same value, to get this "similarity"


Dim ctl as control
If Me.Combo41 = "Off Project" then
    For each ctl in me.controls
        If ctl.tag="SomeValue" then
            ctl.ForeColor=RGB(204, 204, 204)
        end if
    Next ctl
Else
    For each ctl in me.controls
        If ctl.tag="SomeValue" then
            ctl.ForeColor=RGB(0, 0, 0)
        end if
    Next ctl
End if

Enjoy the weekend.
;-)

JeffCoachman
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
I've haven't trid this myself, so can't vouch for it, but you might want to look into this article about extending conditional formatting:
http://msdn.microsoft.com/en-us/library/aa139965(v=office.10).aspx
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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:
http://www.lebans.com/conditionalformatting.htm

JeffCoachman
Database122.mdb

Author

Commented:
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 CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Not really "Objecting", but I believe mbizup answered your question here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27732466.html#a38012347
(Use conditional formatting)
Can you explain why this did not work?
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.

Author

Commented:
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.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
You're running into one of Access's limitations there.

The VBA link I posted is kind of a sneaky way to work around that 3-condition limitation (but not defeat it).

I'll try to come up with a sample in the next couple of days here, if you are still interested.

<<
 In my defense, all of this coding and database work kind of makes me want to cry much of the time
>>
I can completely understand that - I think everyone here has been in a similar position before.  And with your feedback here, I'm honestly fine with the deletion.  Its really the response that's important, even if it is just to say "I don't get it, I've completely had it, I'm done!" - which is best for all of us to know sooner rather than later :-)

Also, in positions like that you can always use the Request Attention button to ask for a technical review to decide the best way to close a question.

Author

Commented:
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.

-M
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Megin,

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?

Author

Commented:
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.

Author

Commented:
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)
          
        
    Else
        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)
        
    Else
        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!

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial