Getting User Name

[Rohn Everson]

I am trying to capture user ID ! I have seen various ways how, but I
can't seem to capture the unique ID. I have a SQL Server 2k (back end for
this DB). The SQL database is running with user permissions for our ERP
software. In this new DB the permission are applied to the only three users
who will have access to this DB through an ODBC but I want to collect the
user ID if possible. I do not have DB security set up in Access since
permissions can be carried from SQL Server.

Any ideas?

Thanks, Rohn

 

[Brendan Reynolds]

I am trying to capture user ID ! I have seen various ways how, but I
can't seem to capture the unique ID. I have a SQL Server 2k (back end for
this DB). The SQL database is running with user permissions for our ERP
software. In this new DB the permission are applied to the only three
users who will have access to this DB through an ODBC but I want to collect
the user ID if possible. I do not have DB security set up in Access since
permissions can be carried from SQL Server.

Any ideas?

Thanks, Rohn

Public Function GetSqlUser() As String

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection

'replace "Students" with the name of a linked table in your app
cnn.ConnectionString = CurrentDb.TableDefs("Students").Connect

cnn.Open
Set rst = New ADODB.Recordset
rst.Open "SELECT SYSTEM_USER AS TheUser", cnn
GetSqlUser = rst.Fields("TheUser")
rst.Close
cnn.Close

End Function

 

[Rohn Everson]

Thank you for the reply Brendan, I also need to know where does this
function go?

Thanks again, Rohn

 

[M Skabialka]

If your users log into a network, use:
Me.strYourUserField = Environ("Username") to return the UserName as logged
into the network
Mich

 

[Rohn Everson]

Mich,
I added; [Me].[strsysuser]=Environ("Username") to the control source of the
text box within the form used for data collection and I get #Name?

What am I doing wrong?

Thanks, Rohn

 

[M Skabialka]

On your strsysuser control properties you would enter for the Control Source
=Environ("Username")
Or
You would use [Me].[strsysuser]=Environ("Username") in code behind the form,
e.g on the On Load event
Mich

Mich,
I added; [Me].[strsysuser]=Environ("Username") to the control source of
the text box within the form used for data collection and I get #Name?

What am I doing wrong?

Thanks, Rohn

If your users log into a network, use:
Me.strYourUserField = Environ("Username") to return the UserName as
logged into the network
Mich

 

[Brendan Reynolds]

Thank you for the reply Brendan, I also need to know where does this
function go?

Thanks again, Rohn

The function can go into any standard module, that is to say, any VBA module
that is not a Form, Report, or other Class module.

Once you've put the code into a standard module, you can use it anywhere in
your application, in VBA code, as the control source of a text box, or as an
expression in a query.

In VBA code ....

MsgBox GetSqlUser()

In a control source property ...

=GetSqlUser()

In a query (in design view) ...

UserName: GetSqlUser()

In a SQL statement ....

SELECT SomeField, GetSqlUser() AS UserName FROM SomeTable