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:
Is this something simple, like defining the other all the other options in that box as being black with more if statements?
Thank you!
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 SubIs this something simple, like defining the other all the other options in that box as being black with more if statements?
Thank you!
This is why I suggested Conditional formatting in your previous question...
https://www.experts-exchange.com/questions/27730810/Setting-up-conditional-fore-color-in-access-form.html?anchorAnswerId=38008370#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
https://www.experts-exchange.com/questions/27730810/Setting-up-conditional-fore-color-in-access-form.html?anchorAnswerId=38008370#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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<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
"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
ASKER
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.
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:
https://www.experts-exchange.com/questions/27732466/How-do-I-make-this-change-only-the-record-showing-and-not-all.html?anchorAnswerId=38012347#a38012347
(Use conditional formatting)
Can you explain why this did not work?
https://www.experts-exchange.com/questions/27732466/How-do-I-make-this-change-only-the-record-showing-and-not-all.html?anchorAnswerId=38012347#a38012347
(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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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?
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?
ASKER
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.
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.
ASKER
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:
Thank you for all the help and patience!
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 SubThank you for all the help and patience!
ASKER
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!
Thank you, again, everyone, for helping me out with this!
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.