Change backcolor on account change

[ClaraBarton]

I have a form of Accounts with a subform of register. The subform is Parent/Child Account number.
I'd like to tie the register background color to the account color. I can't figure out which event to use.
Change of account triggers the Parent/Child connection.
So I tried the Current Event.
Placing a txtbox of the color on the parent form never triggers on change.
I tried including it in the date and account filters but that didn't work.

Private Sub Form_Current()
Dim frm As Form
Set frm = Me.Parent.Register.Form
    [SelectedID] = Me.AccountID
     frm.Detail.BackColor = DetailColor(Me.fColorID)
End Sub

Public Function DetailColor(ID As Long)
Dim bckColor As String
    Select Case ID
        Case 13
            bckColor = &H131313
        Case 14
            bckColor = &H5149E9
        Case 15
            bckColor = &H9E949A
        Case 16
            bckColor = &H7DFB37
        Case 17
            bckColor = &HFAFFF7
        Case 18
            bckColor = &HE583D9
        Case 19
            bckColor = &HF2F244
     End Select
End Function

 

[Gasman]

You haven't set DetailColor to the bckColor ?

 

[ClaraBarton]

Oh you are so right!
Thank you.

 

[ClaraBarton]

Another quick question... is the back color a string? Because that gives me a type mismatch

 

[tvanstiphout]

No, it is an RGB value, long integer.
There are a few defined constants like vbYellow, but otherwise you need to cobble a color together yourself. There is a color selector for the color properties.

 

[ClaraBarton]

i'm using hex...

 

[Gasman]

Add AS String to the first line

 

[The_Doc_Man]

Add AS String to the first line

Actually, if you look at how the function is used, it should be declared "AS LONG" although I believe the default for a function of undeclared type is AS VARIANT and that might work via "LET Coercion" rules.

 

[Gasman]

Actually, if you look at how the function is used, it should be declared "AS LONG" although I believe the default for a function of undeclared type is AS VARIANT and that might work via "LET Coercion" rules.

All I can say is that I added that and it worked.
I did not realise at that time what the type should be. I took it that the O/P knew that.

Though if the Type is Long for the colour, then I agree As Long would be better. In fact it still works with no AS at all.

 

[Gasman]

As an aside, if I wanted to do what you are doing, I would store the colour codes in the accounts table, or somewhere where I could look it up per account.
Then when you add a new account, no need to modify the code.

 

[The_Doc_Man]

I did not realise at that time what the type should be. I took it that the O/P knew that.

Though if the Type is Long for the colour, then I agree As Long would be better. In fact it still works with no AS at all.

It works because of the equal-sign acting as the "assign a value" operator. That enables/triggers "LET-coercion" which is the formal name for what happens when the compiler has to change a data type on-the-fly for data compatibility within an expression. The VBA Language reference does in fact allow a string on the right-hand-side of the value assignment to be converted to a numeric value IF the string is compatible - which in the indicated case, it IS.

 

[ClaraBarton]

Thanks to you all I got it working. However, you cannot use the codes that show on the color picker. Here is the best information:
https://cloford.com/resources/colours/500col.htm

Private Sub Form_Current()
Dim frm As Form
Set frm = Forms![frmAccounts]![Register].Form
    [SelectedID] = Me.AccountID
   
frm.Detail.BackColor = DetailColor(Me.fColorID)
End Sub

Public Function DetailColor(ID As Long) As String
Dim bckColor As String
    Select Case ID
        Case 13  'Black
            bckColor = &H555555
        Case 14   'Blue
            bckColor = 13477484 '&H6CA6CD
        Case 15   'Gray
            bckColor = &HAAAAAA
        Case 16   'Green
            bckColor = 9419919 '&H8B45
        Case 17    'NoColor
            bckColor = &HFFFFFF
        Case 18    'Pink
            bckColor = 11379438 ' &HDDA0DD
        Case 19    'Yellow
            bckColor = 9434879  '&HFFEC8B
     End Select
    DetailColor = bckColor
End Function