Multiple Selection (shift-select) Checkboxes

[Rzrbkpk]

I have a table that populates in a subform with the following fields:

Filepath (memo)
File (text)
Select (True/False - Checkbox)

What I'm trying to accomplish is to allow the user to multi-select checkboxes without having to click (or spacebar) on each record. So that if there are 30 records, they could click on record 1 checkbox, hold down shift and click on record 15 checkbox and all checkboxes between would be selected/checked. I've seen this function in other programs, but I'm not sure if it's a Windows API thing or just custom code. Any help would be appreciated.

 

[MajP]

This works, but is a little primitive. If I had time I would write it so it could be used anywhere. It is hardwired to the form, and you would have to write code to make this available to all your forms.

put in a standardmodule

Sub CreateSimpleShortcutMenu()
    Dim cmbShortcutMenu As Office.CommandBar
    'need reference Microsoft Office 1X.0 Object Library
    ' Create a shortcut menu named "SimpleShortcutMenu".
    Dim cmdBar As CommandBar
    Dim newcontrol As CommandBarControl
    For Each cmdBar In Application.CommandBars
      If cmdBar.Name = "SimpleShortcutMenu" Then Exit Sub
    Next cmdBar
    
    Set cmbShortcutMenu = CommandBars.Add("SimpleShortcutMenu", _
                        msoBarPopup, False, False)
                        
    ' Add your custom selection
    cmbShortcutMenu.Controls.Add Type:=msoControlButton, Id:=605

    
    Set newcontrol = cmbShortcutMenu.Controls.Add(Type:=msoControlButton)
    newcontrol.Caption = "Select All"
    newcontrol.OnAction = "SelectAll"
    
    Set newcontrol = cmbShortcutMenu.Controls.Add(Type:=msoControlButton)
    newcontrol.Caption = "UnSelect All"
    newcontrol.OnAction = "UnSelectAll"
    
    Set cmbShortcutMenu = Nothing
End Sub


Public Sub SelectAll()
  Dim rs As DAO.Recordset
  Dim frm As Access.Form
  Dim recordStart As Integer
  Dim recordEnd As Integer
  Dim I As Integer
  Const FormName = "frmProducts"
  Const SelectField = "Selected"
  Set frm = Forms(FormName)
  Set rs = frm.RecordsetClone
  recordStart = frm.SelTop - 1
  recordEnd = recordStart + frm.SelHeight - 1
  rs.MoveFirst
  For I = recordStart To recordEnd
    rs.AbsolutePosition = I
    'MsgBox rs!ProductName
    rs.Edit
    rs.Fields(SelectField) = True
    rs.Update
  Next I
  frm.Requery
  frm.Recordset.AbsolutePosition = recordStart
End Sub
Public Sub UnSelectAll()
    Dim rs As DAO.Recordset
  Dim frm As Access.Form
  Dim recordStart As Integer
  Dim recordEnd As Integer
  Dim I As Integer
  Const FormName = "frmProducts"
  Const SelectField = "Selected"
  Set frm = Forms(FormName)
  Set rs = frm.RecordsetClone
  recordStart = frm.SelTop - 1
  recordEnd = recordStart + frm.SelHeight - 1
  rs.MoveFirst
  For I = recordStart To recordEnd
    rs.AbsolutePosition = I
    'MsgBox rs!ProductName
    rs.Edit
    rs.Fields(SelectField) = False
    rs.Update
  Next I
  frm.Requery
  frm.Recordset.AbsolutePosition = recordStart
End Sub

put in the form

Private Sub Form_Load()
 CreateSimpleShortcutMenu
 Me.ShortcutMenuBar = "SimpleShortcutMenu"
End Sub

Now you can select the records and right click to select/deselect

 

[MajP]

You have to click on the record selector on the right side of the form in order to select multiple records. This would be a lot more complicated to allow the user to click a first and last checkbox.

 

[Rzrbkpk]

Thank you for your response MajP. I modified the necessary items to match my forms/fields, but I'm getting a run-time error '3265' (Item not found in this collection). The piece of code giving the error is:

rs.Fields(SelectFields) = True

I'm not sure what to do with this.

 

[MajP]

Not sure if that is a typo.

The constant is the name of the field that gets selected. Mine is called "Selected" which is a poor name. You need to modify the value of the constant if your field has a diff name.

Const SelectField = "Selected"
....
rs.Fields(SelectField)

in your code you show an "s"
rs.Fields(SelectFields)

which is probably a typo.
The real problem is that you need to use your field name here.
Const SelectField = "Selected"
my guess it is something different.

 

[Rzrbkpk]

D'oh! I accidentally added that "s" when I was modifying the code. It works perfectly now! THANK YOU!!!