Conditional Formatting Problem

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?

--
Message posted via http://www.accessmonster.com

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

--
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.

"smcgrath via AccessMonster.com" u10749@uwe wrote in message
news:6b0211aa74cd7@uwe...
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

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 wrote:
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?

--
Message posted via http://www.accessmonster.com

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

Allen Browne wrote:
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

--
Message posted via http://www.accessmonster.com

My pleasure

smcgrath via AccessMonster.com wrote:
Thank you guys so much - it worked like a charm!!!!!

Allen Browne wrote:
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

--
Message posted via http://www.accessmonster.com