earngreen
asked on
Access VBA question
I am using the following module in Access to translate the drive letter into the network share. When I try to call this from another module, i get the error "Expected variable or procedure, not module" I am calling the module like so LetterToUNC("N:"). Does anyone have an idea why I am getting this error.
Thanks
Private Const RESOURCETYPE_ANY = &H0
Private Const RESOURCE_CONNECTED = &H1
Private Type NETRESOURCE
dwScope As Long
dwType As Long
dwDisplayType As Long
dwUsage As Long
lpLocalName As Long
lpRemoteName As Long
lpComment As Long
lpProvider As Long
End Type
Private Declare Function WNetOpenEnum Lib "mpr.dll" Alias "WNetOpenEnumA" (ByVal dwScope As Long, ByVal dwType As Long, ByVal dwUsage As Long, lpNetResource As Any, lphEnum As Long) As Long
Private Declare Function WNetEnumResource Lib "mpr.dll" Alias "WNetEnumResourceA" (ByVal hEnum As Long, lpcCount As Long, lpBuffer As Any, lpBufferSize As Long) As Long
Private Declare Function WNetCloseEnum Lib "mpr.dll" (ByVal hEnum As Long) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Any) As Long
Private Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Public Function LetterToUNC(DriveLetter As String) As String
Dim hEnum As Long
Dim NetInfo(1023) As NETRESOURCE
Dim entries As Long
Dim nStatus As Long
Dim LocalName As String
Dim UNCname As String
Dim i As Long
Dim r As Long
' Begin the enumeration
nStatus = WNetOpenEnum(RESOURCE_CONN ECTED, RESOURCETYPE_ANY, 0&, ByVal 0&, hEnum)
LetterToUNC = DriveLetter
'Check for success from open enum
If ((nStatus = 0) And (hEnum <> 0)) Then
' Set number of entries
entries = 1024
' Enumerate the resource
nStatus = WNetEnumResource(hEnum, entries, NetInfo(0), CLng(Len(NetInfo(0))) * 1024)
' Check for success
If nStatus = 0 Then
For i = 0 To entries - 1
' Get the local name
LocalName = ""
If NetInfo(i).lpLocalName <> 0 Then
LocalName = Space(lstrlen(NetInfo(i).l pLocalName ) + 1)
r = lstrcpy(LocalName, NetInfo(i).lpLocalName)
End If
' Strip null character from end
If Len(LocalName) <> 0 Then
LocalName = Left(LocalName, (Len(LocalName) - 1))
End If
If UCase$(LocalName) = UCase$(DriveLetter) Then
' Get the remote name
UNCname = ""
If NetInfo(i).lpRemoteName <> 0 Then
UNCname = Space(lstrlen(NetInfo(i).l pRemoteNam e) + 1)
r = lstrcpy(UNCname, NetInfo(i).lpRemoteName)
End If
' Strip null character from end
If Len(UNCname) <> 0 Then
UNCname = Left(UNCname, (Len(UNCname) - 1))
End If
' Return the UNC path to drive
'added the [] to seperate on printout only
LetterToUNC = UNCname
' Exit the loop
Exit For
End If
Next i
End If
End If
' End enumeration
nStatus = WNetCloseEnum(hEnum)
End Function
Thanks
Private Const RESOURCETYPE_ANY = &H0
Private Const RESOURCE_CONNECTED = &H1
Private Type NETRESOURCE
dwScope As Long
dwType As Long
dwDisplayType As Long
dwUsage As Long
lpLocalName As Long
lpRemoteName As Long
lpComment As Long
lpProvider As Long
End Type
Private Declare Function WNetOpenEnum Lib "mpr.dll" Alias "WNetOpenEnumA" (ByVal dwScope As Long, ByVal dwType As Long, ByVal dwUsage As Long, lpNetResource As Any, lphEnum As Long) As Long
Private Declare Function WNetEnumResource Lib "mpr.dll" Alias "WNetEnumResourceA" (ByVal hEnum As Long, lpcCount As Long, lpBuffer As Any, lpBufferSize As Long) As Long
Private Declare Function WNetCloseEnum Lib "mpr.dll" (ByVal hEnum As Long) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Any) As Long
Private Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Public Function LetterToUNC(DriveLetter As String) As String
Dim hEnum As Long
Dim NetInfo(1023) As NETRESOURCE
Dim entries As Long
Dim nStatus As Long
Dim LocalName As String
Dim UNCname As String
Dim i As Long
Dim r As Long
' Begin the enumeration
nStatus = WNetOpenEnum(RESOURCE_CONN
LetterToUNC = DriveLetter
'Check for success from open enum
If ((nStatus = 0) And (hEnum <> 0)) Then
' Set number of entries
entries = 1024
' Enumerate the resource
nStatus = WNetEnumResource(hEnum, entries, NetInfo(0), CLng(Len(NetInfo(0))) * 1024)
' Check for success
If nStatus = 0 Then
For i = 0 To entries - 1
' Get the local name
LocalName = ""
If NetInfo(i).lpLocalName <> 0 Then
LocalName = Space(lstrlen(NetInfo(i).l
r = lstrcpy(LocalName, NetInfo(i).lpLocalName)
End If
' Strip null character from end
If Len(LocalName) <> 0 Then
LocalName = Left(LocalName, (Len(LocalName) - 1))
End If
If UCase$(LocalName) = UCase$(DriveLetter) Then
' Get the remote name
UNCname = ""
If NetInfo(i).lpRemoteName <> 0 Then
UNCname = Space(lstrlen(NetInfo(i).l
r = lstrcpy(UNCname, NetInfo(i).lpRemoteName)
End If
' Strip null character from end
If Len(UNCname) <> 0 Then
UNCname = Left(UNCname, (Len(UNCname) - 1))
End If
' Return the UNC path to drive
'added the [] to seperate on printout only
LetterToUNC = UNCname
' Exit the loop
Exit For
End If
Next i
End If
End If
' End enumeration
nStatus = WNetCloseEnum(hEnum)
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mbizup
Now I am getting an error that says ambiguous name: LetterToUNC
Now I am getting an error that says ambiguous name: LetterToUNC
ASKER
Sorry,
Just figured it out. had to modLetterToUNC.LetterToUNC .
Thanks for the help.
Just figured it out. had to modLetterToUNC.LetterToUNC
Thanks for the help.
mbizup, u know whats weired
I changed my module name to same as function then created test function list this
Public Sub TestIt2()
debug.print LetterToUNC ("C")
End Sub
and it did not come up with error
however, u go to the Immediate Window and type in LetterToUNC ("C")
u get the error
strange
I changed my module name to same as function then created test function list this
Public Sub TestIt2()
debug.print LetterToUNC ("C")
End Sub
and it did not come up with error
however, u go to the Immediate Window and type in LetterToUNC ("C")
u get the error
strange
Do you have two copies of the module?
Also, if you are running a function, that means a value will be returned.
So where you call it from should have a variable to take that value return.
So try setting up in the module you call the LetterTOUNC from a variable:
Dim varStr as string
When you do the call to the module do this:
varStr = LettertoUNC("N:\")
See what that does.
Thanks.
Also, if you are running a function, that means a value will be returned.
So where you call it from should have a variable to take that value return.
So try setting up in the module you call the LetterTOUNC from a variable:
Dim varStr as string
When you do the call to the module do this:
varStr = LettertoUNC("N:\")
See what that does.
Thanks.
RockiRoads--- That is weird! The issues I have had with modules and functions sharing the same name have at least been consistent problems :-)
I bet Pigster's right about the duplicate module causing the second error. If there is a backup copy of the module (with the same function), you are bound to get an "ambiguous name" error. If you just create a backup of the whole db, rather than just the module you are working on, you wont get the duplicate function names and you can leave out the reference to the module when calling the function.
I bet Pigster's right about the duplicate module causing the second error. If there is a backup copy of the module (with the same function), you are bound to get an "ambiguous name" error. If you just create a backup of the whole db, rather than just the module you are working on, you wont get the duplicate function names and you can leave out the reference to the module when calling the function.
ASKER
That is strange because now it is returning an empty string on any call that i make wether it be by
LetterToUNC ("C")
or by
varStr = LettertoUNC("N:\")
LetterToUNC ("C")
or by
varStr = LettertoUNC("N:\")
I just tested the function as you have posted, and it seems to work fine... Where are you calling the function from? If you are testing it from the immediate window, you need to include a print statement:
Print LetterToUNC ("C")
or
? LetterToUNC ("C")
Or using Pigster's suggestion...
varStr = LettertoUNC("N:\")
Print varStr
Print LetterToUNC ("C")
or
? LetterToUNC ("C")
Or using Pigster's suggestion...
varStr = LettertoUNC("N:\")
Print varStr
also, just out of curiosity, check your references, see if any are missing (Tools/References)