MS Access SQL: Left Join for Selection List with Checkboxes-CodePudding

I'm trying to follow the example provided here(

so, the SelChk function is passed the PK of the given row.

Now, to check/un-check, we drop a button (invisible) on TOP of the check box.

like this:

Don't forget to bring to front this button - we MAKE SURE it is on top of the check box.

hence this:

So, put all above together, and we have this:

Of course we now want a final button that takes the selection, and we can run a report, or even process the records. The code can be this:

Dim strWhere      As String
Dim v             As Variant

For Each v In CheckItems
  If strWhere <> "" Then strWhere = strWhere & ","
  strWhere = strWhere & v
Next

' open a report based on selection

DoCmd.OpenReport "rptHotelsInvoice", acViewPreview, , "ID IN (" & strWhere & ")"

Or, we could process the records in code, say like this:

  Dim strWhere      As String
  Dim v             As Variant
  
  For Each v In CheckItems
     If strWhere <> "" Then strWhere = strWhere & ","
     strWhere = strWhere & v
  Next
  
  Dim strSQL As String
  
  strSQL = "SELECT * from tblHotels where ID IN (" & strWhere & ")"
  Debug.Print strSQL
  
  Dim rst     As DAO.Recordset
  Set rst = CurrentDb.OpenRecordset(strSQL)

  Do While rst.EOF = False
  
      ' process reocrds
      rst.Edit
      rst("FirstName") = rst("FirstName") & "z"
      
      rst.Edit
      rst("FirstName") = rst("FirstName") & "z"
      
      rst.Update
      rst.MoveNext
  Loop
  rst.Close

Now, we assumed a "ID" for PK, but if your pk id is different, then change this:

MySql([ID])  to whatever your PK row ID is. the rest of the code should work as is.

And the final bit of code above that creates the were clause, again would be changed.

so, all in all?

You really don't need a temp table, and using the above custom collection and a function to drive the check box means you don't need a extra table.

The beauty of above?

You can use this against say a linked Excel sheet, SharePoint tables, SQL server tables - it don't matter, since we not messing or dealing with a extra table.