Continuous forms colours

#1

Lets say I have a table
ID AutoNumber
Colour Long

Sample Data
ID Colour
1 255 (Red)
2 16711680 (Blueish)
3 5284719 (Yucky green)
etc

I want to create a continuous form with the field "Colour" shown in the
correct colour. No problem on a single form, but not so easy on a continuous
form. Is this possible?
Thanks
Phil
Phil

#2

On Nov 11, 1:32*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:

Quote:

Lets say I have a table
ID * *AutoNumber
Colour * Long

Sample Data
ID * * * Colour
1 * * * *255 * * * * * * * * * * (Red)
2 * * * *16711680 * * * * *(Blueish)
3 * * * *5284719 * * * * * *(Yucky green)
etc

I want to create a continuous form with the field "Colour" shown in the
correct colour. No problem on a single form, but not so easy on a continuous
form. Is this possible?
Thanks
Phil
Phil.

Maybe use a program like Irfanview to create some Jpgs or Bmps with
the color. Store then in a lookup table. Link to table you need the
colors from. Or try a hyperlink.

#3

In Access 2010 you are allowed up to 50 conditional formatting. And the
color picker does allow you to create/make any color for the given value.

So, if you have less than 50 colors, then you can do this in a continues
form.

It not clear if you need/want the table, or just setting up the conditional
formatting is enough of a solution here.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

#4

On 13/11/2011 04:06:05, "Albert D. Kallal" wrote:

Quote:

In Access 2010 you are allowed up to 50 conditional formatting. And the
color picker does allow you to create/make any color for the given
value.

So, if you have less than 50 colors, then you can do this in a continues

form.

It not clear if you need/want the table, or just setting up the
conditional
formatting is enough of a solution here.

Thanks, Albert

Will look into this as I only need about a dozen colours.

I was under the impression you were limited to 3 conditional formats. I
presume you need to use code to get over the 3 limit.

Phil

#5

Quote:

"Phil" wrote in message news:j9ol1e$c8d$1 (AT) speranza (DOT) aioe.org...

Thanks, Albert

Will look into this as I only need about a dozen colours.

I was under the impression you were limited to 3 conditional formats. I
presume you need to use code to get over the 3 limit.

Phil

No code is required. The limit been changed from 3 to 50. So this works much
as before except you allowed 50 choices.

As mentioned, the color picker is supported and you can type in the RGB
values for your own custom colors if you want.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

#6

On 14/11/2011 01:55:37, "Albert D. Kallal" wrote:

Quote:

"Phil" wrote in message news:j9ol1e$c8d$1 (AT) speranza (DOT) aioe.org...

Thanks, Albert

Will look into this as I only need about a dozen colours.

I was under the impression you were limited to 3 conditional formats. I
presume you need to use code to get over the 3 limit.

Phil

No code is required. The limit been changed from 3 to 50. So this works
much
as before except you allowed 50 choices.

As mentioned, the color picker is supported and you can type in the RGB
values for your own custom colors if you want.

Thanks again Albert, but I don't quite see how I can set a condition like
ColourCode = 16711680 then format it bluish.

Want I want is to click on the field "Colour", use the colour picker to pick
a colour, then show the field "Colour" in it's correct colour. The RGB colour
is stored in that field. If I don't change the colour, the form should still
display as 12 different colour bands.

Phil

#7

"Phil" wrote in message news:j9rlde$vtl$1 (AT) speranza (DOT) aioe.org...

Quote:

Thanks again Albert, but I don't quite see how I can set a condition like
ColourCode = 16711680 then format it bluish.

You have to use the above column of color code as the conditional format
expression for the column you WANT to format.

(you can't use this value to drive the color for the conditional format)

In your case you have a color code field = 16711680

So lets pretend we going to format the last name column based on above
value:

Note that for conditional format you choose here is gong to be "expression
is" in place of "field value is"
since I assume we not trying to format the color code column.

So if we are to add a color CF for lastName, but based on [ColourCode], we
choose choose "expression is"

eg:

[ColorCode] = 16711680

As noted you will STILL have to bring up the color picker and then enter the
RGB value.
So the CF will not be directly driven off the actual color value, but for
only about 10-11 colors this does not matter.

Also, the color picker does not and cannot accept a full decimal value as
you have, and you have to split it out to a RGB values.

You can use the following code for this:

Dim lngColor As Long

lngColor = 16711680

Debug.Print "red = " & CLng("&h" & (Mid(Hex(lngColor), 6, 2)))
Debug.Print "green = " & CLng("&h" & (Mid(Hex(lngColor), 3, 2)))
Debug.Print "blue = " & CLng("&h" & (Left(Hex(lngColor), 2)))

So the above would give us our RGB values that you can type into the color
picker.

The above gives:

red = 0
green = 0
blue = 255

And the reverse is to use RGB funciton
eg:
debug.print RGB(0,0,255)
gives 16711680

So you not use the above code in the CF, but the above code is only to
"convert" for use with the RGB values that you will MANAUALLY enter into the
CF

As noted, if this was gong to be 25 colors or some such, we might try to
write some code that generates or adds or even at runtime sets the CF
values, but I don't think it is worth the time for just 10 values. Convert
your full decimal color vales to RGB, and then simply add the 10 values to
the CF and you are done.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

#8

On 15/11/2011 06:07:12, "Albert D. Kallal" wrote:

Quote:

"Phil" wrote in message news:j9rlde$vtl$1 (AT) speranza (DOT) aioe.org...

Thanks again Albert, but I don't quite see how I can set a condition
like
ColourCode = 16711680 then format it bluish.

You have to use the above column of color code as the conditional format

expression for the column you WANT to format.

(you can't use this value to drive the color for the conditional format)

In your case you have a color code field = 16711680

So lets pretend we going to format the last name column based on above
value:

Note that for conditional format you choose here is gong to be
"expression
is" in place of "field value is"
since I assume we not trying to format the color code column.

So if we are to add a color CF for lastName, but based on [ColourCode],
we
choose choose "expression is"

eg:

[ColorCode] = 16711680

As noted you will STILL have to bring up the color picker and then enter
the
RGB value.
So the CF will not be directly driven off the actual color value, but
for
only about 10-11 colors this does not matter.

Also, the color picker does not and cannot accept a full decimal value
as
you have, and you have to split it out to a RGB values.

You can use the following code for this:

Dim lngColor As Long

lngColor = 16711680

Debug.Print "red = " & CLng("&h" & (Mid(Hex(lngColor), 6, 2)))
Debug.Print "green = " & CLng("&h" & (Mid(Hex(lngColor), 3, 2)))
Debug.Print "blue = " & CLng("&h" & (Left(Hex(lngColor), 2)))

So the above would give us our RGB values that you can type into the
color
picker.

The above gives:

red = 0
green = 0
blue = 255

And the reverse is to use RGB funciton
eg:
debug.print RGB(0,0,255)
gives 16711680

So you not use the above code in the CF, but the above code is only to
"convert" for use with the RGB values that you will MANAUALLY enter into
the
CF

As noted, if this was gong to be 25 colors or some such, we might try to

write some code that generates or adds or even at runtime sets the CF
values, but I don't think it is worth the time for just 10 values.
Convert
your full decimal color vales to RGB, and then simply add the 10 values
to
the CF and you are done.

Thanks Albert

The following uses the colour picker to colour a field called "AreaColour"
with the correct colour on a single form.
There is a command button called "ColourPicker." which opens the colour
picker dialog box.

Public Type HSL
Hue As Integer
Saturation As Integer
Luminance As Integer
End Type

Public Type RGB
Red As Integer
Green As Integer
Blue As Integer
End Type

Private Type COLORSTRUC
lStructSize As Long
hwnd As Long
hInstance As Long
rgbResult As Long
lpCustColors As String
Flags As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Const CC_RGBINIT = &H1
Private Const CC_SOLIDCOLOR = &H80

Private vate Declare Function CHOOSECOLOR Lib "comdlg32.dll" Alias
"ChooseColorA" _ (pChoosecolor As COLORSTRUC) As Long

Public Function aDialogColor(Prop As Property) As Boolean

Dim X As Long, CS As COLORSTRUC

CS.lStructSize = Len(CS)
CS.hwnd = hWndAccessApp
CS.rgbResult = Nz(Prop.Value, 0)
CS.Flags = CC_SOLIDCOLOR Or CC_RGBINIT
CS.lpCustColors = String$(16 * 4, 0)
X = CHOOSECOLOR(CS)
If X = 0 Then
' ERROR - use Default White
Prop = RGB(255, 255, 255) ' White
aDialogColor = False
Exit Function
Else
' Normal processing
Prop = CS.rgbResult
'FntBackColour = CS.rgbResult
End If

aDialogColor = True

End Function

Private Sub ColourPicker_Click()

' Open the colour chooser dialog box

Call aDialogColor(AreaColour.Properties("BackColor"))
AreaColour = AreaColour.Properties("BackColor")

End Sub

What I want is to show this single form as a continuous form with the
AreaColour field shown in the correct colour.

This colour is subsequently used for publishing different sections of
advertising material, and may well be changed on a frequent basis, so it has
to be done by a user rather than a programmer. Equally it is nice to be able
to see all the colours at once to see how they blend.

So using the conditional format box to set the "expression is" [ColorCode] =
16711680 is "not on" as that would mean being in design view on a form, and I
like to distribute .Accde databases.

At the moment I am getting round it by openeing & closing a continuous report
showing the correct colours ... but it is messy.

Phil

#9

Quote:

What I want is to show this single form as a continuous form with the
AreaColour field shown in the correct colour.

This colour is subsequently used for publishing different sections of
advertising material, and may well be changed on a frequent basis, so it
has to be done by a user rather than a programmer. Equally it is nice to
be able to see all the colours at once to see how they blend.

Thanks guys for all your advice.

There is an Access bug that seems to stop the FormatConditions.Add working if
there are less than 4 formats established (the old limit was 3)

This seems to work

Sub SetFormatCond()

Dim FormatCond As FormatCondition
Dim i As Long

On Error GoTo SetFormatCond_Err

'Remove existing format conditions but due to an Access bug, if you have
' less than 4 formats, the FormatConditions.Add wont work.
' There is a limit of 3 formats otherwise
Do Until Me!AreaColour.FormatConditions.Count = 4
Me!AreaColour.FormatConditions(1).Delete
Loop
' Add the bew formats, starting at number 4
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Set FormatCond = Me!AreaColour.FormatConditions.Add _
(acExpression, (acExpression, , "[AreaColour] = " &
Me.RecordsetClone!AreaColour) .MoveNext
Loop
.Close
End With
Debug.Print Me!AreaColour.FormatConditions.Count

i = 4 ' Ignore the first 4 formats
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Me!AreaColour.FormatConditions(i).BackColor i).BackColor =
Me.RecordsetClone!AreaColour i = i + 1
.MoveNext
Loop
.Close
End With

Exit Sub
SetFormatCond_Err:
MsgBox "Error Number: " & Err.Number & " " & Err.Description

End Sub

Phil

#10

Ok so as noted my suggestion was based on if the colors can be hard
coded and not really be charged by end users.

You digging up code to add CF seems like a good idea - I not aware of
some vba 3 limit bug but you have to use the newest file formats and
not a mdb but acceb

Albert k.