Create a select all button

[Michael Walsh]

In a few applications, I've tried "Select All" buttons to
place checkmarks in a whole column. I've done this by
associating an update query with a button. For example...

Private Sub SelectNonePaid_Click()
On Error GoTo Err_ButtonNonePaid_Click
Dim stDocName As String
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblTrainees SET tblTrainees.
[ICTRPaid] = No WHERE (((tblTrainees.Link)=[Forms]!
[frmTrainingEvents]![ID]));"
DoCmd.SetWarnings True
DoCmd.Requery

Exit_ButtonNonePaid_Click:
Exit Sub

Err_ButtonNonePaid_Click:
MsgBox Err.Description
Resume Exit_ButtonNonePaid_Click
End Sub

The problem with this method is the doCmd.requery
statement. It will reflect the select all or select none,
but the requery will reset the whole form back to the
first record of however many there are so if you were
working on the 50th record and you click select all, you'd
be sent back to the first record.

My question is two-fold:
Is there another way to selct all or none without a query?

How do I use repaintObject to repaint the object and will
that work? What is the context if the main form
containing these buttons is called frmTrainingEvents and
the subform containing the checkboxes is called
sfrmTrainees?

 

[Elwin]

Instead of DoCmd.Requery, use Me.Refresh

The refresh method updates the records in the existing
recordset, which won't reset your pointer.

The requery method re-evaluates which records should be in
your recordset, so your old recordset is destroyed,
thereby resetting your pointer.

The only drawback is that if your update query altered
records outside of those displayed in your current
recordset, you won't see them. It's a trade-off.

Good luck.