Filtering a form with subforms in Access

Question

In a SQL database I have a table, Table1. This table is related to another table, Table2 which in turn is related to Table3. There is a query Query1 that selects certain records from Table1.

This database is linked to in an Access database project

A form Table1Data is based on Table1, with a datasheet containing related Table2 data (and subsequently Table3 data). This form is opened by another form (Switchboard). The problem comes when the form is opened. I want the form to be filtered, but when I set up a macro and open the form and set the Filter to Query1, the data in the form is not filtered. Why does this happen, is this not the way to do it? Query1 selects all the columns from Table1, so mismatching columns should not be an issue.

Additionally I want to lock it down - only certain people can execute Query1, same with other queries (Query2, Query3 etc). So they can only edit the data that they are permitted to edit.

Answer 1

My preferred solution is to set the recordsource in the Form Open event. This gives me the most control over what is going on.

Here is my boilerplate for doing this. It also includes looking up the OpenArgs which are passed on calling the form. You can just comment out or remove the If/Then statement if you aren't looking to specify anything from the calling form in your SQL.

Private Sub Form_Open(Cancel As Integer)
    ' Comments  :
    ' Parameters: Cancel -
    ' Modified  :
    ' --------------------------------------------------

    On Error GoTo Err_Form_Open

    Dim strSQL As String
    Dim strVariable As String
    Dim strDateVariable As String
    Dim dteDateVariable As String
    Dim i As Integer
    Dim n As Integer

    'Get variables from Left and right of | in OpenArgs
    If Not (IsNull(Me.OpenArgs)) Then

        i = InStr(1, Me.OpenArgs, "|")
        n = Len(Me.OpenArgs)

        strVariable = Left(Me.OpenArgs, n - (n - i + 1))

        strDateVariable = Right(Me.OpenArgs, (n - i))

        dteDateVariable = CDate(strDateVariable)

    Else

        GoTo Exit_Form_Open

    End If

    strSQL = "SELECT   ... " _
           & "FROM     ... " _
           & "WHERE (((Field1)='" & strVariable & "') " _
           & "  AND  ((Field2)=#" & dteDateVariable & "#));"

    Me.RecordSource = strSQL

    Me.Requery

Exit_Form_Open:

    Exit Sub

Err_Form_Open:

    Select Case Err.Number
        Case Else
            Call ErrorLog(Err.Number, Err.Description, "Form_Open", "frmName", Erl)
            GoTo Exit_Form_Open
    End Select

End Sub