Demo of Conditional format in subform - Unlimited colours

[mshmyob]

This demo is a simple way to change the colours in a subform to highlite different conditions.

Access only allows a maximum of 4 conditional changes to individual controls but with this simple trick you can apply thousands of colour changes to any number of controls or even entire rows in a subform set to continuous forms.

Note: This works only in AC2007 I believe. Since I notice more and more AC2007 questions and always see questions about conditional formatting I decided to throw this together for the community.

If you have any questions please feel free to ask.

cheers

[NeoPa]

I've moved this across to the Insights area (as well as noted so I can direct people here when required).

[FishVal]

I don't have Access 2007, but still curious to know what method was applied to achieve the goal. Could you please describe the basics of your approach? Sure, not full how-to-do instructions - just few words about the main idea of the method.

Regards,
Fish.

[mshmyob]

Sure Fish.

Actually was quite easy to accomplish with AC2007.

The tables used in demo consisted of a Vendor Table and Product Table.

Main form for Vendor - subform Products.

DataSource for Subform

SELECT tblVendor.VendorID, tblProduct.ProductID, tblProduct.ProductDesc, tblProduct.ProdPrice, fProdPriceCondition(ProdPrice) AS PriceConditional FROM tblVendor INNER JOIN tblProduct ON tblVendor.VendorID=tblProduct.VendorID WHERE tblProduct.VendorID=Forms![frmColumnConditional].txtVendorID; 
 

Module Code

Public Function fProdPriceCondition(dblPrice As Double)
 
'this function is used to determine the background colour for the product price
' this is an example of how to change the colour of a single control on each row of a subform
' if under $10 1st conditional, 11 to 15 condition 2, 16 to 20 condition 3, 21 to 25 condition 4, 26 to 30 condition 5
 
Dim strDatabasePath As String
 
' get the path where the bitmaps are stored
strDatabasePath = CurrentProject.Path
 
' change background colour of text price control (really changing an image control behind the text price control based on the value in the price field
Select Case dblPrice
    Case Is <= 10
        fProdPriceCondition = strDatabasePath & "\red_price.bmp"
    Case Is <= 15
        fProdPriceCondition = strDatabasePath & "\pink_price.bmp"
    Case Is <= 20
        fProdPriceCondition = strDatabasePath & "\green_price.bmp"
    Case Is <= 25
        fProdPriceCondition = strDatabasePath & "\yellow_price.bmp"
    Case Else
        fProdPriceCondition = strDatabasePath & "\tan_price.bmp"
End Select
 
End Function
 

Simple enough so far - just a query to populate my subform and a function to show a simple condition.

The subform has a image (rectangular bitmap) behind the Price column. The price column is set to transparent background.

In Access2007 a new property for image controls is a Control Source (my control source is set to my function 'PriceConditional' - see query).

As you can see in my module I return the Control Source path to indicate which image to use behind each price.

All images are linked and NOTembedded in the table thereby not increasing the size of the database.

cheers,

@FishVal

[FishVal]

Nice solution. :)
Thank you.

[mshmyob]

Thank you.

cheers,

@FishVal