Mismatch Error

[BlueJacket]

I want to change the backcolor and fontweight properties of a listbox depending on if it's displaying a record or not, but I'm getting a mismatch error. What should I set my variable as if I'm trying to manipulate these properties?

Private Sub Form_Current()

    Dim stBackColor As String
    Dim stFontWeight As String
    
    stBackColor = "#ED1C24"
    stFontWeight = "Bold"

    If Not Me.lboUpcomingHearings.ListCount = 0 Then
        Me![lboUpcomingHearings].BackColor = stBackColor [COLOR="Blue"]<--- Highlighted[/COLOR]
        Me![lboUpcomingHearings].FontWeight = stFontWeight
    End If
        
End Sub

 

[plog]

I don't think .BackColor accept HTML color codes--looks like it takes a base 10 integer:

https://msdn.microsoft.com/en-us/library/office/ff196119.aspx?f=255&MSPPError=-2147217396

Check out that page, looks like theres a RGB() function you can use to easily convert your values to what .BackColor requires.

 

[The_Doc_Man]

Color codes can be specified in HEX using syntax similar to &Hfffff, but beware that you are looking at the hex codes in little-endian order so use &Hbbggrr as your 8-bit blue, green, and red weights.

As to font weight on controls, use object browser and look up "acBold" or "acFontBold" to see what is the correct value.

 

[MarkK]

To use Hexadecimal in VBA, prefix your number with "&H", so you can print hex numbers in the immediate pane, like...

? &HFF
 255 
? &HABC
 2748

...but the 6 character hex number that Access uses for colors is reversed from VBA. Access uses #RRGGBB, but to specify a color using hex in VBA, you need to do &HBBGGRR, so if you want to set an Access color to #ED1C24, then set it to &H241CED...

Private Sub Form_Current()
   with me.lboUpcomingHearings
      if .ListCount Then
         .BackColor = &H241CED[COLOR="Green"] 'sets it to #ED1C24[/COLOR]
         .FontWeight = "Bold"
      end if
   end with
End Sub

hth

 

[MarkK]

... as the The_Doc_Man was saying, to the minute ...

 

[Galaxiom]

Also FontWeight is numeric.

 

[The_Doc_Man]

Hey, Mark - great minds think alike!

 

[The_Doc_Man]

On further research, it appears that Access doesn't do that the same way that Excel or Word does it. You can't give it "Bold" in quotes because the font weight field is not text. It is an integer value that has up to 9 possible values from "Thin" to "Heavy." The mismatch is because unlike HTML where you could theoretically enter the name of the state you want, you cannot do that in Access and using Object Browser, I could not find any set of weight constants. I think they are like the line width constants - pure numbers that don't translate easily.

What is worse is that I think that the available codes depend on the font itself. I had a form lying around so I called it up in design mode and picked one of the text boxes. When I was using one of the older fonts, MS Sans Serif, I could select any of the nine settings but the display only changed once when it picked any value in the list that was bold or thicker (i.e. the font didn't "condone" more than two values - normal and bold.)

Some fonts have lots of options and will respond differently to the different possible weight values, but it looks like it is going to be font-specific and varying all over the place. BlueJacket, I think you are just going to have to play with this one, but remember it is numeric and the lower numbers are "less bold" weights.

Oddly enough, "italic" is a separate Boolean flag (because you CAN have something that is both bold and italic).