CODEWITHSUNDEEP
ms-accessvba
Wayne Seymour
Wayne Seymour

Reputation: 21

How to get records count into a access form's text box

I have a access table that I am doing a search by date range on. In the form I have a text box TxtTotal that I want to display the number of records in the filtered range the code I have. keeps giving me the complete number of records and not the range filtered.

This is my module

Function FindRecordCount(strSQL As String) As Long
     Dim db As Database
     Dim rstRecords As Recordset

'On error GoTo ErrorHandler
            Set db = CurrentDb
            Set rstRecords = db.OpenRecordset("TblPurchases")
    If rstRecords.EOF Then
    FindRecordCount = 0
Else
    rstRecords.MoveLast
    FindRecordCount = rstRecords.RecordCount

End If
   rstRecords.Close
   db.Close
Set rstRecords = Nothing
Set db = Nothing
End Function

This is my code for the TxtTotal text box on the form

   Sub Search()
   Dim strCriteria, task As String

      Me.Refresh
     If IsNull(Me.TxtPurchaseDateFrom) Or IsNull(Me.TxtPurchaseDateTo) 
 Then
   MsgBox "Please enter the date range", vbInformation, "Date Range 
   Required"
    Me.TxtPurchaseDateFrom.SetFocus
  Else
    strCriteria = "([Date of Purchase] >= #" & Me.TxtPurchaseDateFrom & 
   "# and [Date of Purchase] <= #" & Me.TxtPurchaseDateTo & "#)"
    task = "select * from TblPurchases where( " & strCriteria & ") order 
    by [Date of Purchase] "
   DoCmd.ApplyFilter task
    Me.TxtTotal = FindRecordCount(task)
End If

End Sub

the results keeps giving me the complete number of records and not the range filtered.

Upvotes: 1

Views: 2783

Answers (2)

Gustav
Gustav

Reputation: 56026

You could replace all this with a DCount expression in the ControlSource of the textbox txtTotal:

=DCount("*","TblPurchase ","[Date of Purchase] Between #" & Format(Nz(Me!TxtPurchaseDateFrom.Value,Date()), "yyyy\/mm\/dd") & "# And #" & Format(Nz(Me!TxtPurchaseDateTo.Value,Date()), "yyyy\/mm\/dd") & "#")

Upvotes: 0

EliSauder
EliSauder

Reputation: 138

I believe the main issue is this line:

Set rstRecords = db.OpenRecordset("TblPurchases")

You are setting the Record set to use the table as its source instead of your SQL string. No matter what your input dates are, if you are looking at the whole table, it will return the whole table xD.

As for finding the total count of items from a query result, it might make sense to use the SQL COUNT function eg: SELECT COUNT(<Column>) FROM <table> WHERE <criteria>; This will provide you the number of data entries that are provided from that query.

I would also recommend using the QueryDef Object for your SQL definitions since it makes things a little cleaner. But again, this is just a recommendation EG:

Function FindRecordCount(dateFrom As Date, dateTo As Date) As Long
    Dim db As DAO.Database
    Dim QDF As DAO.QueryDef
    Dim rstRecords As DAO.Recordset
    Dim SQL As String

    SQL = "SELECT COUNT(*) FROM TblPurchase WHERE([Date of Purchase] >= #@dateFrom# AND [Date of Purchase] <= #@dateTo#)"

    Set db = CurrentDb
    Set QDF = db.QuerDefs(SQL)
    QDF.Paramaters("@dateFrom").Value = dateFrom
    QDF.Paramaters("@dateTo").Value = dateTo

    Set rstRecords = QDF.OpenRecordset("TblPurchases")

    If rstRecords.EOF Then
        FindRecordCount = 0
    Else
        rstRecords.MoveLast
        FindRecordCount = rstRecords.RecordCount
    End If

    rstRecords.Close
    QDF.Close
    db.Close
    Set rstRecords = Nothing
    Set QDF = Nothing
    Set db = Nothing
End Function

Best Regards.

Upvotes: 0

Related Questions