Solved

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

Posted on 2014-01-13
9
1,564 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now