Compile error after a function is returning a recordset

Question

I have a rather simple issue I suppose as I'm just learning Access, but couldn't find any solutions from the forum. In my code there is a function that is returning a recordset and it does that just fine, but when I set a pointer to that recordset it is not pointing. The relevant code in my file is as following:

Private Sub Form_Open(Cancel As Integer)

EntryId = Forms.Item("fCustlist").Recordset.Fields("Custlist_Entry_Id")
setCustomersRecordset
setFormElements

End Sub

Private Sub setCustomersRecordset()
Dim rsCustomersDb As ADODB.Recordset
Dim i As Integer

Set rsCustomersDb = getRelatedCustomers(EntryId)

For i = 1 To rsCustomersDb.RecordCount '<--COMPILE ERROR: Type mismatch
'.....
Next i

End Sub

Public Function getRelatedCustomers(EntryId As Long) As ADODB.Recordset
Dim sql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

sql = "SELECT blah blah blah"

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, cn, , adLockReadOnly

Set getRelatedCustomers = rs
End Function

I am using getRelatedCustomers to return a recordset, and I can do

Debug.Print getRelatedCustomers(entryId).RecordCount

which does return the correct number, yet after setting RsCustomersDb = getRelatedCustomers(entryId) it does not compile. What am I missing here?

Answer 1

The ADODB recordcount property is a long. You should use Dim i As Long, not Dim i As Integer.

Generally, you should not use an integer in VBA, unless you really need an integer for legacy API's. A long can store more, is faster, and of equal size in memory. Read more here.