Inaccessible GetUserName Function

[StarvinMarvin]

Wanted to add the GetUserName code to a query for GetUserName and Now() to use to timestamp record entries.

Works in the query after I create the function, after I save the function in the module and restart I get the undefined function error....

Seems like this is ending up private or something... tried adding Public before the Function definition and still no go. Does it have to be in the declarations? Just adding Public up there doesn't work.

The version I'm using is:

[COLOR=blue]Declare Function[/COLOR] WNetGetUser Lib "mpr.dll" _
     [COLOR=blue]Alias[/COLOR] "WNetGetUserA" (ByVal lpName [COLOR=blue]As String[/COLOR], _
     [COLOR=blue]ByVal[/COLOR] lpUserName [COLOR=blue]As String[/COLOR], lpnLength As Long) [COLOR=blue]As Long[/COLOR]
[COLOR=blue]Const[/COLOR] NoError = 0                    [COLOR=darkgreen]'The Function call was successful[/COLOR]
[COLOR=navy]Function[/COLOR] GetUserName() [COLOR=navy]As String[/COLOR]
     [COLOR=navy]Dim[/COLOR] LUserName [COLOR=navy]As String[/COLOR]
     [COLOR=navy]Const[/COLOR] lpnLength [COLOR=navy]As Integer[/COLOR] = 255
     [COLOR=navy]Dim[/COLOR] status [COLOR=navy]As Integer[/COLOR]
     Dim lpName
     [COLOR=darkgreen]' Assign the buffer size constant to lpUserName.[/COLOR]
     LUserName = Space$(lpnLength + 1)
     [COLOR=darkgreen]' Get the log-on name of the person using product[/COLOR].
     status = WNetGetUser(lpName, LUserName, lpnLength)
     [COLOR=darkgreen]' See whether error occurred.[/COLOR]
     [COLOR=navy]If[/COLOR] status = NoError Then
          [COLOR=darkgreen]' This line removes the null character. Strings in C are null-[/COLOR]
[COLOR=darkgreen]        ' terminated. Strings in Visual Basic are not null-terminated.[/COLOR]
[COLOR=darkgreen]        ' The null character must be removed from the C strings to be used[/COLOR]
[COLOR=darkgreen]        ' cleanly in Visual Basic.[/COLOR]
          LUserName = Left$(LUserName, InStr(LUserName, Chr(0)) - 1)
     [COLOR=navy]Else[/COLOR]
          [COLOR=darkgreen]' An error occurred.[/COLOR]
          [COLOR=navy]MsgBox[/COLOR] "Unable to get the name."
          [COLOR=navy]End[/COLOR]
     [COLOR=navy]End If[/COLOR]
     GetUserName = LUserName
[COLOR=navy]End Function[/COLOR]

This is obviously simple but I'm not seeing when this becomes private, if that's what's happening, and what's necessary to make it public.

side note...just tried the other version at mvps.org and I get the same thing, works until I save it, can't be found after opening.

 

[RuralGuy]

Do you have this code in a standard module?

 

[StarvinMarvin]

I made a module, inserted procedure. Saved upon close as a module. You mean that kinda module?

 

[RuralGuy]

Exactly! Sounds like you did it correctly. Can you run the procedure fron the Immediate window?
?GetUserName

 

[StarvinMarvin]

ok...working with the other version now since it's recco'd by folks here:

[COLOR=navy]Public Declare Function[/COLOR] apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
[COLOR=navy]Function[/COLOR] fOSUsername() [COLOR=navy]As String[/COLOR]
' Returns the network login name
[COLOR=navy]Dim[/COLOR] lngLen [COLOR=navy]As Long[/COLOR], lngX [COLOR=navy]As Long[/COLOR]
[COLOR=navy]Dim[/COLOR] strUserName [COLOR=navy]As String[/COLOR]
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    [COLOR=navy]If[/COLOR] (lngX > 0) [COLOR=navy]Then
[/COLOR]        fOSUsername = Left$(strUserName, lngLen - 1)
    [COLOR=navy]Else
[/COLOR]        fOSUsername = vbNullString
    [COLOR=navy]End If
[/COLOR][COLOR=navy]End Function[/COLOR]

Same thing tho, create module, insert procedure, save as fOSUsername, saves as module on quit. Can't find it on reopen.

?fOSUsername in imediate yields : compile error type mismatch.

 

[RuralGuy]

Have you checked to see if you have any bogus MISSING References?

 

[ChrisO]

If you are saying you have a Function named fOSUsername in a module named fOSUsername change the name of the module to something like mdlUsername.

 

[RuralGuy]

Good eye Chris! I didn't see that.

 

[ChrisO]

Just another good reason to have some sort of naming convention in place.

 

[RuralGuy]

I couldn't agree more! I usually start with mod or bas.

 

[StarvinMarvin]

Still a newb with this... so the module has to have a different name than the name of the fomula inside... there's a problem interpreting what level object I'm referencing due to duplicate names...? nk...named the module different, querying by name of function without referencing the module and it's working... closed, saved, restarted and it looks like the query finds the function buried in a module with a different name.

Seems odd that if I reference fOSUsername() that the function can't be found... doesn't the way I reference it tell it what I'm looking for versus a module name?

Another valuable lesson learned! Thanks guys

 

[ghudson]

I think the default is Public even if it is not declared in front of the Sub or Funciton name but I would put it there just to be safe.

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

[COLOR="Blue"][SIZE="4"][B]Public [/B][/SIZE][/COLOR]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

 

[SOS]

Yes, that is correct. If a sub or function is NOT preceded with PRIVATE then it is PUBLIC.

So

Function test()

and

Public Function test()


are EXACTLY the same as far as scope.

 

[RuralGuy]

Just glad we could help.

 

[ChrisO]

StarvinMarvin

>>doesn't the way I reference it tell it what I'm looking for versus a module name?<<

Actually that's a pretty good question to which I have no answer.
I've never seen a valid reason given for it but one would assume there is.

 

[vbaInet]

I would imagine that the compiler would have something similar to this syntax when referencing a function in this context:

For each module in Modules
    If [COLOR=Red][B]module.Name <> nameOfFunction[/B][/COLOR] Then
        For Each function in Functions
        
        Next
    End If
Next

 

[ChrisO]

Maybe, but why module.Name <> nameOfFunction

I can see the point that ModuleName, PublicProcedureName and ModuleName.PrivateProcedureName must be unique but why ModuleName <> ProcedureName?

In a Class Module we can even have non-unique PublicProcedureName whereby the uniqueness is derived from the Class name, ModuleName.PublicProcedureName. In this case we can have a PublicProcedureName called Form1 in the class module of Form1 whereby Form1 and Form1 does not cause a problem. Even if we regard a PublicProcedureName as a property of the class module why not also regard a PublicProcedureName as a property of a unique standard module? It would then seem that the only requirement of the word Private is to disassociate the ProcedureName from the module name which would limit the call to being internal.

I think it would be all guesswork until Microsoft gave us the reason and I have never seen that reason.

 

[vbaInet]

I was talking in terms of procedures that are called without a fully qualified reference. It could be that there are two paths to which a procedure is referenced. So in the scope of a query, the first path being following the reference (if fully qualified) and the other traversing the Modules object to find the function. For the second instance, because the compiler does not see a dot or bang within the call, the rules for finding the procedure becomes stricter.

Well, it's all guess work Chris.

I think it would be all guesswork until Microsoft gave us the reason and I have never seen that reason.

That would certainly be a long wait (that is if it ever suffices).

If the OP had put the full reference it would have worked.