Showing a checkbox in a listbox or combo box (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,681
I needed a means to quickly view boolean values in a combo or listbox (I didn't want to use a subform). However since these lists are text, checkboxes cannot be shown, instead you get true/false.

1710116811915.png

However with a small modification to the recordsource, you can get checkboxes
1710116865685.png

Better still, with a couple of lines of code you can make the listbox updateable (works for a combo as well, but the dropdown functionality gets in the way). Not so easy to demonstrate with images, please see the attached db
1710117127421.png

the rowsource to the first example is simply
Code:
SELECT PK, fName, fSelected FROM Table1

for the second example we make use of the chrW and iif functions
Code:
SELECT PK, fName, chrw( iif(fSelected,9745,9949)) AS Selected FROM Table1

the same rowsource for the third example, but code in the listbox afterupdate event to manage updates
Code:
Private Sub UDListbox_AfterUpdate()
Const sqlStr = "UPDATE Table1 SET fSelected = Not [fSelected] WHERE PK="

     With UDListbox
  
            CurrentDb.Execute sqlStr & .ItemData(.ListIndex - .ColumnHeads)
            .Requery

    End With
 
End Sub

as an alternate to the checkbox, just use a tick and leave the false's blank
1710119616164.png


Note that for booleans, 0 is false, any other number is true (not just -1). so you might create your boolean with a comparison or using an exists subquery (although these would not be updateable). For example in relation to showing whether an invoice has been created you could use an exists subquery to display a tick or cross rather than displaying the invoice number or date. Or indicate that membership has been paid (or not)

edit: small change to the last listbox to make it updateable
 

Attachments

  • listboxwithcheckbox.accdb
    448 KB · Views: 74
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:15
Joined
May 7, 2009
Messages
19,246
do you have sample of Row Source Type = Value List?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,681
yes

The list rowsource might be (note this example does not have headings)

Code:
List0.RowSource = "name1," & ChrW(9745) & ",name2," & ChrW(9949)
1710148630109.png

and the listbox afterupdate event

Code:
Private Sub List0_AfterUpdate()
Dim s() As String
Dim r As String
Dim i As Integer

     With List0
            
        r = .RowSource
        s = Split(r, ",")
       i = ((.ListIndex - .ColumnHeads) * .ColumnCount) + 1 'modify the 1 to relate to the column being updated
        If AscW(s(i)) = 9949 Then s(i) = ChrW(9745) Else s(i) = ChrW(9949)
        r = ""
        For i = 0 To UBound(s)
            r = r & "," & s(i)
        Next i
        .RowSource = Mid(r, 2)

    End With

End Sub

would need additional code of course to update a variable or property if required - I'm using the latter to control how a treeview form behaves when opening/closing nodes and allows the user to save the current view/settings for next time if required.

Another variation would be to have multiple yes/no columns. You would need to determine which column was being clicked. I would get the X position of the cursor - either using API's or perhaps use the mouse down or up events to populate a form variable

Could use a subform of course - gives you more flexibility for formatting - and for a value list, populate an ADO disconnected recordset instead. But this example is about using a listbox.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:15
Joined
May 7, 2009
Messages
19,246
Microsoft Forms 2.0 Listbox has same "option"
listbox.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:15
Joined
May 7, 2009
Messages
19,246
it is but a re-invention.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,681
perhaps you would like to show the code you used to populate and manage the forms 2 listbox?

Otherwise, if all you are prepared to do is offer snide comments, then don't bother.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:15
Joined
May 21, 2018
Messages
8,608
perhaps you would like to show the code you used to populate and manage the forms 2 listbox?
The MSFORMS listbox has two properties that you have to modify. If doing this in a MSFORMS you can get these properties in the designer. I do not think you can get these in an access form without code. I try to avoid ActiveX whenever possible because of portability issues, but the MSFORMS controls are pretty stable compared to others. This lisbox is also aesthetically nice IMO.

These are:
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti


In the below I added three items, but you can also bind the MSFORMS controls
Code:
Private Sub Form_Load()
  Dim lst As MSForms.ListBox
  Set lst = Me.ListBox7.Object
  With lst
    .ListStyle = fmListStyleOption
    .MultiSelect = fmMultiSelectMulti
    .AddItem "Dog"
    .AddItem "cat"
    .AddItem "Mouse"
  End With
End Sub

Untitled.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:15
Joined
May 21, 2018
Messages
8,608
I thought there was a rowsource property, but I think it is takes an excel range
Also I do not think you can add multi collumns of data in the add item method like in access. So here is how I did your multi column from a table.


Code:
Private Sub Form_Load()
  Dim lst As MSForms.ListBox
  Dim rs As DAO.Recordset
  Dim i As Integer
 
  Set rs = CurrentDb.OpenRecordset("select * from table1 order by Fname")
  Set lst = Me.ListBox7.Object
 
  With lst
    .ListStyle = fmListStyleOption
    .MultiSelect = fmMultiSelectMulti
    .ColumnCount = 2
    .ColumnWidths = "0;3"
    
    'Add first row to serve as column header
    Do While Not rs.EOF
      .AddItem rs!pk
      .List(i, 1) = rs!Fname  ' to add subsequent columns
      rs.MoveNext
      i = i + 1
    Loop
    
  End With
End Sub

the List property adds to another column.
I also could not get the column header to work in a multi column listbox
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,681
also could not get the column header to work in a multi column listbox
number of quetions
Is that the standard listbox?
can you use an alternative to the checkbox?
Can the checkbox be to the right?
Does this also work on the equivalent combo control?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:15
Joined
May 21, 2018
Messages
8,608
Is that the standard listbox?
Depends what you mean. If you mean an Access lisbox then no. It is the standard if working in Excel or any other VBA product.

can you use an alternative to the checkbox?
You can use and option button if you set it to single select, but not in multiselect.

Can the checkbox be to the right?
Not as far as I can tell. I am pretty sure no.

Does this also work on the equivalent combo control?
Sort of. You can add a radio button. But neither that version or your version has any utility as far as I can tell. If you want a useable method see my multiselect combobox.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,681
But neither that version or your version has any utility as far as I can tell
my combo version can show ticks or tickboxes - but not set up to be updateable. This example is a list of payees - the ticks indicate we have their bank details. Sorry for all the redactions - data confidentiality
1710198761597.png
 

isladogs

MVP / VIP
Local time
Today, 18:15
Joined
Jan 14, 2017
Messages
18,258
Very simple but effective. Thanks
Just to point out that the updateable listbox idea can also be applied to the first text only listbox without any changes.
No need to use a checkbox or similar to do this.

Code:
Private Sub StdListBox_AfterUpdate()

Const sqlStr = "UPDATE Table1 SET fSelected = Not [fSelected] WHERE PK="

     With StdListBox        
            CurrentDb.Execute sqlStr & .ItemData(.ListIndex - .ColumnHeads)
            .Requery
    End With

End Sub
 

Users who are viewing this thread

Top Bottom