Solved

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

Posted on 2014-01-13
9
1,658 Views
Last Modified: 2014-02-05
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
Comment
Question by:doublex
9 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39778660
Upload a sample database.
0
 
LVL 84
ID: 39778926
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39779013
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:doublex
ID: 39779150
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
 
LVL 84
ID: 39779468
You can use the method I suggest. Are you familiar with VBA coding?
0
 

Author Comment

by:doublex
ID: 39780124
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
 

Accepted Solution

by:
doublex earned 0 total points
ID: 39794638
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
 
LVL 84
ID: 39794819
It's up to you to close your question, not other participants.
0
 

Author Closing Comment

by:doublex
ID: 39835102
Solved the Issues example are attached and the Solution
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question