Access VBA question

Posted on 2006-05-16
Medium Priority
Last Modified: 2012-06-22
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.


Private Const RESOURCETYPE_ANY = &H0
    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_CONNECTED, 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).lpLocalName) + 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).lpRemoteName) + 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
Question by:earngreen
  • 3
  • 3
  • 2
  • +1
LVL 65

Expert Comment

ID: 16690979
do a debug/compile, if any errors, see what line it stops on
also, just out of curiosity, check your references, see if any are missing (Tools/References)
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 16691109
>Expected variable or procedure, not module

If your module and your function have the same name, you may get this error.  If this is the case, make sure there is a distinction between the two.  ie:

Function Name:

Module Name:

Author Comment

ID: 16691177

Now I am getting an error that says ambiguous name: LetterToUNC
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 16691241

Just figured it out. had to modLetterToUNC.LetterToUNC.

Thanks for the help.

LVL 65

Expert Comment

ID: 16691252
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

Expert Comment

ID: 16691258
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.

LVL 61

Expert Comment

ID: 16691375
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.

Author Comment

ID: 16691864
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:\")
LVL 61

Expert Comment

ID: 16695475
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")
? LetterToUNC ("C")

Or using Pigster's suggestion...
varStr = LettertoUNC("N:\")
Print varStr


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question