Conditional Formatting Problem

  • Thread starter Thread starter smcgrath via AccessMonster.com
  • Start date Start date
S

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?
 
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
 
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
 
Thank you guys so much - it worked like a charm!!!!!

Allen said:
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
Click to expand...
[quoted text clipped - 18 lines]
Me.SortAcct.BackColor=vbWhite
End If
Click to expand...
Click to expand...
 
My pleasure
Thank you guys so much - it worked like a charm!!!!!

Allen said:
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
Click to expand...
[quoted text clipped - 18 lines]
Me.SortAcct.BackColor=vbWhite
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top