Conditional Formatting Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smcgrath - 19 Dec 2006 19:50 GMT

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?

Reply to this Message

Allen Browne - 20 Dec 2006 01:51 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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

Reply to this Message

smcgrath - 20 Dec 2006 13:52 GMT

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

>Sounds like you are using a subdatasheet, not a subform.
>
[quoted text clipped - 21 lines]
>> Me.SortAcct.BackColor=vbWhite
>> End If

Reply to this Message

Jacob - 21 Dec 2006 02:00 GMT

My pleasure

> Thank you guys so much - it worked like a charm!!!!!
>
[quoted text clipped - 23 lines]
> >> Me.SortAcct.BackColor=vbWhite
> >> End If

Reply to this Message

Jacob - 20 Dec 2006 02:17 GMT

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

> 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]
[quoted text clipped - 16 lines]
>
> Any suggestions?

Reply to this Message