MS Access Table filtered by checkboxes from a form, creating a new table-CodePudding

I want to create a new table "TblMany", filtering values from other data table "TblControl".

Filtering with multiple checkboxes of a Form "FrmMany".

Table with data "TblControl":

Form with checkboxes, representing all values available in "Box?" columns from data table:

After pressing the button, it should create a new table (or recreate one existing) that shows rows with numbers selected in the "FrmMany"

Multiple selection needed:

I have done some tests with "iif" or "where" but i think it's only possible via VBA.

Any ideas?

CodePudding user response：

You are correct that you will need to use VBA to do this. Firstly, you will need to loop the checkboxes to see if they are to be used in the selection of data. Once this has been done, you need to build a SQL string that inserts the data into the existing table. Something like this seems to work:

Private Sub cmdProcess_Click()
    On Error GoTo E_Handle
    Dim intLoop1 As Integer
    Dim strSQL As String
    For intLoop1 = 1 To 8
        If Me("chk" & intLoop1) = True Then strSQL = strSQL & intLoop1 & ","
    Next intLoop1
    If Len(strSQL) > 0 Then
        If Right(strSQL, 1) = "," Then strSQL = Left(strSQL, Len(strSQL) - 1)
        CurrentDb.Execute "DELETE * FROM tblMany;"
        CurrentDb.Execute "INSERT INTO tblMany " _
            & " SELECT * FROM tblControl " _
            & " WHERE Box1 IN(" & strSQL & ") " _
            & " OR Box2 IN(" & strSQL & ") " _
            & " OR Box3 IN(" & strSQL & ");"
    End If
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "cmdProcess_Click", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

I am assuming that tblMany is an exact copy of of tblControl apart from field ID being an Autonumber in tblControl and just numeric in tblMany.

Rather than repeatedly deleting and inserting data (which will lead to bloat), you may find it better to use a query and modify its SQL as needed:

CurrentDb.QueryDefs("qryMany").SQL="SELECT * FROM tblControl " _
    & " WHERE Box1 IN(" & strSQL & ") " _
    & " OR Box2 IN(" & strSQL & ") " _
    & " OR Box3 IN(" & strSQL & ");"