Search code examples
ms-access

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

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":

Table Primary

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

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

New table filter from checkboxs from form

Multiple selection needed:

2 Checkboxes choosen

enter image description here

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

Any ideas?

Solution

  • 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 & ");"