How to use a VBA function in a query

Question

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

Andre · Accepted Answer · 2021-08-21 17:46:50Z

4

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.

answered Aug 21, 2021 at 17:46

Andre

27.7k7 gold badges43 silver badges85 bronze badges

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

1 Comment

Fil Over a year ago

Renaming the module fixed the issue straight away! Thanks

Collectives™ on Stack Overflow

How to use a VBA function in a query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related