Finding out how many records an SQL returns

[prgwhiting]

I need to find out how many records an SQL query returns. I don't want to have to create a normal query, or a temporary table. I need this to check for duplicates, by checking if a name exists in my name table and a Postcode/zipcode exists in my address table. Please help.
Thanks

 

[tc3of4]

Here's one way... It's in my Global Module that will return the number of records in a recordset (meaning, a table or valid sql statement)

Public Function ReturnRecordCount(aRecordset As String) As Long
On Error GoTo ErrorHandling

Dim dbs As Database
Dim rst As Recordset

Dim count As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(aRecordset, dbOpenSnapshot)
With rst
.MoveLast
count = .RecordCount
End With

Exit_Function:
ReturnRecordCount = count
Exit Function

ErrorHandling:
If (Err.Number <> 3021) Then '/** Error Number 3021: No current record
MsgBox Err.Description, , "Error Number: " & Err.Number & " - GetLastTabPage()"
End If
count = 0
Resume Exit_Function

End Function


------
Hope this helps,
tc3of4

 

[prgwhiting]

Thanks a lot, I've tried it but seem to be getting no response. I think maybe the problem is in my SQL. It's just occurred to me that I'm just using a Select Query as my SQL and wondered if that was maybe the problem. The query is below.

SELECT TBLAPPLICANTS.LASTNAME1, TBLADDRESS.POSTCODE1, TBLADDRESS.POSTCODE2
FROM TBLADDRESS INNER JOIN TBLAPPLICANTS ON TBLADDRESS.ADDRESSID = TBLAPPLICANTS.ADDRESSID1
WHERE (((TBLAPPLICANTS.LASTNAME1) Like [FORMS]![FRMAPPLICANTS]![SEARCHLASTNAME] & ““*”“) AND ((TBLADDRESS.POSTCODE1) Like [FORMS]![FRMAPPLICANTS]![POSTCODE1] & ““*”“) AND ((TBLADDRESS.POSTCODE2) Like [FORMS]![FRMAPPLICANTS]![POSTCODE2] & ““*”“));

The way I'm adding this is by placing it in the aRecordset string

Set aRecordset

Thanks for your help. I really love the way this forum works and peoples attitudes are so great. Why can't the world be like this