Solved

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

Posted on 2014-01-13
9
1,799 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 30

Expert Comment

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

Expert Comment

by:Dale Fye
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 85
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 85
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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