RecordCount never update when open a form

[GelC]

Hi all,

I've having this problem and spending days to catch the error, never figure out what wrong however.
I have a form to enter data, a form to search, and a form to generate reports.
Everything work just fine until I added a new combo box named cboDips (record source is from a new table).

(I had 3 records before made changes. I tried to enter few more for testing. The total records should be 10 now.)
- The entry form work fine, data is saved into table
- The search form based on criteria is working fine, show all 10 records
- On reports form, I have the unbound text box to show number current records. BUT the current record count never update, it always show 3 records.
Please look at my code to help me to point out what I did wrong here... Thanks!

Private Sub Form_Open(Cancel As Integer)
    updateCurrentRecordCount
End Sub

Public Sub updateCurrentRecordCount()
Me.txtRecordCountStatus = myModule.getRecordCount(getSQL4Report)
End Sub

Function getRecordCount(ByVal strSQL As String) As Integer
On Error GoTo Err_getRecordCount

    Dim intCount As Integer
    Dim myConn As New ADODB.Connection
    Dim myRS As ADODB.Recordset

    'open ADO connection to current database
    Set myConn = CurrentProject.Connection
    Set myRS = New ADODB.Recordset
    Set myRS.ActiveConnection = myConn

    'set and execute SQL string
    strSQL = Replace(strSQL, "*", "%")
    intCount = 0
    
    If (Len(strSQL) > 2048) Then
        MsgBox "Warning: SQL statement exceeds 2048 characters and will present report issues with Access 2000", vbOKOnly + vbExclamation, "Warning!"
    End If
    
    'return zero if strSQL is empty
    If (strSQL = "" Or IsNull(strSQL)) Then
        getRecordCount = intCount
    Else _
        : Set myRS = myConn.Execute(strSQL)
        Do While (Not myRS.EOF)
            intCount = intCount + 1
            myRS.MoveNext
        Loop

        getRecordCount = intCount
        myRS.Close
    End If

    Set myRS = Nothing

Exit_getRecordCount:
    Exit Function
Err_getRecordCount:
    MsgBox "getRecordCount " & Err.Description
    Resume Exit_getRecordCount
End Function

 

[JoeAtWork]

strSQL = Replace(strSQL, "*", "%")

This suggests to me that you have a WHERE clause in your SQL statement. If that is the case, are you sure it is not filtering out 7 of the 10 records?

Please show the contents of your strSQL variable.