Conditional Format & VLOOKUP (Maybe) on Access 2007 Form Redeux
Preamble:
Please, no comments on the fact that this DB doesn't follow general standards. I can't help that right now. I created previous questions and have attempted to take direction from them of creating relationships and using queries, and none of this worked. A lot of time was put into those responses, so I awarded those experts and now I must move on. If you can DO THIS FOR ME, great. Otherwise, I'll have to live without it. It must be done in Access 2007 and provided in accdb file format. If necessary, I can be available by phone.
Question:
On the Issue Details form, I would like to conditionally format the "Assigned to" textbox to be fill/back color yellow if the machine's username matches the current user's username in the Contacts list. The dropdown currently contains a combo box with first and last name from a query called Contacts Extended. I did add Username to that query. I just don't know how to proceed.
Thanks so much for your time.
Issues3.accdb
Please, no comments on the fact that this DB doesn't follow general standards. I can't help that right now. I created previous questions and have attempted to take direction from them of creating relationships and using queries, and none of this worked. A lot of time was put into those responses, so I awarded those experts and now I must move on. If you can DO THIS FOR ME, great. Otherwise, I'll have to live without it. It must be done in Access 2007 and provided in accdb file format. If necessary, I can be available by phone.
Question:
On the Issue Details form, I would like to conditionally format the "Assigned to" textbox to be fill/back color yellow if the machine's username matches the current user's username in the Contacts list. The dropdown currently contains a combo box with first and last name from a query called Contacts Extended. I did add Username to that query. I just don't know how to proceed.
Thanks so much for your time.
Issues3.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay. I seem to have figured out how to get the code in there, but it does not work--no color changes, no error. I do have my PC here at home set up to have my correct username. I did exit the form and change the value to ensure the event would fire, but nothing is happening.
Please go ahead and try what I posted, and post back if you have any trouble. Implemented correctly, it should work.
<<If you can DO THIS FOR ME, great.>>
<< I just need it done.>>
Happy to show you the solution, explain how it works and help you implement it through comments/screenshots/whate
<<If you can DO THIS FOR ME, great.>>
<< I just need it done.>>
Happy to show you the solution, explain how it works and help you implement it through comments/screenshots/whate
ASKER
I have been a member of this site a very long time, and I completely understand the spirit of it, and that's why I wrote the preamble to my question. I don't like when people ask me to do their work for them. But, today, I have no choice. I have spent the past 3 days trying to figure out how to get these things to work, and I am out of time. If I cannot get the solution, I have to do without.
<< I seem to have figured out how to get the code in there,>
Since the code was copy/pasted into your application, you may need to do the following:
- Open your form in design view
- Open the form's property sheet
- Click the "..." next to On Current
That should take you to the current event code that you copied in... and it will also 'connect' the form event to the code.
The current event fires when the form opens and as the user moves from record to record.
If you need this to happen immediately when the user changes a value in the combo box, you will have to add the same code to the After Update event of your combo box.
Since the code was copy/pasted into your application, you may need to do the following:
- Open your form in design view
- Open the form's property sheet
- Click the "..." next to On Current
That should take you to the current event code that you copied in... and it will also 'connect' the form event to the code.
The current event fires when the form opens and as the user moves from record to record.
If you need this to happen immediately when the user changes a value in the combo box, you will have to add the same code to the After Update event of your combo box.
Anyhow - if that doesn't work and you want to ditch the approach I'm taking here, I understand. Just let me know and I'll stop posting/ defer to others.
ASKER
If you need this to happen immediately when the user changes a value in the combo box, you will have to add the same code to the After Update event of your combo box.
Not required, but it's not working. I'm uploading it in case you have time to check my work.
Issues3.accdb
What you have actually seems to work. If you hard-code your username (atroy) the color changes.
Try temporarily commenting out the DLookup and replace it with this (hard-coded):
You mentioned different computers... my guess is that your UserName on the computer you are currently using is not 'atroy'.
Try temporarily commenting out the DLookup and replace it with this (hard-coded):
If Me.Assigned_To.Column(1) = DLookup("[First Name] & ' ' & [Last Name]", "Contacts", "Username = '" & "atroy" & "'") ThenYou mentioned different computers... my guess is that your UserName on the computer you are currently using is not 'atroy'.
One other thing (and this is only doable on your side).
If you haven't done so already, make sure that you have enabled macros/VBA or placed your database in a Trusted Location.
If that hasn't been done, any VBA in your database will simply not run (no errors or any indication of it -- it just won't run).
If you haven't done so already, make sure that you have enabled macros/VBA or placed your database in a Trusted Location.
If that hasn't been done, any VBA in your database will simply not run (no errors or any indication of it -- it just won't run).
ASKER
I don't know what you mean by "hard code". I wrote atroy, added it to the list of contacts, and it still is not changing the color of the background.
I commented out the old line, put in the new, and it still doesn't change anything, with atroy or Anne Troy chosen from the dropdown.
username.png
code.png
I commented out the old line, put in the new, and it still doesn't change anything, with atroy or Anne Troy chosen from the dropdown.
username.png
code.png
ASKER
If you haven't done so already, make sure that you have enabled macros/VBA or placed your database in a Trusted Location.
It's the first thing I do on an Office install/reinstall.
OK -
- Delete your module modCFUserName (only the code you placed in your form is needed).
- Open your form in design view
- Click the "..." next to On Current in your forms property sheet to ensure the event is connected to the code.
What you have is working for me 100 % - so you are almost there.
- Delete your module modCFUserName (only the code you placed in your form is needed).
- Open your form in design view
- Click the "..." next to On Current in your forms property sheet to ensure the event is connected to the code.
What you have is working for me 100 % - so you are almost there.
Just to make sure we are on the same page, this is what I'm seeing:
ASKER
(So I had the code twice, huh? LOL)
*&&^%$# it still doesn't work for me.
I *am* doing the username thing right, aren't I? I'm not much of a Windows 7 person, but I changed my username the other day for testing this. I'm going to go to Excel and do the username thing to make sure that part is working here at home.
*&&^%$# it still doesn't work for me.
I *am* doing the username thing right, aren't I? I'm not much of a Windows 7 person, but I changed my username the other day for testing this. I'm going to go to Excel and do the username thing to make sure that part is working here at home.
ASKER
It's returning Anne... duh. Working on fixing that now. Thanks for your patience!
ASKER
OMG. It's working. I think I'm gonna cry. This has been so frustrating. My bad for not realizing that changing the username in Win 7 was cosmetic only. Bastids.
Thanks so much!
Thanks so much!
ASKER
Nooo! Damn EE! I thought I selected you! I'll fix that.
ASKER
Phew. Got the right one selected for grading this time. Thanks again!
Yay! Very glad you got that working.
The reason for hard coding 'atroy' (using that literal text instead of your username function) was a test to verify the code regardless of what computer/login name you were using.
You should change that back to the original line if you haven't done so already (so that it will base the color on the actual login name instead of using 'atroy' all of the time).
And I understand you are under a time constraint here, but *when* you get a chance, consider implementing a login name / password screen instead of reading the computer username, because as you just noticed that computer username depends on the computer/account you are logged into (versus who you actually are).
The reason for hard coding 'atroy' (using that literal text instead of your username function) was a test to verify the code regardless of what computer/login name you were using.
You should change that back to the original line if you haven't done so already (so that it will base the color on the actual login name instead of using 'atroy' all of the time).
And I understand you are under a time constraint here, but *when* you get a chance, consider implementing a login name / password screen instead of reading the computer username, because as you just noticed that computer username depends on the computer/account you are logged into (versus who you actually are).
ASKER
Did so already...
And I plan to rebuild the whole thing from scratch.
Our usernames don't change... it's not *that* important. It's just something that lets me know this record is mine in some aspect. We don't switch PCs around much.
Wondering if you had a look at my other open Q... :)
And I plan to rebuild the whole thing from scratch.
Our usernames don't change... it's not *that* important. It's just something that lets me know this record is mine in some aspect. We don't switch PCs around much.
Wondering if you had a look at my other open Q... :)
I haven't had a chance yet. Heading out for lunch with a dear high school friend right now but I will check in later on if it is still open.
ASKER
Wow! Thanks for that vote of confidence - but really unnecessary :-)
One thing I've suggested to people who have made similar offers is charitable donations. A couple that mean a lot to me personally at the moment are:
http://www.semperfifund.org/
http://ww5.komen.org/
One thing I've suggested to people who have made similar offers is charitable donations. A couple that mean a lot to me personally at the moment are:
http://www.semperfifund.org/
http://ww5.komen.org/
ASKER
Nice.
When I was in the Navy, we paid you.
And I'm a 3-time survivor myself.
When I was in the Navy, we paid you.
And I'm a 3-time survivor myself.
ASKER
But I tried anyway. I can do #1. I have no idea how to do #2. I tried pasting the code into a new module. Now what?
I don't mean to sound ungrateful for your help.