A
Andrew Kidd
Hi,
I'm using Access XP and have the following code in the class module behind a form (error handling has been removed for clarity:
Private Function m_GetNumberOfRecords(strQueryName As String) As Long
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
m_GetNumberOfRecords = 0
With rs
.ActiveConnection = CurrentProject.Connection
.Open strQueryName, , adOpenDynamic, adLockOptimistic, adCmdStoredProc
If Not .BOF = True And Not .EOF = True Then
.MoveFirst
Do While Not .EOF
m_GetNumberOfRecords = m_GetNumberOfRecords + 1
.MoveNext
Loop
Else
m_GetNumberOfRecords = 0
End If
.Close
End With
' Tidy up.
Set rs = Nothing
End Function
The purpose of the function is to return the number of records for any query, which is passed in as a string value from a combo box selected by the user.
Now, the odd thing that is happening is that when a user uses the normal Access wildcard of an asterisk (e.g. Like "Company*"), although Access shows the correct number of records, the above code returns 0. However, when the user uses the equivalent SQL wildcard (e.g. Like "Company%"), although Access now shows no records, the above function returns the correct number of records.
Can anyone shed any light on this please? Or should I tell my users to use SQL wildcards?
Thanks in advance,
Andy
I'm using Access XP and have the following code in the class module behind a form (error handling has been removed for clarity:
Private Function m_GetNumberOfRecords(strQueryName As String) As Long
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
m_GetNumberOfRecords = 0
With rs
.ActiveConnection = CurrentProject.Connection
.Open strQueryName, , adOpenDynamic, adLockOptimistic, adCmdStoredProc
If Not .BOF = True And Not .EOF = True Then
.MoveFirst
Do While Not .EOF
m_GetNumberOfRecords = m_GetNumberOfRecords + 1
.MoveNext
Loop
Else
m_GetNumberOfRecords = 0
End If
.Close
End With
' Tidy up.
Set rs = Nothing
End Function
The purpose of the function is to return the number of records for any query, which is passed in as a string value from a combo box selected by the user.
Now, the odd thing that is happening is that when a user uses the normal Access wildcard of an asterisk (e.g. Like "Company*"), although Access shows the correct number of records, the above code returns 0. However, when the user uses the equivalent SQL wildcard (e.g. Like "Company%"), although Access now shows no records, the above function returns the correct number of records.
Can anyone shed any light on this please? Or should I tell my users to use SQL wildcards?
Thanks in advance,
Andy