Individually formatting list box items?

[snoopy75]

I have a callback function to fill a list box that goes like this:

Function FillList(ctl As Control, varID As Variant, lngRow As Long, lngCol As Long, intCode As Integer) As Variant
On Error GoTo Err_FillList

    Dim varRetval As Variant
    Static db As DAO.Database
    Static rstData As DAO.Recordset
    
    Select Case intCode
        Case acLBInitialize
            Set db = CurrentDb()
            Set rstData = db.OpenRecordset("SELECT [tbl Users].Name, [tbl Users].Online FROM [tbl Users];")
            varRetval = True
            
        Case acLBOpen
            varRetval = Timer
            
        Case acLBGetRowCount
            rstData.MoveLast
            varRetval = rstData.RecordCount + 1
            
        Case acLBGetColumnCount
            varRetval = 2
            
        Case acLBGetColumnWidth
            varRetval = -1
        
        Case acLBGetValue
            Select Case lngRow
                Case 0
                    Select Case lngCol
                        Case 0
                            varRetval = "Name"
                        Case 1
                            varRetval = "Online?"
                    End Select
                Case Else
                    rstData.MoveFirst
                    rstData.Move lngRow - 1
                    Select Case lngCol
                        Case 0
                            varRetval = rstData(lngCol)
                        Case 1
                            varRetval = Switch(rstData(lngCol) = -1, "Yes", rstData(lngCol) = 0, "No")
                    End Select
            End Select
            
        Case acLBGetFormat
            varRetval = -1
            
        Case acLBEnd
            Set rstData = Nothing
            Set db = Nothing
            
    End Select
    FillList = varRetval

Exit_FillList:
    Exit Function
    
Err_FillList:
    MsgBox err.Number & ": " & err.Description
    Resume Exit_FillList
    
End Function

This is basically a list of database users, and whether or not they are currently logged on to the database. What I want to do is, if the user is logged on, turn that list item green, and if they are not logged on, turn it red. So here's what I tried to do:

...

        Case acLBGetFormat
            If rstData(1) = -1 then
                retval = "[green]"
            Else
                varRetval = "[red]"
            End If

...

This not only doesn't work, it messes up the rest of the data in the list box. What am I doing wrong? Is this kind of thing even possible?

--Ryan

 

[rewdee]

Hi Ryan

What error do you get? What version of Access are you using? As far as I know Access 97 will not let you format individual items only the entire listbox. I believe the same goes for datasheets. You might approach the task with a continuous subform that way you can change the fontColor property.

Hope this helps,
Rewdee

P.s. I've seen a similiar callback function but never used it. Pardon my ignorance, but why do you moveFirst and then move row minus one in the acLBGetValue case when the row is not zero.

 

[snoopy75]

Hi Rewdee!

I don't get any error, it just doesn't work. I am using Access 97, and I know that this kind of thing works because I've done it before, just not with colors. And I didn't think you could do it at all with continuous forms. Does it really work?

About your question, acLBGetValue supplies Access with the value in the listbox at a specified row and column position. On the first row (row 0), I supply my own values in order to generate a header row. Therefore, on every other row, I have to subtract one from the Move function to make up for that extra row.

 

[rewdee]

Okay Ryan you got me on the continuous form. I was thinking of something like in a report having the OnFormat or OnDetail event.

I did do the some reading in the Access Developer's Handbook and although it did not explicitly say you cannot format the color, I think the formatting is reference to the value that is being painted into the listbox. For example, a date could be formatted as "mmm dd yyyy" format.

I know it is not as appealing, but for simplistic sake, you may create a subform with a boolean yes/no checkbox. The checkbox makes for visual confirmation to whose logged on.

Hope this helps,
Rewdee

 

[BoxHead]

Ryan,

Pretty sure that you can only set a property for a control, not for individual values within the control. If the forecolor of the listbox is green, it's green.

If you're looking for a way to make the visual cue more intuitive than the yes/no field, I've seen listboxes set up with two columns where the first column shows some items (people not online) and the second column shows the others (those online). I think this could be done in your original query statement with a couple IIf statements.

Offline Online

User1
           User2
           User3
User4
User5

Just a thought.


John

Use what you have,
Learn what you can,
Create what you need.