Solved

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

Posted on 2014-01-13
9
1,632 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

806 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