CODEWITHSUNDEEP
vbams-access
Fil
Fil

Reputation: 471

How to use a VBA function in a query

I have a Module called GetRole I want to use the returned string variable from the Function in the WHERE clause of the Query. Please see the Query and the Function below. I get undefined function error when I try to run the query.

My question is, what is the correct way of using a function in a query? Thanks and kind regards!

The Query:

SELECT * FROM tblUsers WHERE Role = GetRole();

In the GetRole module:

Option Compare Database
Option Explicit

Public Function GetRole() As String
Dim rs As DAO.Recordset
Dim strUserName As String
Dim strUsers As Object
Set strUsers = CreateObject("WScript.Network")
strUserName = strUsers.UserName
Set rs = CurrentDb.OpenRecordset("SELECT [Role] FROM tblUsers WHERE [UserName] Like '" & strUserName & "'")

GetRole = rs.Fields("Role").Value

Set strUsers = Nothing
rs.Close
Set rs = Nothing

End Function

Upvotes: 1

Views: 175

Answers (1)

Andre
Andre

Reputation: 27634

Don't give modules and functions the same name - the module name will override the function name, hence the undefined function error.

Name the module e.g. mGetRole, and it should work.

Upvotes: 4

Related Questions