Conditional Formatting on a form returns #Error

[rccline]

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

 

[rccline]

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

 

[rccline]

To clarify the quotation marks, I rewrite

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

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

 

[dhookom]

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

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

 

[rccline]

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

 

[dhookom]

Try this in a query:

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

 

[rccline]

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

 

[dhookom]

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:

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

 

[rccline]

Holy Smokes Duane!

SUCCESS.

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

Rccline

 

[rccline]

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

 

[dhookom]

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

 

[dhookom]

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

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

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

 

[rccline]

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.