Method is too slow...Dlookups Dcounts etc 1

[eatwork]

Hi, I was wondering if anyone could help with the following method. It seems very slow, and I am calling it from the timer. It has to open some access window to process the function and I wouldn't think a dlookup or dcount would take so long.

Public Sub updateStatuses()
    Me.lbl_StorageLastReviewedOn.Caption = ""
    
    'Dim notInvoicedCount As Integer
    'notInvoicedCount = DCount("jobId", "qryNOTINVOICEDPARENT", "")
    'Dim storageLastReviewedOn As Date
    'storageLastReviewedOn = Format(DLookup("value", "tbl_CONSTANT", "constantType='Storage Reviewed Last On'"), "mm/dd/yyyy")
    Dim storageItemsCount As Integer
    storageItemsCount = DCount("job", "qryStorageCommodities", "Status='Storage'")

    
    'If notInvoicedCount > 0 Then
    '    Me.lbl_StorageLastReviewedOn.Caption = Me.lbl_StorageLastReviewedOn.Caption & "Jobs/Loads Require Billing: " & notInvoicedCount
    'End If
    'If DateDiff("d", Format(storageLastReviewedOn, "yyyy/mm/dd"), Format(Now(), "yyyy/mm/dd")) >= 14 Then
    '    Me.lbl_StorageLastReviewedOn.Caption = Me.lbl_StorageLastReviewedOn.Caption & _
    '                                          IIf(Nz(Me.lbl_StorageLastReviewedOn.Caption, "") = "", "", "      ") & _
    '                                          "Items In Storage: " & storageItemsCount & "      " & _
    '                                          "Storage: Last Reviewed On " & Format(storageLastReviewedOn, "mm/dd/yyyy")
    'End If
    If Me.lbl_StorageLastReviewedOn.Caption = "" Then
        Me.lbl_StorageLastReviewedOn.Visible = False
    Else
        Me.lbl_StorageLastReviewedOn.Visible = True
    End If
End Sub

Is this efficient or can it be modified to be faster? thank you

 

[mp9]

DCount is always going to be slow. However, you can speed it up by using DCount("*"... Also, you can simplify/replace your if/then/else at the end. Here's a fractionally faster version of your code. For simplicity I've removed all your commented lines.

Public Sub updateStatuses()
    Me.lbl_StorageLastReviewedOn.Caption = ""
    
    Dim storageItemsCount As Integer
    storageItemsCount = DCount("*", "qryStorageCommodities", "Status='Storage'")

    Me.lbl_StorageLastReviewedOn.Visible = (Me.lbl_StorageLastReviewedOn.Caption <> "")
End Sub

 

[eatwork]

Hi mp9,
thanks for the post, tried the code, not sure if it made it a lot faster, but I've changed my code. Thanks for the help

 

[sfatz]

Could it be that the slowdown is in the query 'qryStorageCommodities" and not the code?

Just a thought.

 

[mgmslate]

Have you considered creating a recordset using your criteria and have storageItemsCount = RecordCount.

Mike

 

[eatwork]

Hello sfatz, mgmslate, and all others,
Thank you for your posts.
Not sure if the slowdown was the query itself or not, but I decided against the automatic updating of the label, would've been nice but it could hold up a user while processing data so I decided against it.

mgmslate,
I'm not exactly sure what u might mean by recordset. If you mean create a query that returns a record count based on the query itself, then yes I tried that, but it didn't seem to make a difference. My queries, as sfatz stated, might be too complicated to populate the items within a reasonable time I guess. Thanks for your help