• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

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

0
Karen Schaefer
Asked:
Karen Schaefer
  • 9
  • 7
  • 4
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You only need GetUserName() ... no argument.

But seems you are missing the VBA Function:

You need all of this:

http://access.mvps.org/access/api/api0008.htm

mx
0
 
Karen SchaeferAuthor Commented:
What is if proper syntax for using within the query?

K
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" 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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Karen SchaeferAuthor Commented:
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));
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

If you use the routine MX posted a link to:

User:fOSUserName()

JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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],
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
0
 
Karen SchaeferAuthor Commented:
i do have what you recommended and I am still getting the Undefined Function issue

K
0
 
Karen SchaeferAuthor Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
Karen SchaeferAuthor Commented:
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

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And for good measure:


Public Function fOSUserName() As String
0
 
Karen SchaeferAuthor Commented:
Now it will not compile either way.
 getUserErrMsg
K
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
K,

  Your pasting that in an object module (attached to a form or report).

  It needs to be in a standard module.

  And the declare should be private.

JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<And the declare should be private.>>

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

JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Not according to the error message.  It cannot be Public, which was my mistake.

mx
0
 
Karen SchaeferAuthor Commented:
The posting to separate module probably was my problem all a long.  Thanks guys.

K
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 9
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now