Use custom functions in the Expression Builder

Question

I added a function to get the Active Directory user login, for an Access DB using VBA, but I'm not sure why I don't see my function listed in the Expression Builder

I defined the functions like in this question but I can not see the function in the Expression Builder. I plan to use this function to fill an invisible txtBox on my form and log it into db.

    Public Function GetUser(Optional whatpart = "username")
        Dim returnthis As String
        If whatpart = "username" Then GetUser = Environ("USERNAME"): Exit Function
        Set objSysInfo = CreateObject("ADSystemInfo")
        Set objUser = GetObject("LDAP://" & objSysInfo.USERNAME)
        Select Case whatpart
            Case "fullname": returnthis = objUser.FullName
            Case "firstname", "givenname": returnthis = objUser.givenName
            Case "lastname": returnthis = objUser.LastName
            Case Else: returnthis = Environ("USERNAME")
        End Select
        GetUser = returnthis
    End Function

Remember to mark the answer by @ashleedawg as an ANSWER if it worked for you - as you say it did below. It looks like you have a long history of asking questions and not marking answers as such. Hopefully that's just an oversight in this case - you can also go back over old questions and see if you missed answers for them too. — dbmitch, Jun 04 '18 at 03:19

ashleedawg · Answer 1 · 2018-05-30T03:52:00.493

I think you're asking how to have custom functions show up in the Expression Builder? (and therefore unrelated to Active Directory or logins).

It's most likely a simple case of either:
- you haven't saved the module since pasting the functions in, or,
- the functions are not located in a public module.

To make sure they're saved:
1. Close Access completely (all open databases), and then,
2. Re-open the database, and,
3. Open your module (from the "Modules" section of the Navigation Bar:

...and confirm your code is there.

Then try the expression builder again.

If the module is not showing in the Modules section, then your code is not in a public module.

Note that you don't have to use the Expression Builder. I've actually never used it, but I just tried it and once the module with my functions was saved, they showed up under:

Functions → My database name → My module name.

This screenshot shows how my function wasn't listed i the Expression Builder until I saved it, and then how I found it in the Builder:

There is a caveat--there are some expression builders that you (apparently) cannot use custom functions in. For example, you will not be able to find your custom functions in the expression builder for calculated columns, even if it shows up in the expression builder for queries. There may be other instances where it doesn't work either, but that's the one I'm sure of. — claypooj, Mar 20 '23 at 02:56

Use custom functions in the Expression Builder

1 Answers1