Access VBA Subform Dynamic SQL Parent Form Count Slow

Question

I have a filters private function on a form, to dynamically generate the SQL for the record source on a subform, for searching records. I run this onload of the main form, and afterupdate of a series of text box (dates), comobo box, and check box controls. Below is the Filters code. When the form loads, there are 15,247 records. When I load the form, it is frozen for a while, getting the count.

The subform is datasheet, and I wanted the counts bigger than the navigation buttons record count, so I put a textbox on the form footer of the main form. To display the count of the subform records on the main form, I put a textbox in the form footer of the subform, and it's control source =count(*)

On the main form footer textbox, the controlsource is
=[frmsubform].[Form]![txtCountrecs] & " Records"

It works, but it is so slow. I'm wondering if there is anyway to make this less slow and laggy.

Private Sub Filters()
    Dim fSQL As String

    fSQL = "SELECT [fields] " & _
            "FROM tbltable1 LEFT JOIN tbltable2 ON tbltable1.ID = tbltable2.FKtbl1ID " & _
            "WHERE ((tbltable3.ID) Is Not Null) AND ((tbltable4.ID) Is Not Null)) "

    If Nz(Me.cboFilterTo.Value, 0) <> 0 Then
        fSQL = fSQL & " AND tbltable5.ID = " & Me.cboFilterTo.Column(0)
    End If

    If Nz(Me.cboFilterFrom.Value, 0) <> 0 Then
        fSQL = fSQL & " AND tbltable6.ID = " & Me.cboFilterFrom.Column(0)
    End If

    If Nz(Me.txtDateOnOrAfter, 0) <> 0 Then
        fSQL = fSQL & " AND tbltable4.dtdate >= " & Me.txtDateOnOrAfter
    End If

    If Nz(Me.txtDateOnOrBefore, 0) <> 0 Then
        fSQL = fSQL & " AND tbltable4.dtdate <= " & Me.txtDateOnOrBefore
    End If

    If Nz(Me.chkUncompleted, 0) <> 0 Then
        fSQL = fSQL & " AND nz(tbltable3.dtdatedone,0) = 0"
    End If

'Debug.Print fSQL
    Me.frmSubform.Form.RecordSource = fSQL
    Me.frmSubform.Form.Requery
End Sub

If there is any way to make this faster, I'd love some suggestions.

missscripty missscripty · Accepted Answer · 2019-10-16T18:52:36

I found the answer, so I wanted to post the solution for anyone it may help.

To make the form load faster, I converted the initial fSQL select, into a SQL view, and then made that initial fSQL be this:

fSQL = "SELECT * FROM vw_MyNewView WHERE 1 = 1 "

Then, after I set the subform's recordsource and requery it, I have the following:

With Me.frmSubform.Form.RecordsetClone
    If .RecordCount > 0 Then .MoveLast
    ccount = .RecordCount
End With
Me.txtCountRecords = ccount & " Records"

The form loads way quicker, and this count tabulates quickly too.

Hope this helps others!

Access VBA Subform Dynamic SQL Parent Form Count Slow

1 Answers