How to have unbound checkboxes on an access continuous form

Question

I have investigated the question already and learned that it is not possible to have an unbound checkbox on a continuous form in MS Access.

I'd like to know what the canonical solution is of having unbound objects (i.e. checkboxes for a user to select) on an access continuous form.

Some answers provide a workaround if the Front End is separate for each user. Others suggest putting it in a listbox.

I need to let my users select the record so that I can then continue with that selection. The only possible answer i've seen, seems to be to duplicate the recordset, create a temporary table, and have the form bound to that. This of course increases the file size of a front end access file (until a compact repair is done), plus it is a relatively cumbersome way of solving this issue.

What is the best way of solving this problem?

Answer 1

You can actually use a unbound check box, and tie it to a function.

And you don't have to introduce ADO or a new technology to do this.

http://www.kallal.ca/msaccess/msaccess.html

The approach involves using a collection object, and places a transparent button on top of the check box.

the click code is thus this:

If IsChecked(Me.ContactID) = False Then
  colCheckBox.Add CLng(Me.ContactID), CStr(Me.ContactID)
Else
  colCheckBox.Remove (CStr(Me.ContactID))
End If
Me.Check11.Requery

So you simply drop in a function called IsChecked("the pk column id goes here")

The isChecked function looks like this:

Public Function IsChecked(vID As Variant) As Boolean

  Dim lngID      As Long
  IsChecked = False
  
  On Error GoTo exit1
  lngID = colCheckBox(CStr(vID))
  If lngID <> 0 Then
     IsChecked = True
  End If
  
exit1:
End Function

So we look for the PK in the check box collection. If it not found, then the code errors out. (this means we don't have to write a loop to check the collection).

So the trick here is to use a collection, and use that for the expression of the check box control. And for the "where" clause, I also have a function that returns the list of check PK id list like this:

Private Function MySelected() As String

  Dim i       As Integer
    
  For i = 1 To colCheckBox.Count
     If MySelected <> "" Then
        MySelected = MySelected & ","
     End If
     MySelected = MySelected & colCheckBox(i)
     
  Next i

End Function

So, above is handy, since then I can say launch a report based on the selected items, like this:

Dim strWhere         As String
strWhere = MySelected

If strWhere <> "" Then
  strWhere = "contactID in (" & strWhere & ")"
End If

DoCmd.OpenReport "Contacts1", acViewPreview, , strWhere

So we use that "collection object" of PK id's and return a string separated by ",", and thus we can use the in-clause to launch a report (or a form) using a where clause with "in (our like of PK id)".

I have seen some issues of re-plot problems. It is not a lot of code, and I can't say it much more then say presenting a listbox (multi-column) that could be used in place of a check box.

As noted, the working sample is years old, but I have used this a number of times in production code. So it is a "bit" of code, but not a large amount.

So the check box is un-bound, but the data expression is thus this:

=IsChecked([contactid])

So, you ARE allowed to bind a control to a expression. (a public function in the form). So, the above is the data source of the checkbox. So, this works quite well, and you don't need a extra data column in the table. Now for single user? Just add that extra check box as a column to the table (but that can get messy over time). But if this is multi-user, or even sql server back end, then adding a check box column is often not a option, and thus the above is a 100% un-bound.

Answer 2

You can actually do this using an in-memory ADO recordset. That way, nothing is persisted in the front-end.

Personally, I've used temporary tables for it, creating the table on form open and destroying it on form close, but that comes with performance consequences and scales poorly to many users.

For an in-memory recordset, you can use the following code (added to the form, both load and unload events registered):

Dim r As ADODB.Recordset

Private Sub Form_Load()
    'Init recordset
    Set r = New ADODB.Recordset
    r.Fields.Append "MyCheckbox", adBoolean, attrib:=adFldMayBeNull
    r.CursorType = adOpenKeyset
    r.CursorLocation = adUseClient
    r.LockType = adLockPessimistic
    r.Open
    'Init rows, say 5
    Dim i As Long
    For i = 1 To 5
        r.AddNew
        r.Update
    Next
    'Bind recordset to form
    Set Me.Recordset = r
End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Free recordset
    Set r = Nothing
End Sub

The checkbox here is actually bound, to the MyCheckbox recordset field. But not persisted anywhere, and I guess that was the real goal.

Note that adding and filtering is not available, nor is adding this "unbound" recordset to an existing bound continuous form. Those things all require some additional work.