Access Junkie - MVP Alumnus - SDET Access

I am playing with the new Multivalued Lookup Column feature in Access 2007. I am wondering, is there a way to select all values in the lookup column (click all the check boxes) without having to select them all individually?

Yes, you can do this. Below is some sample code first provided by Access MVP Albert Kallal, which I modified.

Private Sub cmdSelectAll_Click()
On Error GoTo ErrorHandler

   Dim varValue As Variant
   Dim varValueList As Variant
   Dim rstAdd As DAO.Recordset
 
   ' Selects all values in the multi-value field combo box

   varValueList = Split(Me.FavoriteColors.RowSource, ";")

   ' Delete any existing selected records
   Set rstAdd = Me.Recordset!FavoriteColors.Value
   Do While rstAdd.EOF = False
      rstAdd.Delete
      rstAdd.MoveNext
   Loop

   ' Select all
   Me.Recordset.Edit
   For Each varValue In varValueList
      rstAdd.AddNew
      rstAdd(0) = Split(varValue, Chr(34))(1)
      rstAdd.Update
   Next
   Me.Refresh
  
ExitPoint:
    Exit Sub
   
ErrorHandler:
    MsgBox "The following error occurred - Error #" & Err.Number & vbNewLine & _
        "Error Description: " & Err.Description, vbExclamation, "Error"
    Resume ExitPoint

End Sub

See more tips and tricks like this in our book: Microsoft Office Access 2007 Inside Out