UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Replace Double Quotes Problem, Access 2016    
 
   
clauscbh
post Aug 12 2019, 08:03 AM
Post#1



Posts: 5
Joined: 12-August 19



Hi there:-)

Would someone be so kind to give me a hint on what is wrong in this small code?

I am trying to replace double qoutes from the field but it keeps saying type mismatch, i am pretty new to access

Dim str1 As String
str1 = Replace(MainColor.Value, """", "")

Me.Detail.BackColor = str1

Thanx in advance

Go to the top of the page
 
DanielPineault
post Aug 12 2019, 08:21 AM
Post#2


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



Which line is giving the error?
What is the value of MainColor.Value?
What is the value of str1?

Also, don't forget BackColor is expecting a Long value and str1 is a string, so you probably need to conversion in there. Perhaps

CODE
Me.Detail.BackColor = CLng(str1)


but without knowing more about the controls, values, setup, it is very hard to guide you.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
clauscbh
post Aug 12 2019, 08:27 AM
Post#3



Posts: 5
Joined: 12-August 19



Thank you so much:-)

The Error line is: Me.Detail.BackColor = str1
The value of the main color is: #1C2938


So what i esentially want is to use the color code:#1C2938 which is stored in the field MainColor but i think the double quotations are giving me problems and that is why i am trying to replace those

Makes sense?
Go to the top of the page
 
DanielPineault
post Aug 12 2019, 08:30 AM
Post#4


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



You can't directly use HEX colors as a property value. They are strings and BackColor expects a Long numerical value. Take a look at http://www.devhut.net/2019/01/12/access-lets-talk-colors/ and be forewarned that HEX colors can be unreliable in Access depending on how they are used!

Previously, I'd would have recommended wrapping the value in a Val(), but this doesn't always work properly and thus should be avoided. Instead, download the utility in the post mentioned above and extract the necessary code to perform the conversion from HEX to OLE and push that value as the BackColor.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
clauscbh
post Aug 12 2019, 08:35 AM
Post#5



Posts: 5
Joined: 12-August 19



Great, thank you very much i will have a look right away:-)
Go to the top of the page
 
DanielPineault
post Aug 12 2019, 08:55 AM
Post#6


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



Here are the 2 functions of interest to you.

CODE
Function HEXtoRGB(HEX As String) As String
On Error GoTo HEXtoRGB_Error

    R = CByte("&H" & Left(HEX, 2))
    G = CByte("&H" & Mid(HEX, 3, 2))
    B = CByte("&H" & Mid(HEX, 5, 2))

HEXtoRGB = R & "," & G & "," & B

ExitFunction:
   Exit Function

HEXtoRGB_Error:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: ColorConvert / HEXtoRGB" & vbCrLf & _
    "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    GoTo ExitFunction
End Function

Function RGBtoOLE(ByVal R As Long, ByVal G As Long, ByVal B As Long) As Long
On Error GoTo RGBtoOLE_Error

    RGBtoOLE = R + (G * 256) + (B * 65536)

ExitFunction:
   Exit Function

RGBtoOLE_Error:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: ColorConvert / RGBtoOLE" & vbCrLf & _
    "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    GoTo ExitFunction
End Function


And then you can do something along the lines of
CODE
Private Sub cmd_ApplyHEX_Click()
    Dim sHEX                  As String
    Dim aRGB                  As Variant
    Dim lOLE                  As Long

    If IsNull(Me.MainColor) = False Then
        sHEX = Me.MainColor
        If Left(sHEX, 1) = "#" Then sHEX = Replace(sHEX, "#", "")
        aRGB = Split(HEXtoRGB(sHEX), ",")
        lOLE = RGBtoOLE(aRGB(0), aRGB(1), aRGB(2))
        Me.Section(acDetail).BackColor = lOLE
    End If
End Sub

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Aug 12 2019, 09:06 AM
Post#7


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



Even better, with these two functions we can easily create a new function to do the conversion directly.
CODE
Public Function HEXtoOLE(ByVal sHEX As String) As Long
    Dim R As Long
    Dim G As Long
    Dim B As Long
    
    On Error GoTo Error_Handler

    If Left(sHEX, 1) = "#" Then sHEX = Replace(sHEX, "#", "")
    
    R = CLng(CByte("&H" & Left(sHEX, 2)))
    G = CLng(CByte("&H" & Mid(sHEX, 3, 2)))
    B = CLng(CByte("&H" & Mid(sHEX, 5, 2)))
    
    HEXtoOLE = R + (G * 256) + (B * 65536)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: HEXtoOLE" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function


and then call it
CODE
Private Sub cmd_ApplyHEX_Click()
    If IsNull(Me.MainColor) = False Then
        Me.Section(acDetail).BackColor = HEXtoOLE(Me.MainColor)
    End If
End Sub

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
ADezii
post Aug 12 2019, 09:37 AM
Post#8



Posts: 2,639
Joined: 4-February 07
From: USA, Florida, Delray Beach


If you like keeping things short & sweet:
CODE
Dim strColor As String

strColor = "#1C2938"
strColor = Replace(strColor, "#", "")

Me.Section(acDetail).BackColor = CLng("&H" & Right$(strColor, 2) & Mid$(strColor, 3, 2) & Left$(strColor, 2))
Go to the top of the page
 
clauscbh
post Aug 12 2019, 09:48 AM
Post#9



Posts: 5
Joined: 12-August 19



Wow what a great effort you did to help me:-D... I'm SO thankful for that sir! I will test it out asap... Buy the i found the soloution with the converting tool you send me to work as OLE, but i will definatly try your new suggestion, couse that see,s much better:-)
Go to the top of the page
 
clauscbh
post Aug 12 2019, 10:15 AM
Post#10



Posts: 5
Joined: 12-August 19



It is working perfectly :-D :-D
Go to the top of the page
 
DanielPineault
post Aug 12 2019, 10:25 AM
Post#11


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



My pleasure. thumbup.gif



uarulez2.gif

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 11:23 AM