• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2008
  • Last Modified:

Access 2010 Field conditional format on a form from an external table field

I have a Form with a field that I want use a conditional format on, but the condition is in another table is a field for the condition.
Example:
 Form1 has the field which needs to go to a table that has three fields and match the first field in the table.
When there is a match in the first field in the table, then check the third field in the table for that value= "Y",
IF that is a match then color the field on the form.
I know how to do a conditional format on the field on the form,
its the going to a table lookup to verify the two fields and come back.
I am running Win7 x64 and Access 2010 x86 and the Tables or linked to a SQL backend.

My Table has these fields: dcode, dname, promo.
The form field to put the condition on is called  dname
They are all text fields.

Can anyone give me some guidance, I am sure that it is an expression that I need in the Conditional formatting on the field.
0
doublex
Asked:
doublex
1 Solution
 
hnasrCommented:
Upload a sample database.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is this a continuous or datasheet form?

If not, you can use the Current event of the form to check the other table:

Sub Form_Current()
  If Nz(DLookup("SomeField", "OtherTable", "Field1=" & YourValue & " AND Field2=" & YourOtherValue),0) <> 0 Then
  YourFormField.Backcolor = vbRed
  End If
End If

Open in new window

OtherTable is the table where you are looking up those color values
SomeField is any field in the table (usually the Primary Key field is simplest)
Field1 and Field2 are the Fields in OtherTable you want to check
YourValue and YourOtherValue are the values you expect to find in Field1 and Field2

Note that if Field1 and Field2 are Text fields, you'd have to do this:
Sub Form_Current()
  If Nz(DLookup("SomeField", "OtherTable", "Field1='" & YourValue & "' AND Field2='" & YourOtherValue & "'"),0) <> 0 Then
  YourFormField.Backcolor = vbRed
  End If
End If

Open in new window

Note I added single quotes around YourValue and YourOtherValue.
0
 
Dale FyeCommented:
Another option might be to join (Left Join) the table that is the RecordSource of your current form with the 2nd table and pull in the "third field" from the 2nd table into the query.

If that "third field" always has a value, then if [third field] is NULL in the query, then you know you don't have a match between the two fields that need to match.  So you could then set up a conditional format something like:

NZ([Third Field], "N") = "Y"

and then set your conditional colors.

The reason I bolded the "might be" in the first line is that joining these two tables might make the overall query uneditable.  If it is uneditable, then you obviously would not be able to update your form, but it would be worth a try.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
doublexAuthor Commented:
This is a form that bring up one account information. There are three sub-forms now.  the main form has the field that I want to put the Conditional format on.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the method I suggest. Are you familiar with VBA coding?
0
 
doublexAuthor Commented:
a little.
I have added the Field from the Table to the form called [promo1] and in the Field (I want to color) conditional format I have the Following attached:

I want it to check all three conditional and then color the Field based on the Condition.

When I have one it was working, now that I have three it stopped?
conditional-format-settings.JPG
0
 
doublexAuthor Commented:
Close this Question, I have resolved my issue.

Since Access 2010 will allow at least 50+ conditional formats in a field.  I Set up a table which is going to be used for multiple purposes, and Used Field Conditional formats as shown in the attached file.
I tested two different conditional types
1. using a value
2. using an expression

I found that both are working fine with the Table I have setup. see example
All three fields are text fields in the table.  The first two fields are the fields in the main tables.  the third field if the Field I used for the conditional format.
conditional-format-settings.JPG
conditional-format-settings-usin.JPG
conditional-format-settings-usin.JPG
conditional-format-table-example.JPG
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It's up to you to close your question, not other participants.
0
 
doublexAuthor Commented:
Solved the Issues example are attached and the Solution
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now