INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Conditional Formatting on a form returns #Error

Conditional Formatting on a form returns #Error

(OP)
I would like to apply conditional formatting to a field on a subform. If I set Myfield ]]

I added the DLookup to add the field to my subform.
=DLookup("T-R-S", "qry_HHH", "Criteria='string'")

I then set my conditional rule

Value = DLookUp("T-R-S", qry_HHH","Criteria = 'string' ")

The Apply button does not work and my "T-R-S" field shows #Error

I am lost. What am I doing wrong?

Thank you.

Rccline





RE: Conditional Formatting on a form returns #Error

(OP)
I changed some field names to distinguish the fields of the two queries. I also changed the conditional formatting to an expression.
Still, no joy.

[qry_HHH].[TRS]
[qry_List].[TRS2]

The subform is based upon a query: [qry_List]

I want the letters in conditional formatting on my form to turn red when [qry_List].[TRS2]=[qry_HHH].[TRS]

The expression in my conditional formatting (which isn't working) is:

[TRS2]=DLookUp("TRS","qry_HBP_HHH","TRS ='" & [TRS2] & "'")

What am I doing wrong?

Thanks

rccine

RE: Conditional Formatting on a form returns #Error

(OP)
To clarify the quotation marks, I rewrite

CODE

[TRS2]=DLookUp("TRS","qry_HBP_HHH","TRS ='" & [TRS2] & "'") 

I no longer get the #Error message but I also do not get the conditional formatting.

RE: Conditional Formatting on a form returns #Error

Try place the DLookup() in the form's recordsource query.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Conditional Formatting on a form returns #Error

(OP)
Thank you Duayne:

My subform pulls an entire query, lets call that qry_1. How do I pull that record source and also add the DLookup from qry_2?

I took the DLookup code directly from another AccessDB that works correctly. But still no joy. Are the single quotes correctly written in the DLookUp code as written above?

My subform is based up a query. I tried to add a left outer join with the second query to add the TRS field; i.e.: All from qry_1 and only those from qry_2 where TRS2 = TRS.
That added the TRS field to the record source, but I still no joy.


Thank you.

rccline

RE: Conditional Formatting on a form returns #Error

Try this in a query:

CODE --> SQL

SELECT qry_1.*, DLookUp("TRS","qry_HBP_HHH","TRS ='" & [TRS2] & "'") AS MyTRS
FROM qry_1 

Do you see the expected values in MyTRS column? This assumes TRS2 and TRS are both string values/fields.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Conditional Formatting on a form returns #Error

(OP)
I see no values in MyTRS field. And yes, both TRS and TRS2 are strings.

Expression in the query for TRS

TRS: [Wh_Twpn] & "-" & [Wh_RngN] & "-" & [Wh_Sec]

Still no joy.

Rccline

RE: Conditional Formatting on a form returns #Error

If you don't see any values in the column what does that tell you?

Apparently there are no matching values.

Just one more test:
Try creating a query with this SQL:

CODE --> SQL

SELECT qry_1.*, qry_HBP_HHH.TRS AS MyTRS
FROM qry_1 LEFT JOIN qry_HBP_HHH on qry_1.TRS2 = qry_HBP_HHH.TRS 

If you still see nothing in the MyTRS column then clearly there are no records where qry_1.TRS2 = qry_HBP_HHH.TRS.

If you get an error, please reply with the error and a copy of the SQL you are using.

Sometimes, I paste records into Excel to view the actual values.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Conditional Formatting on a form returns #Error

(OP)
Holy Smokes Duane!

SUCCESS.

Thank you so much for sticking with me on this Duane; and so timely. Great advice.

Rccline

RE: Conditional Formatting on a form returns #Error

(OP)
Ooops. I have another problem. Once my query has an outer join, the subform based upon thatquery is no longer updateable.

How do I get around this?

Thanks

Rccline

RE: Conditional Formatting on a form returns #Error

I figured the left join wouldn't be updateable. It was just troubleshooting. I'm on my phone so I don't see the complete posts but you might have added un-needed spaces.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Conditional Formatting on a form returns #Error

I'm having trouble understanding your logic. Isn't this looking up a value which will return itself?

CODE --> expression

[TRS2]=DLookUp("TRS","qry_HBP_HHH","TRS ='" & [TRS2] & "'") 

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Conditional Formatting on a form returns #Error

(OP)
The idea is the person enters data. If a particular value is in a different table, then the data that is entered should alert the person entering the data. The newly entered data turns red.

The left outerjoin prevented the query from being updatable.

I got around that by placing a subform from the other table on the main form. It is not the most efficient layout, but at least is it serving the purpose of identifying data which is in another table.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close