Conditional Formatting Problem

[smcgrath via AccessMonster.com]

I have a main form in datasheet view with a subform linked by [sortacct]. Is
it possible that when a record exists in the subform to have the [sortacct]
field on the main form show up in bold or another color possibly. I have
searched all over the internet and most people just want a field to show up
highlighted or bold in the subform which is easily done with conditional
formatting. I need a way to view the accounts from the main form and
determine which mortgage folders are signed out without having to click on
the + (subform) for each record.

I tried the following code in the on Activate property of the main form but I
keep getting a runtime error saying "Invalid reference to the property
form/report

If IsNull(Forms![MortgageFolderfrm]![Folderoutsubfrm].Form![ToWhom] Then
Me.SortAcct.BackColor=vbRed
Else
Me.SortAcct.BackColor=vbWhite
End If

Any suggestions?

 

[Allen Browne]

Sounds like you are using a subdatasheet, not a subform.

Use the form's Current event, not Activate, to handle the current record.
But that won't work for all records, since you seem to be in datasheet view.

Instead, use Conditional Formatting (Format menu, in form design.)

You will need to use a DLookup() expression to see if there is a matching
value in the related table. The result is Null if there is no match.

Set the Conditional formatting to Expression, and enter an expression along
these lines:
Not IsNull(DLookup("ID", "Table2", "Table2.[ForeignID] = " &
Nz([MainID],0)))

For help with the expression, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Jacob]

Use The Dlookup() function to search the table/query undelaying
Mortgagefoldersubfrm:

If IsNull(Dlookup("[ToWhom]","YrTableOrQuery","[SortAcct]=" &
Me.SortAcct _
& " AND [someKey]=" & Me.theKey ThatLink the subform)) Then

Regards
Jacob

 

[smcgrath via AccessMonster.com]

Thank you guys so much - it worked like a charm!!!!!

Sounds like you are using a subdatasheet, not a subform.

Use the form's Current event, not Activate, to handle the current record.
But that won't work for all records, since you seem to be in datasheet view.

Instead, use Conditional Formatting (Format menu, in form design.)

You will need to use a DLookup() expression to see if there is a matching
value in the related table. The result is Null if there is no match.

Set the Conditional formatting to Expression, and enter an expression along
these lines:
Not IsNull(DLookup("ID", "Table2", "Table2.[ForeignID] = " &
Nz([MainID],0)))

For help with the expression, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

I have a main form in datasheet view with a subform linked by [sortacct].
Is

[quoted text clipped - 18 lines]

Me.SortAcct.BackColor=vbWhite
End If

 

[Jacob]

My pleasure

Thank you guys so much - it worked like a charm!!!!!

Sounds like you are using a subdatasheet, not a subform.

Use the form's Current event, not Activate, to handle the current record.
But that won't work for all records, since you seem to be in datasheet view.

Instead, use Conditional Formatting (Format menu, in form design.)

You will need to use a DLookup() expression to see if there is a matching
value in the related table. The result is Null if there is no match.

Set the Conditional formatting to Expression, and enter an expression along
these lines:
Not IsNull(DLookup("ID", "Table2", "Table2.[ForeignID] = " &
Nz([MainID],0)))

For help with the expression, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

I have a main form in datasheet view with a subform linked by [sortacct].
Is

[quoted text clipped - 18 lines]

Me.SortAcct.BackColor=vbWhite
End If