Showing converted Hex/RGB color samples in a MS Access form with continuous rows

user_17785303 0
Viewed 87

I am having a single ms access form with two unbound text boxes where I am showing two variants of PANTONE colors which are converted from Hex values to RGB (BGR) values in two public functions. I have about 2 thousand different colors.

The two Hex text boxes I have are named [tpx_hex] and [tcx_hex]. The two unbound text boxes are named [tpx_color] and [tcx_color].

My code (see below) works fine in a single form as shown below, but when I try to do the same in a continuous form then all the unbound textboxes turn black.

I would be grateful for some kind of guidance or a solution to my color issue.

Thanks in advance.

enter image description here

Private Sub Form_Current()
   If IsNull(tpx_hex) Then
         Exit Sub
      Else
        Me.tpx_color.BackColor = Color_tpx_Hex_To_Long([tpx_hex])
   End If
   If IsNull(tcx_hex) Then
         Exit Sub
      Else
         Me.tcx_color.BackColor = Color_tcx_Hex_To_Long([tcx_hex])
   End If

End Sub

Public Function Color_tpx_Hex_To_Long(strColor As String) As Long
    Dim iRed As Integer
    Dim iGreen As Integer
    Dim iBlue As Integer

    strColor = Replace(strColor, "#", "")
    strColor = Right("000000" & strColor, 6)
    iBlue = Val("&H" & Mid(strColor, 1, 2))
    iGreen = Val("&H" & Mid(strColor, 3, 2))
    iRed = Val("&H" & Mid(strColor, 5, 2))

    Color_tpx_Hex_To_Long = RGB(iBlue, iGreen, iRed)
End Function

Public Function Color_tcx_Hex_To_Long(strColor As String) As Long
    Dim iRed As Integer
    Dim iGreen As Integer
    Dim iBlue As Integer

    strColor = Replace(strColor, "#", "")
    strColor = Right("000000" & strColor, 6)
    iBlue = Val("&H" & Mid(strColor, 1, 2))
    iGreen = Val("&H" & Mid(strColor, 3, 2))
    iRed = Val("&H" & Mid(strColor, 5, 2))

    Color_tcx_Hex_To_Long = RGB(iBlue, iGreen, iRed)
End Function
ms-access-2010 converters forms vba function
2 Answers

In a continuos form, all unbound controls will share the same properties.

A workaround is to create a form with a bunch of colour boxes as in my example here:

VBA.ModernTheme

Another could be to create a tiny picture for each form (a few pixels, that you zoom), and then use a picture control. I used that method here:

VBA.PictureUrl

user_35272970

Looks like you have the tough work done already with your custom color functions.

I think you could easily solve this issue by adding the two color fields to your table - say TPX_COLOR and TCX_COLOR - defined as LONG

Then run an update query that sets those fields using your public functions. I just set the null values to #000000 so it'll display a black box even if your Hex color is null

This should work using the field names matching your example

UPDATE [My-Color_Table_Name]
SET TPX_COLOR = Color_tpx_Hex_To_Long(NZ([tpx_hex],"#000000")), 
TCX_COLOR = Color_tcx_Hex_To_Long(NZ([tcx_hex],"#000000"))

Make sure to bind your tpx_color and tcx_color form fields to the new ones in your table.

Then your Form_Current code could change to setting the Foreground and Background colors to the same value - effectively hiding the field's value:

Private Sub Form_Current()
    Me.tpx_color.ForeColor = [TPX_COLOR]
    Me.tpx_color.BackColor = [TPX_COLOR]

    Me.tcx_color.ForeColor = [TCX_COLOR]
    Me.tcx_color.BackColor = [TCX_COLOR]
End Sub
user_24191280
Related