Count Status

[jegesmaci]

I have a database that has records at different statuses along the process, like "created", "submitted to finance", etc.

I have a form where I can choose the status from a dropdown and it will total the number of records at that status and display in an unbound field "Total".

My only problem is that when there are not any records at that status it will still display the previous statuses total. Am I missing something in my code?

Private Sub status_AfterUpdate()

    If Not IsNull(Me![status]) Then
        Call CalcTotal
    Else
        Me![Total] = 0
    End If
                
End Sub

Private Sub CalcTotal()
On Error GoTo Err_CalcTotal

    Dim db As Database
    Dim rs As Recordset
    Dim Q As QueryDef
    
    DoCmd.Hourglass True
    
    Set db = CurrentDb()
    Set Q = db.QueryDefs("qry_total")
    Q.Parameters("[status]") = Me![status]
    Set rs = Q.OpenRecordset()
    rs.MoveFirst
    
    Me![Total] = rs![Status Total]

Exit_CalcTotal:
    DoCmd.Hourglass False
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

Err_CalcTotal:
    Resume Exit_CalcTotal

End Sub

Thank you ahead of time for your help!

Adam

 

[jegesmaci]

Anyone? I would really appreciate the help.

Adam

 

[BryanT]

Hello jegesmaci

I would say that you may need to initialise the value in Me![Status] between calculations.

Bryan

 

[Mile-O]

Why use code when all you need is a DCount domain aggregate function in the ControlSource of your Totals textbox?

 

[jegesmaci]

SJ McAbney,

I don't know how to do this. If you feel that DCount will work better, can you please walk me through what to enter?

Adam

 

[Mile-O]

I was sort of hoping you'd use your initiative and look up DCount in the help files that come with Access and maybe do a search on DCount examples from the forum's archives.

 

[jegesmaci]

Sounds good! I give it a shot. Thanks for the help. Don't want to ruffle any feathers, but wouldn't it make sense to put the answer in the thread so that future idiots can find relief?

Anyway, don't want to be considered as a "non-searcher". I appreciate taking your time. I have found this to be the most extensive source of Access help so thank you very much!

SJ McAbney, based on the code above, is there anything you can see that would make it so the count would refresh between selections and if zero show zero? Thanks again!

Adam

 

[Mile-O]

wouldn't it make sense to put the answer in the thread so that future idiots can find relief?

There's already loads of threads. And let's not call anyone idiots.

The DCount structure is basically,

=DCount("fieldName", "tableName", "criteriaString")

i.e.

=DCount("*", "tblCountries", "Continent = 'Europe'")

If I had a table called countries then this would count all the records where the continent field was Europe.

based on the code above, is there anything you can see that would make it so the count would refresh between selections and if zero show zero?

Me.Controlname.Requery

 

[jegesmaci]

SJ McAbney,

Sorry another dumb question. Where do I put:

Me.Status.Requery

I tried it here and it didn't work:

Private Sub status_AfterUpdate()

    If Not IsNull(Me![status]) Then
        Call CalcTotal
    Else
        Me![Total] = 0
    End If
    
    [B]Me.status.Requery[/B]
        
    Me![runexportquery_button].SetFocus
        
End Sub

I tried it here and it didn't work:

Private Sub CalcTotal()
On Error GoTo Err_CalcTotal

    Dim db As Database
    Dim rs As Recordset
    Dim Q As QueryDef
    
    DoCmd.Hourglass True
    
    Set db = CurrentDb()
    Set Q = db.QueryDefs("qry_total")
    Q.Parameters("[status]") = Me![status]
    Set rs = Q.OpenRecordset()
    rs.MoveFirst
    
    Me![Total] = rs![Status Total]
    
   [B] Me.status.Requery[/B]

Exit_CalcTotal:
    DoCmd.Hourglass False
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

Err_CalcTotal:
    Resume Exit_CalcTotal

End Sub

I appreciate any help.

Thanks,

Adam