Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 35
  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  2. #17
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    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?

  3. #18
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Maybe something like this?

    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
    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.

    And it worked!

    Now where and how do I best store the Public Function?

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  5. #20
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    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.

  6. #21
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    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?
    You can have multiple general modules. Just don't name any module with same name as any procedure it contains.
    VBA doesn't require you to specify the module name when calling a Public procedure in a general module.

  7. #22
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Deleted duplicate post

  8. #23
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Now what with Null problem in Dlookup

    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

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  10. #25
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Well F me, you can't put a null into a string in VBA. Okay, I'll go with variant. Aggghhhh!

  11. #26
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    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?

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  13. #28
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    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

  14. #29
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  15. #30
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    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
    ...

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-18-2020, 11:37 AM
  2. Replies: 4
    Last Post: 11-18-2014, 03:12 AM
  3. Alternate Back Color Property
    By RayMilhon in forum Reports
    Replies: 5
    Last Post: 11-15-2012, 06:20 PM
  4. Setting color in SQL query
    By Giewont90 in forum Queries
    Replies: 3
    Last Post: 01-15-2012, 09:15 PM
  5. Replies: 0
    Last Post: 10-24-2008, 11:20 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums