conditional formatting rules manager for access 2010

[Paintballlovr]

Does anyone know if you can use a call to a table in setting up conditional formatting? I have a ticket number field on a form that I want to backcolor, depending on if the ticket number is present in a table. The wizard will take you into an expression builder, but it doesn't seem to work when I use it. The wizard works fine if I specify a single ticket number, but I would like to use the table because the list will evolve and a ticket number that would get backcolored today may not tomorrow.



The full rule the wizard created is: field value is | Equal to | [tblTEMP_UPDATED_ISSUES_NOT_RCTEAM]![ProbNum]

If the wizard is not the answer, is there another option to apply conditional formatting in this manner?

[June7]

Cannot call a table like that anywhere. Try DLookup. Is ProbNum a text field?

Expression Is: Not IsNull(DLookup("ProbNum", "tblTEMP_UPDATED_ISSUES_NOT_RCTEAM", "ProbNum='" & [ProbNum] & "'"))

[Paintballlovr]

Probnum has a datatype of number

[June7]

Then remove the apostrophe delimiters:

"ProbNum=" & [ProbNum]))

[Paintballlovr]

Even if the database hadn't thrown up an error, I knew this was wrong out of the gate. Here is how I modified your code to try and bend it to my will

If Not IsNull(DLookup("ProbNum", "tblTEMP_UPDATED_ISSUES_NOT_RCTEAM", "ProbNum=" & [IDTEXT])) Then
DoCmd.SetProperty "idtext", acPropertyBackColor, vbBlue
End If

I'm having one of those days and can't seem to get anything to work right. The text box on my form has the name IDTEXT and the control source is ID

[June7]

The VBA would be: Me.yourcontrolname.BackColor = vbBlue

Is the form in Continuous or Datasheet view? Using VBA to set properties will not work nice. Have to use Conditional Formatting.

I thought you were using Conditional Formatting.

You said the rule was whether or not ProbNum is in the table. My suggested DLookup should determine that. What does IDTEXT and ID have to do with this? Is ProbNum a foreign key for ID?

[Paintballlovr]

Sorry, I went about 7 steps past on the over thinking scale. The form is continuous and you are right, I was trying to use conditional formatting. I saw your answer and for some reason my mind jumped from there to vba so I was trying to modify it for that purpose. Now that I have pulled my head out, I went back and put the code in the expression builder for conditional formatting. I am getting the backcolor I want, but it is applying to all rows. Not sure if this is a function of the form being continuous or if they really all exist in the table. I can verify that.

[June7]

It is not a function of the form being continuous. Conditional Formatting is designed to work with Continuous or Datasheet view.

What is the relationship of ProbNum and ID - FK/PK?

[Paintballlovr]

Probnum is a number field in my temp table. ID is an autonumber from my master table. Neither one is set to Primary.

[June7]

If the autonumber field is not a primary then what is its purpose? Why are you using expression that compares the two values? If there is no relationship, why do this?

[Paintballlovr]

The master table existed long before I came along and I haven't made any changes to it (which is probably a pretty weak argument for it not having a primary key). If I need to make changes to tables, I'm ok with that, I was just trying to find the path of least resistance. My temp table is a make table, so I couldn't establish a relationship between the two. If that is required or will assist the fix, I can change it to an append query and just wipe the table each time I'm finished using it. Ultimately, I wanted to use the temp table as a list of IDs that should be highlighted, leaving the ones not in the temp table "normal".

[June7]

"Temp" table should not be repeatedly deleted and recreated. The table should be permanent and records should be purged.

I still don't know why you changed the DLookup to compare ProbNum and ID. Are ProbNum and ID the same data?

[Paintballlovr]

Ok, hopefully some sleep will help stay me focused on this better today. I have modified the temp table to append and delete records instead of deleting the table and recreating it each time. I was tracking wrong with you yesterday when I was modifying the dlookup, I have gone back to the original code you supplied. Probnum and ID is the same data in two different tables. ID is the name of the probnum in my master table and when I make a list of the records from the master table where the last update is from someone outside of my target userlist (my temp table), the probnum is actually called probnum. As an example, my master table has records one thru five. records two and five have their most recent updated from someone on my target list of users, so my temp table has just two and five. I want my form to display all five records, but highlight two and five with a different back color, so I can visually tell which ones have been updated by someone from the target list of users.

[June7]

I wonder the temp table is actually necessary. What method do you use to determine the 'target' users and write records?

Otherwise, is this solved?

[Paintballlovr]

I guess there are a couple of other players I didn't mention, so here is the whole process:

table 1 has a list of users on my team
table 2 is the master table with the work orders
table 3 is the table that houses the comments which appear in the subform on the work orders
table 4 is the temp table, which is a list of all work orders where the most recent comment is from someone other than who appear in table 1

SO, each time the worklist is opened, I run a query to delete all of the records from table 4 and immediately follow that up with a query that appends an updated list of records back to table 4. This ensures that my list in table 4 is current as of the time when the worklist is opened. When the worklist opens, I want the database to look at each work order number and if that number appears in table 4, it back colors the field, so when I'm looking at the list of work orders, I can tell right away which ones have been most recently noted by someone outside of my team. I have not had a chance to go back and work on this yet today, so I'm still at the point where it either back colors all of the work orders or none of them.