asked on

Replacing Environ Funciton with GetUserName

After researching many options I am still unable to resolve the issue. I need to return the value of the UserName from within and Append query. the Query originally had the Environ function, but errors out when I attempt to use the same database within the Access 2007 environment. I have tried numerous options but it does not like my syntax or you just can't use it within the query.

What is the best way to append a table with specific data along with the current users name?

I also need to know the proper syntax of using this function within the query.

User: GetUserName("UserName") also tried User: GetUserName("User")

sql :
INSERT INTO audit ( APNO, WSNo, EditDate, [User], SourceField )
SELECT Getgapno() AS APNO, A.WS_No, A.DtAppnWS, GetUserName("UserName") AS [User], "WS Add" AS SourceField
FROM Audit RIGHT JOIN (SELECT TA_WS.WS_No, TA_WS.WS_ActCd, TA_WS.WS_Title, TA_WS.WS_CntrlCd, TA_WS.WS_DtTgtEngr, TA_WS.WS_DwgNo, TA_WS.DtAppnWS
FROM TA_WS
GROUP BY TA_WS.WS_No, TA_WS.WS_ActCd, TA_WS.WS_Title, TA_WS.WS_CntrlCd, TA_WS.WS_DtTgtEngr, TA_WS.WS_DwgNo, TA_WS.DtAppnWS
HAVING (((TA_WS.WS_No)<>Exists (SELECT WSNo FROM Audit WHERE (((APNO)=getgapno()) AND ((EditDate) Between GetgISISDate() And Date()) AND ((SourceField)="WS Add")))) AND ((TA_WS.DtAppnWS)>=GetgISISDate()))) AS A ON Audit.WSNo = A.WS_No
WHERE (((Audit.WSNo) Is Null));

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Open in new window

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

What is if proper syntax for using within the query?

K

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

" the Query originally had the Environ function, but errors out when I attempt to use the same database within the Access 2007 environment"
And yes, that is the case. Environ() no longer works as of A2007 when used in a query OR an expression in the control source of a Text box. You must now use GetUserName() or equivalent ... which is much preferred anyway.

mx

Karen Schaefer

ASKER

Still getting Undefined function'FOSUserName' in expression

INSERT INTO audit ( APNO, WSNo, EditDate, [User], SourceField )
SELECT Getgapno() AS APNO, A.WS_No, A.DtAppnWS, fOSUserName() AS [User], "WS Add" AS SourceField
FROM Audit RIGHT JOIN (SELECT TA_WS.WS_No, TA_WS.WS_ActCd, TA_WS.WS_Title, TA_WS.WS_CntrlCd, TA_WS.WS_DtTgtEngr, TA_WS.WS_DwgNo, TA_WS.DtAppnWS
FROM TA_WS
GROUP BY TA_WS.WS_No, TA_WS.WS_ActCd, TA_WS.WS_Title, TA_WS.WS_CntrlCd, TA_WS.WS_DtTgtEngr, TA_WS.WS_DwgNo, TA_WS.DtAppnWS
HAVING (((TA_WS.WS_No)<>Exists (SELECT WSNo FROM Audit WHERE (((APNO)=getgapno()) AND ((EditDate) Between GetgISISDate() And Date()) AND ((SourceField)="WS Add")))) AND ((TA_WS.DtAppnWS)>=GetgISISDate()))) AS A ON Audit.WSNo = A.WS_No
WHERE (((Audit.WSNo) Is Null));

Jim Dettman (EE MVE)

If you use the routine MX posted a link to:

User:fOSUserName()

JimD.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"What is if proper syntax for using within the query?"

Example - in part of your SQL ... and ... referring to the link above. Again, you need the fOSUserName() Function to go with the API Declaration ... and put this in a VBA module.

SELECT Getgapno() AS APNO, A.WS_No, A.DtAppnWS, fOSUserName() AS [User],

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"Still getting Undefined function'FOSUserName' in expression"
Again, because you (so far) ONLY have the API Declaration. You need the fOSUserName() Function to go with that. You cannot call the API declaration by itself.

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If ( lngX > 0 ) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Karen Schaefer

ASKER

i do have what you recommended and I am still getting the Undefined Function issue

K

Karen Schaefer

ASKER

INSERT INTO audit ( APNO, WSNo, EditDate, [User], SourceField )
SELECT Getgapno() AS APNO, A.WS_No, A.DtAppnWS, fOSUserName() AS User, "WS Add" AS SourceField
FROM Audit RIGHT JOIN (SELECT TA_WS.WS_No, TA_WS.WS_ActCd, TA_WS.WS_Title, TA_WS.WS_CntrlCd, TA_WS.WS_DtTgtEngr, TA_WS.WS_DwgNo, TA_WS.DtAppnWS
FROM TA_WS
GROUP BY TA_WS.WS_No, TA_WS.WS_ActCd, TA_WS.WS_Title, TA_WS.WS_CntrlCd, TA_WS.WS_DtTgtEngr, TA_WS.WS_DwgNo, TA_WS.DtAppnWS
HAVING (((TA_WS.WS_No)<>Exists (SELECT WSNo FROM Audit WHERE (((APNO)=getgapno()) AND ((EditDate) Between GetgISISDate() And Date()) AND ((SourceField)="WS Add")))) AND ((TA_WS.DtAppnWS)>=GetgISISDate()))) AS A ON Audit.WSNo = A.WS_No
WHERE (((Audit.WSNo) Is Null));

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Do you have a Reference set to DAO 3.6 - see image.
Is that code in a Regular VBA module ... and *not* in a Form module ?

mx
Capture1.gif

Jim Dettman (EE MVE)

K,

<<i do have what you recommended and I am still getting the Undefined Function issue>>

1. Make sure you have pasted that code into a standard module
2. The declare appears at the top of the module in the declarations section.
3. You save the module with a name other then fOSUserName.

If you've done all that, you should from the debug window be able to type:

Debug.? fOSUserName()

hit return and get your login name back. Once you can do that, move onto the query.

JimD.

Karen Schaefer

ASKER

1. Yes
2. Yes
3. Am I suppose to rename the function?

I debug and got error Sub or Function not defined, but when I debug compile it complies.

Option Compare Database
Option Explicit

Private Const mdbErrorLog = "S:\Access\ErrorLog.mdb" '***STANDARD***
Private Const txtApplName = "ApDbms" '***UPDATE***
Private Const basModName = "FE_CpxCdWS" '***UPDATE***
Private txtSubFuncName As String
Private txtApNo As String 'For use with databases that are airplane specific (each airplane having its own database) ***UPDATE*** 'InWork
Private dbs As Database
Private qdf As QueryDef

'for getting user id
'Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

'for getting computer name
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

Open in new window

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You have Private. You need Public

Public Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Public Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

And for good measure:

Public Function fOSUserName() As String

Karen Schaefer

ASKER

Now it will not compile either way.

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

sorry ... it's just

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function fOSUserName() As String

and the fOSUserName() needs to be in the same module.

mx

Jim Dettman (EE MVE)

<<And the declare should be private.>>

Although it can be public, but there is no need to make it so.

JimD.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Not according to the error message. It cannot be Public, which was my mistake.

mx

Karen Schaefer

ASKER

The posting to separate module probably was my problem all a long. Thanks guys.

K