How to use system username directly in MS Access query?

Question

I would like to know if there is a way to get system username and use it directly in an MS Access query. I have made a parameter work within a query from a combo box on a form and I have also acquired system name in Access VBA using ENVIRON ("USERNAME").

Kindly let me know if this is possible.

Answer 1

You need to create a VBA function that returns the username, and then use the function in the query.

Public Function GetUserName() As String
    ' GetUserName = Environ("USERNAME")
    ' Better method, see comment by HansUp
    GetUserName = CreateObject("WScript.Network").UserName
End Function

and

SELECT foo FROM bar WHERE myUserName = GetUserName();

Answer 2

My solution kept all the work in VB.
I used a variable for the windows login username and then created a SQL string with that variable inserted. Lastly, I updated the query behind the form to use this new SQL string.

The CHR(34) puts quotes around the name as it is now a string inside the SQLSTR and needs to be within a set of quotes.

If you have a complex SQL statement, write it in the QBE using a string for the name and all the other variables, then switch to the SQL view and replace it using a VBA variable as shown below.

MyName = Environ("username")

sqlstr = "SELECT * From Projects WHERE ( ((Projects.LeadEngineer)=" & Chr(34) & MyName & Chr(34) & " AND ActiveYN = True ));"

Forms![Main Form].RecordSource = sqlstr