Compile error on trying to have function return an object

[darlingm]

I'm using Access 2007. I'm trying to mimick functionality that I often use in C++ to simplify code that interacts with databases, but this is my first time using VBA and Access together. I'm getting an error that I'm not sure how to get around.

I have a Table which I will call MyTable.

MyTable has three colums, which I will call MyTableID, FieldA, and FieldB. Both MyTableID and FieldA are unique (no duplicates in table). FieldA and FieldB are both string types.

When I try compiling the below code, on the emphasized lines, I get the same error: "Compile error: Invalid use of property"

I think it's not liking that I'm trying to return objects from functions and set objects by calling a function.

Can VBA even handle returning an object like I'm trying to do? Am I just writing it wrong for VBA to understand?

I know if I unravel B_GetRecordSet into B_GetFieldB and B_SetFieldB that I'll get around the compile error, but I'm interested in learning VBA's functionality and limits. I'm sure I'll run into more situations where I would like to be able to return objects.

MODULE B

Option Compare Database
Option Explicit

Private Function B_GetRecordSet(FieldA As String) As ADODB.Recordset
   Dim rs As ADODB.RecordSet

   Set rs = New ADODB.RecordSet

   rs.Open "SELECT * FROM MyTable WHERE FieldA=""" & FieldA & """;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

   [B][I][U]B_GetRecordSet = rs[/U][/I][/B]
End Function

Public Function B_GetFieldB(FieldA As String) As String
   Dim rs As ADODB.RecordSet

   [B][I][U]rs = B_GetRecordSet(FieldA)[/U][/I][/B]

   If rs.EOF Then
      B_GetFieldB = ""
   Else
      B_GetFieldB = RecordSet!FieldB
   End If

   rs.Close

   Set rs = Nothing
End Function

Public Sub B_SetFieldB(FieldA As String, FieldB As String)
   Dim rs As ADODB.RecordSet

   [B][I][U]rs = B_GetRecordSet(FieldA)[/U][/I][/B]

   If rs.EOF Then
      rs.AddNew
      rs!FieldA = FieldA
   End If

   rs!FieldB = FieldB

   rs.Update
   rs.Close

   Set rs = Nothing
End Sub

 

[RuralGuy]

I don't know if I would name my variable RecordSet. That just *has* to be a reserved word. Problem names and reserved words in Access

 

[darlingm]

I don't know if I would name my variable RecordSet. That just *has* to be a reserved word. Problem names and reserved words in Access

Very good point. My original code uses a different variable name than "RecordSet", but I shortened and changed the function and variable names to make my post more generic. I'll edit my original post to remove any confusion.

 

[ChrisO]

Properties need to be set not simply equated so: -

Set B_GetRecordSet = rs

should fix the problem.

Hope that helps.

Regards,
Chris.

 

[darlingm]

Properties need to be set not simply equated so: -

Set B_GetRecordSet = rs

should fix the problem.

Hope that helps.

Regards,
Chris.

Ahh, thanks for the help! Works now.