Converting Sub to Function

[CB_DFW]

I really have no experience with modules and code but i have tried to use code that others create. I am trying to use this code to enter in the username into a table when the user runs a particular macro. I was told that I have to convert this sub (?) into a function.

This was my original way of doing this by prompting the user to type in their name.

INSERT INTO [EEC Uploads Log] ( MacroName, EndingTotal, EndTime, Auditor )
SELECT "Newhires" AS Upload, Count(EmpInf.EEID) AS CountOfEEID, Now() AS EndTime, [Specialist Name] AS Auditor
FROM EmpInf
GROUP BY "Newhires", Now(), [COLOR=red][Specialist Name], [/COLOR]EmpInf.FILECOMPLETE
HAVING (((EmpInf.FILECOMPLETE)=False));

I now want to use this code:

' Declare for call to mpr.dll.
   Declare Function WNetGetUser Lib "mpr.dll" _
      Alias "WNetGetUserA" (ByVal lpName As String, _
      ByVal lpUserName As String, lpnLength As Long) As Long
 
   Const NoError = 0       'The Function call was successful
 
   Sub GetUserName()
 
      ' Buffer size for the return string.
      Const lpnLength As Integer = 255
 
      ' Get return buffer space.
      Dim status As Integer
 
      ' For getting user information.
      Dim lpName, lpUserName As String
 
      ' Assign the buffer size constant to lpUserName.
      lpUserName = Space$(lpnLength + 1)
 
      ' Get the log-on name of the person using product.
      status = WNetGetUser(lpName, lpUserName, lpnLength)
 
      ' See whether error occurred.
      If status = NoError Then
         ' This line removes the null character. Strings in C are null-
         ' terminated. Strings in Visual Basic are not null-terminated.
         ' The null character must be removed from the C strings to be used
         ' cleanly in Visual Basic.
         lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
      Else
 
         ' An error occurred.
         MsgBox "Unable to get the name."
         End
      End If
 
      ' Display the name of the person logged on to the machine.
      MsgBox "The person logged on this machine is: " & lpUserName
 
   End Sub

I tried to call this from my query like this, but it does not work. I was told it has to be a function but I don't know how to create it as a function. Any help would be greatly appreciated.

INSERT INTO [EEC Uploads Log] ( MacroName, EndingTotal, EndTime, Auditor )
SELECT "Newhires" AS Upload, Count(EmpInf.EEID) AS CountOfEEID, Now() AS EndTime, GetUserName() AS Auditor
FROM EmpInf
GROUP BY "Newhires", Now(), GetUserName(), EmpInf.FILECOMPLETE
HAVING (((EmpInf.FILECOMPLETE)=False));

Thanks so much. CB

 

[pbaldy]

Yes, to return a value it has to be a function. This looks simpler:

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

 

[CB_DFW]

I was not able to get that to work. I pasted in a module and named it like it said. When I try to run it, it does not do anything.

Any ideas?

 

[pbaldy]

I use that in a number of applications, so I know it works. How are you calling it?

 

[CB_DFW]

I was just hitting the run button from in the VB editor.

 

[pbaldy]

Try this in the Immediate window:

?fOSMachineName()

 

[CB_DFW]

Says: Sub or Function not defined

This is what is in the code:

Option Compare Database
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

Thanks so much.

 

[pbaldy]

I assume that's in a standard module, and the name of the module is different than the name of the function?

 

[CB_DFW]

I attached the database. It's just a shell really.

Thanks so much.

 

[pbaldy]

What's the issue? What I suggested earlier works, as does this:

SELECT Table1.Name, age([dob]) AS age, fOSUserName() AS UserName
FROM Table1;

 

[CB_DFW]

Works perfect. I guess I was doing something wrong. When I put it in the query it works like a charm. Sorry to be such a pain.

You rock. Thanks so much for the help.

CB

 

[pbaldy]

No problem, glad we got it sorted out. What I typically do is run that function when the application starts, and store it in a hidden form control (I usually have a main menu form that stays open). Then I just grab the value from that control whenever I need it, rather than running the function over and over. It's not a big performance problem though.

 

[CB_DFW]

I've added it to four queries that we run morning and afternoon. Now we don't have to type in who ran them.

My desire to do this was because I want to put timers on these queries/macros so that they will run automatically. I needed to eliminate any manual intervention before I could move on to this.

Thanks again.