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
– HansUp, Commented Apr 12, 2013 at 16:20
Power went out in the office before I could give it a try. I'll test this out Monday. Thank you. — Del
– Del, Commented Apr 12, 2013 at 18:11
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
– Del, Commented Apr 16, 2013 at 15:45

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

2

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)

answered Apr 16, 2013 at 16:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cardmagik · Accepted Answer · 2013-04-12 16:20:13Z

1

I have found using Debug > Compile finds a lot of issues in VBA - it will highlight many errors:

enter image description here

answered Apr 12, 2013 at 16:20

cardmagik

1,69819 silver badges18 bronze badges

Collectives™ on Stack Overflow

Access VBA Form Private Sub calling Public Function in different Module

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related