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(), [Specialist Name], 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

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

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

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?

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

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

Try this in the Immediate window:

?fOSMachineName()

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.

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

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

Thanks so much.

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

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

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

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.

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.