How to look up data in an Access table using the UserName Environ

Question

In MS Access, I have a table named "Table" with three fields. Besides the ID field, I have one field named "User" and one field named "Email". Both fields are "short text". The table has two rows, the first: User = Nobody and Email = [email protected]. The second: User = Somebody and Email = [email protected].

I've created a Module with the following code:

Sub Test()

Dim rs As Recordset
Dim localUser As String

localUser = Environ("UserName")
Set rs = CurrentDb.OpenRecordset("Table", dbOpenDynaset)

rs.FindFirst "[User] = 'localUser'"

MsgBox rs![Email]

End Sub

The local user is "Somebody" as shown when performing a: MsgBox Environ("UserName")

When I execute the code, I get a message box with "[email protected]".

What I am trying to get is a message box with "[email protected]".

What am I doing wrong?

Thank you in advance.

Gustav Gustav · Accepted Answer · 2019-05-17T17:26:22

Try with:

rs.FindFirst "[User] = '" & localUser & "'"

But you could use DLookup:

Dim localUser As String

localUser = Environ("UserName")
MsgBox Nz(DLookup("[Email]", "Table", "[User] = '" & localUser & "'"))

How to look up data in an Access table using the UserName Environ

1 Answers