Reference Removal, Get 'Subscript out of range.'

[Dan Hugos]

Trying to remove a reference to a form object from an app. My code:



Dim ref As Reference, strRef As String
For Each ref In References

If ref.FullPath Like "*" & "FM20" & "*" Then
strRef = ref.FullPath
GoTo DeleteIt
End If

Next ref

DeleteIt:
Set ref = References(strRef)
References.Remove ref

When it goes to set the reference, I can see that it's getting to the "Set ref = References(strRef)" statement, but then it breaks. I get "Subscript out of range." What am I missing?

[June7]

What is FM20? Is this supposed to be literal text and not a variable? Why use concatenation?

If ref.FullPath Like "*FM20*" Then

I don't understand what 'reference' you are removing.

[Dan Hugos]

Yes, I'm looking for literal text that is a part of the Fullpath property of the Reference.

For what it's worth, the reference is MS Forms 2.0, but the actual issue is why the code works fine until it gets to the line where it says 'Set Ref = References(strRef)' which is when it returns 'Subscript out of range.'

This is even though Access set the strRef variable. Doesn't seem to make sense, so I'm guessing I'm missing something very obvious.

[June7]

I don't even see that library in the References list.

I've never tried code to add or remove references. No idea why that fails.

Does this help http://support.microsoft.com/en-us/kb/308340

[Dan Hugos]

Since it doesn't appear by default on your reference list, you have to browse to it. On an XP machine it's in Windows\System32, on a Win7 machine it's in Windows\SysWow64. It's called FM20.DLL. I'll have a look through the kb, thanks for pointing it out.

[ssanfu]

I found some code by Terry Kreft at http://bytes.com/topic/access/answer...references-vba
and modified it to print to the immediate window.

Sub CheckReference()
    'Terry Kreft
    'http://bytes.com/topic/access/answers/210488-how-delete-missing-references-vba

    Dim refCurr As Access.Reference
    Dim intCount As Integer
    Dim strRef As String
    Dim bIsName As Boolean

    For intCount = Access.References.Count To 1 Step -1
        bIsName = False
        Set refCurr = Access.References(intCount)
        Debug.Print intCount & "  |  " & refCurr.BuiltIn & "  |  " & refCurr.Name & "  |  " & refCurr.FullPath

        'cannot remove built in references
        If Not refCurr.BuiltIn Then

            If refCurr.IsBroken Then
                Access.References.Remove refCurr
            Else
                'is this the one I want to delete?
                bIsName = InStr(refCurr.FullPath, "FM20")

                'could also use
                '    bIsName = InStr(refCurr.Name, "MSFORMS")

                If bIsName Then
                    Access.References.Remove refCurr
                    Debug.Print "***** reference to MS Form removed *******"
                End If
            End If
        End If
    Next

End Sub

Tested in Win XP, Access 2010