Using Conditional formatting with queries on continuous forms

[Steve Antrobus]

Hello all,

I've seen many discussions regarding continuous forms in Access and how to highlight individual rows and they all seem to suggest conditional formatting as the only way.

This is fine, but I am struggling to understand if it will solve my problem.

I have two subforms on a main form that contain many rows of data. What I want to do is highlight in subform 2 those rows that are not present in subform 1.

I am using a query from within VB to determine this and it appears to be working. Once the result is determined any new rows should be highlighted red. Currently all are highlighted red.

Is there a way I can get this to work either using conditional formatting or via some other method?

And also will invoking my code work with on_load function or will this only evaluate the first entry in the subform?

Any help would be greatly appreciated - I've been searching for ages on this!

Thanks,

Steve.

[puppydogbuddy]

Hello all,

I've seen many discussions regarding continuous forms in Access and how to highlight individual rows and they all seem to suggest conditional formatting as the only way.

This is fine, but I am struggling to understand if it will solve my problem.

I have two subforms on a main form that contain many rows of data. What I want to do is highlight in subform 2 those rows that are not present in subform 1.

I am using a query from within VB to determine this and it appears to be working. Once the result is determined any new rows should be highlighted red. Currently all are highlighted red.

Is there a way I can get this to work either using conditional formatting or via some other method?

And also will invoking my code work with on_load function or will this only evaluate the first entry in the subform?

Any help would be greatly appreciated - I've been searching for ages on this!

Thanks,

Steve.

You should be able to do what you want using conditional formatting if you complete the following steps. You did not post your conditional expression. Keep in mind that if this expression is not correctly syntaxed, the conditional formatting won't work.:

1. The control to be conditionally formatted on the continuous form must be bound to a field in the table. If it is unbound, bind it by adding a field to your table for this purpose, or you will continue to show red for all records like you do now..
2. Place the subform in design view
3. Highlight the control that will be conditionally formatted.
4. Go to command menu and select Format>Conditiional Formatting
5. for condition1 first textbox select: Expression is
6 Second texbox enter" Your Conditional expression
7. preview of format textbox: select the red background color that you want formatted control to be when condition is met
8. Click ok button and test.

[Steve Antrobus]

You should be able to do what you want using conditional formatting if you complete the following steps. You did not post your conditional expression. Keep in mind that if this expression is not correctly syntaxed, the conditional formatting won't work.:

1. The control to be conditionally formatted on the continuous form must be bound to a field in the table. If it is unbound, bind it by adding a field to your table for this purpose, or you will continue to show red for all records like you do now..
2. Place the subform in design view
3. Highlight the control that will be conditionally formatted.
4. Go to command menu and select Format>Conditiional Formatting
5. for condition1 first textbox select: Expression is
6 Second texbox enter" Your Conditional expression
7. preview of format textbox: select the red background color that you want formatted control to be when condition is met
8. Click ok button and test.

Thanks for reply!

The above all makes sense to me, but the problem that I am having is that the expression is a query result in VB - can I pull this into the conditional formatting?

I am running the following query:
X = DLookup("[ParentStoreID]", "tblParentStoreAssociation", "[ParentID]= " _
& newParentID & "AND [ParentVersionID]= " _
& newParentVersionID & "AND [StoreID]= " _
& newStoreID)

Which is basically determining if the value in the current subform already exists in the table.

Then I use the following IF

If Not IsNull(X) Then

and based on the outcome of this I was trying to colour the cells. Can I somehow get this into conditional formatting?

[puppydogbuddy]

Thanks for reply!

The above all makes sense to me, but the problem that I am having is that the expression is a query result in VB - can I pull this into the conditional formatting?

I am running the following query:
X = DLookup("[ParentStoreID]", "tblParentStoreAssociation", "[ParentID]= " _
& newParentID & "AND [ParentVersionID]= " _
& newParentVersionID & "AND [StoreID]= " _
& newStoreID)

Which is basically determining if the value in the current subform already exists in the table.

Then I use the following IF

If Not IsNull(X) Then

and based on the outcome of this I was trying to colour the cells. Can I somehow get this into conditional formatting?

Steve,
In your case you would do your conditional formatting using vba as shown. Assuming StoreID is bound, you would just put the following expression in the current event of your subform, but you must go back and Dim X as a global variable, leaving your prior code where it is. With global X, the following expression should work nicely in the subform's current event.

Private Sub YourSubform_Current()
If Not IsNull(X) Then
Me.StoreID.BackColor = RGB(200, 0, 0)
End IF
End Sub