Access VBA Form Private Sub calling Public Function in different Module

Question

In my database I have a public function in a module that receives a SQL string and returns an open recordset. This function works just fine when called by other public subs, both in this module and in other modules.

However if I try to call the same function using the same exact code I get a compile error: "Wrong number of arguments or invalid property assignment."

If I cut and paste the exact code into a different module as a public sub it works normally. I suspect the issue has to do with naming or perhaps hidden variables. I haven't been able to come up with any solutions by searching unfortunately.

Public Function Query(SQL As String) As ADODB.Recordset
   Dim cnnConn As ADODB.Connection
   Dim rsRecord As New ADODB.Recordset
   Set cnnConn = CurrentProject.Connection
   rsRecord.ActiveConnection = cnnConn
   rsRecord.Open SQL
   Set Query = rsRecord
End Function

Private Sub Setup()
   Dim rs As New ADODB.Recordset
   Dim SQL As String
   SQL = "Select * FROM [Configuration]"
   Set rs = Query(SQL) <--- Compile Error
   rs.Close
End Sub

Any help is much appreciated.

If you rename the function from Query to fnQuery, change its return value to Set fnQuery = rsRecord, and then change Setup() to Set rs = fnQuery(SQL) ... does it work? — HansUp
Power went out in the office before I could give it a try. I'll test this out Monday. Thank you. — Del
That was it. Thank you. I thought I was in the clear with naming, because it was working from another sub. Hopefully that will teach me to follow proper naming syntax. — Del

HansUp HansUp · Accepted Answer · 2013-04-16T16:44:37

My hunch is the problem results from using Query as the name of that custom VBA function.

Rename the function from Query to fnQuery and change its return value to Set fnQuery = rsRecord. Then change the assignment statement in the Setup() procedure to Set rs = fnQuery(SQL)

Access VBA Form Private Sub calling Public Function in different Module

2 Answers