Ah, that is ringing some bells.
I tested a hot pink code #FF69B4 in VBA and it produces a shade of violet. So I tested RGB(255, 105, 180) and I get the hot pink. Now back to the hex and "&HB469FF" gives the hot pink.
Ah, that is ringing some bells.
I tested a hot pink code #FF69B4 in VBA and it produces a shade of violet. So I tested RGB(255, 105, 180) and I get the hot pink. Now back to the hex and "&HB469FF" gives the hot pink.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I knew there was something weird going on. I'm going to remove the # from the stored codes, but I want to keep the same order as the designer to make life easier.
So what has to change in this code if we get rid of the # and have just BA1419 coming in for temp1 in my program?
Also, I want to change the colors only for the current run of the report. I don't want the values from VBA making a permanent change to the report design.
Am I doing this correctly?
Maybe something like this?
I'm not sure where to put the public funcion part if I want it available to all forms and reports in this db or all dbs.Code:Option Compare Database Option Explicit Private Sub Report_Load() 'Change Report header and footer colors to std or custom based on user preference Dim temp1 As String temp1 = DLookup("Pt2", "tbl_Au_cntlLoc", "ID='Col_Template'") If IsNull(temp1) Then temp1 = DLookup("Pt1", "tbl_Au_cntlLoc", "ID='Col_Template'") End If If Not IsNull(temp1) Then ReportHeader.BackColor = fcnColorConvert(temp1) End If 'Place the copyright message into the page footer txtPfNotice = DLookup("Pt1", "tbl_Au_CntlLoc", "ID='AppNotice'") End Sub Public Function fcnColorConvert(ByVal hexColor As String) As String ' changes the string used in form/report designer for color to one useful to VBA Dim Red As String: Dim Green As String: Dim Blue As String 'hexColor = Replace(hexColor, "#", "") Red = Val("&H" & Mid(hexColor, 1, 2)) Green = Val("&H" & Mid(hexColor, 3, 2)) Blue = Val("&H" & Mid(hexColor, 5, 2)) fcnColorConvert = RGB(Red, Green, Blue) 'The output is a decimal value End Function
And it worked!
Now where and how do I best store the Public Function?
Public function goes in a general module, not behind any form or report.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Okay, I'm guessing there can be more than one general module?
How do you tell the report module (?) where to look for the general module?
Sorry, this is my first day in VBA.
You can have multiple general modules. Just don't name any module with same name as any procedure it contains.Okay, I'm guessing there can be more than one general module?
How do you tell the report module (?) where to look for the general module?
VBA doesn't require you to specify the module name when calling a Public procedure in a general module.
Deleted duplicate post
First, let me say a big thanks for all the help today.
I finally bit the bullet and dived into VBA. There goes 6 hours of my life for this simple experiment!
Attached is the db that I created to test.
All the code should be here, and in the correct place.
I've got a lot to learn, I know that.
Anyways, the whole idea was that if a user wanted a different color, I could do it without changing the report design.
A stupid test I know, I had little problem with the copyright message, but the colors!
Now I got stumped again. I went into the tbl_Au_CntlLoc, record "Col_Template" and deleted field Pt2.
This should get rid of the custom color the user wanted and return to the default color code of DFA7A5.
But doing this gets an error in the VBA code, at a place that makes no sense.
Of course Dlookup can return a null!
Or is something else going on? Like Temp1 can't have a null?
The color code is there now (in Pt2), run with it there to see everything works, then delete it and watch what happens.
ReportChangeColor.accdb
Other than Variant, explicitly declared variable types (String, Long, Integer, etc) cannot hold Null so yes, those will error if Null is passed. Only Variant can hold Null. If a variable is not explicitly declared, it will default to Variant.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Well F me, you can't put a null into a string in VBA. Okay, I'll go with variant. Aggghhhh!
Now, without checking each character of temp1 to see if it's between 0-9 and A-F, is there a quick way to test that temp1 contains a valid hex string? Like maybe a conversion that could return an error?
Could just test if not Null or provide alternative value.
with IsNull() function
If Not IsNull(DLookup(...)) Then
or Nz()
x = Nz(DLookup(...), "")
If x <> "" Then
As for handling an error, CLng() will error on normal string. Apparently not on hex code string.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Well, I'm thinking what's read into temp1 must:
1. Be six characters,
2. Each character is between 0-9 or A-F
Any violation returns "" and the properties aren't changed for that value.
It could be handled in the fcnColorConvert I think
Can be &H + more or less than six characters. Test in the Immediate Window.
?CLng("&H1419")
returns 5145
?CLng("&HB1419")
returns 726041
?CLng("&HBA14198")
returns 195117464
Drop the & or the H and you get an error.
But yes, could test for length before concatenation.
If Len(temp1) = 6 Then
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Don't read this post. The dang forum keeps logging me off before I can finish testing an idea.
When I reply, the post gets all messed up, after logging back in.
How can I delete this post?
Alright, with all the good help I got it working as first described in post #1
...