If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Conditional Formatting Problem
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 |
#3
|
|||
|
|||
Conditional Formatting Problem
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 |
#4
|
|||
|
|||
Conditional Formatting Problem
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 |
#5
|
|||
|
|||
Conditional Formatting Problem
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 |
Thread Tools | |
Display Modes | |
|
|