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,
Quote:
Originally Posted by 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.
