More efficinet way to filter form

Question

I have the following code:

Public Function BuildSQL(stQueryName As String, stWhereClause As String) As String
    On Error GoTo Err_BuildSQL

    Dim SQLcmd  As String
    Dim intPos  As Integer
    Dim db      As Database
    Dim qryOrig As QueryDef

    Set db = CurrentDb()
    Set qryOrig = db.QueryDefs(stQueryName)

    SQLcmd = qryOrig.SQL

    intPos = InStr(SQLcmd, "WHERE")
    If intPos > 0 Then
        SQLcmd = Left(SQLcmd, intPos - 1)
    End If

    intPos = InStr(SQLcmd, ";")
    If intPos > 0 Then
        SQLcmd = Left(SQLcmd, intPos - 1)
    End If

    If Not (stWhereClause = "") Then
        SQLcmd = Trim(SQLcmd) & " WHERE " & stWhereClause & ";"
    Else
        SQLcmd = Trim(SQLcmd) & ";"
    End If

    BuildSQL = SQLcmd

Exit_BuildSQL:
    Set qryOrig = Nothing
    Set db = Nothing
    Exit Function

Err_BuildSQL:
    MsgBox Err.Description
    Resume Exit_BuildSQL

End Function

Private Sub SandBox_Click()
    On Error GoTo Err_SandBox_Click

    Dim db         As Database
    Dim rs         As Recordset
    Dim stSQL      As String
    Dim stFrmName  As String
    Dim stQryName  As String
    Dim stSQLWhere As String
    Dim stIDList   As String

    stFrmName = "Libri"
    stQryName = "Libri_All_Query"

    'Define WHERE clause
    stSQLWhere = ""
    If Not (IsNull([Forms]![Libreria]![Editore]) Or [Forms]![Libreria]![Editore] = "") Then
        stSQLWhere = stSQLWhere & "Libri_Editori.Editore = '" & [Forms]![Libreria]![Editore] & "'"
    End If
    If Not (IsNull([Forms]![Libreria]![CognomeAutore]) Or [Forms]![Libreria]![CognomeAutore] = "") Then
        If (stSQLWhere = "") Then
            stSQLWhere = stSQLWhere & "Autori.Cognome = '" & [Forms]![Libreria]![CognomeAutore] & "'"
        Else
            stSQLWhere = stSQLWhere & " AND Autori.Cognome = '" & [Forms]![Libreria]![CognomeAutore] & "'"
        End If
    End If

    'Here several more fields of the search form will be checked and added

    stSQL = BuildSQL(stQryName, stSQLWhere)

    '*** Code in question!
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(stSQL)
    If Not (rs.EOF And rs.BOF) Then
        stIDList = "("
        rs.MoveFirst
        Do Until rs.EOF = True
            If (stIDList = "(") Then
                stIDList = stIDList & rs.Fields(0)
            Else
                stIDList = stIDList & ", " & rs.Fields(0)
            End If
            rs.MoveNext
        Loop
        stIDList = stIDList & ")"
    Else
        Err.Description = "Errore! Recordset vuoto."
        Resume Err_SandBox_Click
    End If
    DoCmd.OpenForm stFrmName, , , , acFormReadOnly
    Access.Forms(stFrmName).RecordSource = "SELECT * FROM Libri WHERE Libri.ID IN " & stIDList
    '**** End code in question

Exit_SandBox_Click:
    Set db = Nothing
    Set rs = Nothing
    Exit Sub

Err_SandBox_Click:
    MsgBox Err.Description
    Resume Exit_SandBox_Click
End Sub

This code works as I want but "looks" slow even with a test DB with only a few records in each table. I believe the time is spent (how can I check if this is true?) in the loop between comments. Is there a more basic, obvious and efficient way to filter the form than creating a recordset and looping through it as I am doing?
The form "Libri" is a big one with several subform to be able to see all the data of a Book.
The query "Libri_All_Query" is a join of almost all tables in the DB and the code shown is executed from a form where I plan to add all possible search fields.

Answer 1

Forms have a filter property:

stWhereClause = "Title Like '" & Me.txtSearch & "*'"
Me.Filter = stWhereClause 
Me.FilterOn = True

The filter should be constructed in a similar way to a WHERE statement. There are some limitations compared with Where. You may wish to check with DCount that records will be returned.

EDIT

If you want a set of records where a subform contains only certain records, you need something on these lines:

SELECT b.Title
FROM Books b 
WHERE b.ID IN (
   SELECT j.BookID FROM BooksAuthorJunction j 
   INNER JOIN  Authors a ON j.AuthorID = a.ID
   WHERE a.Author Like "Arn*")

There are advantages in building more that one form, books as a main form and authors as a subform, then authors as a main form and books as a subform. It is often easier on the user.