Access VBA Subform Dynamic SQL Parent Form Count Slow 1

[misscrf]

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"

The count works, but it is so slow, even slower than the form itself. This means, the form will load and show the records, but be frozen, for about 30 seconds, before the count shows up and the form is usable.

I'm wondering if there is anyway to make this less slow and laggy. The below code is generalized, but enough to give the gist of what the filters code does, to dynamically build the subform's recordsource, based on no filters (onload or reset filters button) or with any number of the filters set.

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.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would try use the where clause in a DCount() like:

  Dim strWhere as String
  strWhere = "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)
     strWhere = strWhere & " AND tbltable5.ID = " & Me.cboFilterTo.Column(0)
  End If
  [COLOR=#4E9A06]' do the same with the other Ifs[/color]
  Debug.Print strWhere    [COLOR=#4E9A06]' take a look![/color]
  Me.txtCountRecords = DCount("*","[Your Table or Query]",strWhere)

I would probably use my faq701-4233 in the Query Forum in place of the DCount().

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[misscrf]

The problem is that the SQL is not 1 table. It's about 6 tables all joined together. I was thinking of making a 2nd sql, that would be the exact same, but instead of selecting the fields, it would be a select count. I'll try that and post back.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

It's about 6 tables all joined together

That's why I suggested the Concatenate() function.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[misscrf]

Well, I did that and it causes the entire form to take an entire minute to load. I have tried other ways, like taking the dynamic SQL statement and doing a recordsset.fields(0) count tally, but that is slow and doesn't seem to get the right count. Still looking for options.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Andrzejek]

It's about 6 tables all joined together" - would a query made out of those 6 tables work faster


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Fields() count will count the number of fields, not records. Can you share your "I did that" code? Are all of the join fields as well as "where" fields indexed?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[misscrf]

I did a few things. I took the default sql and made it into a SQL view, which runs way faster. That turned my filter code to the following:

Private Sub Filters()
    Dim fSQL As String

   [b] fSQL = "SELECT * FROM vw_MyView WHERE 1 = 1 "[/b]

    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

I put the change in bold. I then added the follow to the Dims at the beginning

   Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ccount As Integer

and this after the requery:

Set db = CurrentDb
    Set rs = db.OpenRecordset(fSQL)
    ccount = rs.Fields(0)
    Me.txtCountRecords = ccount & " Records"

Even though on form load, there should be 15,247 records, that fields(0) returns 2,787 records. There are not that many fields even in all the linked tables. so that doesn't make sense that this would return the count of columns.

I also tried this:

ccount = CurrentProject.Connection.Execute(fSQL, dbSeeChanges).Collect(0)
Me.txtCountRecords = ccount & " Records"

and get the same count. When I change that from Collect(0) to RecordCount, I get -1 as the result.

And of course, getting me to write this all out, caused me to find the answer. I did this instead, after I set the subform record source and requeried:

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

Changing the bulk of the SQL to a view made the form faster, and doing this last method for getting the result count was lightning quick.

Thanks for helping me work it out!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Andrzejek]

Shouldn't the last check be:

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

---- Andy

There is a great need for a sarcasm font.