OutputTo hangs on Error 52 (1 Viewer)

[David R]

Some of my users do not have access to all of our servers. In trying to automate I've hit one user whose system hangs up when Access tries to write to a server folder she does not have write (or even read) permissions for (Error 52, Bad file name or number). See the "Me.CitationType > 500" line below.

Private Sub comboStatus_AfterUpdate()
Dim hDate As String
Dim sFile As String
    
    If Me.CitationStatus = "Dismissed" Then
        DoCmd.OpenReport "reportDismissalLetter", acViewPreview, , "[CitationID] = " & Me.CitationID

        DoCmd.Hourglass True
        If Me.CitationType > 500 Then 'DB
            hDate = "\\LockedDownServer\Folder\Citations\" & Me.refHearingDate.Column(1) & " " & Format(Me.refHearingDate, "mmmm dd, yyyy")
        Else
            hDate = "\\AccessibleServer\FolderName\Citations\" & Me.refHearingDate.Column(1) & " " & Format(Me.refHearingDate, "mmmm dd, yyyy")
        End If
        
        If Len(Dir(hDate, vbDirectory)) = 0 Then MkDir hDate
        
        sFile = "Dismissal letter " & Me.CaseNumber & " " & Me.comboAdmin.Column(0) & ".pdf"
        DoCmd.OutputTo acOutputReport, "reportDismissalLetter", acFormatPDF, hDate & "\" & sFile, , , , acExportQualityPrint
        DoCmd.Hourglass False
    End If
End Sub

It gets as far as "If Len(Dir(hDate, vbDirectory)) = 0" and then hangs with Error 52. Testing for Dir() sooner doesn't help either.

I'm at a loss how to either test for read-write permissions or trap Error 52. "On Error GoTo ErrorHandler" never fires to even test for 52, unless I'm missing something.

 

[spikepl]

What is Filesystem.len?

 

[David R]

Typo (hadn't cleared out all of my latest attempt). That line has been fixed.

 

[spikepl]

And the higher level directories exist for sure? Which exact bit in that statement hangs it? Break it into a then end if, such that there is no doubt.

 

[David R]

Correct. Other users (including myself, with full server access) can write to LockedDownServer just fine, which is why I never noticed it during testing. I can simulate it now by changing the LockedDownServer directory to one I don't have access to on a third, unrelated server.

To reiterate: If Len(Dir(hDate, vbDirectory)) = 0 Then is what throws the error 52. Breaking it into a more formal If/Then doesn't change the highlight or breakpoint. She can't see that server at all, so it can't get Len() for it. And since her FE is locked down, without error handling it just hangs on that moment.

I can get her access to that server from IT (probably), but that's not the point. I'd like to learn how to trap/test for this error in the future.

 

[spikepl]

You can reiterate until you are blue in the face I cannot see what you see, so I ask.

I am stunned that you still have not broken it down into atomic bits so as not to be in doubt what causes what. Do it.

 

[David R]

I reiterate because I already answered your question in the OP, spike. I'd already done everything you suggested, hence the testiness. I'm not a new user here.

Nonetheless, the seventeenth iteration of error handling trapped it and now all works as designed.

Private Sub comboStatus_AfterUpdate()
Dim hDate As String
Dim sFile As String
    
    If Me.CitationStatus = "Dismissed" Then
        DoCmd.OpenReport "reportDismissalLetter", acViewPreview, , "[CitationID] = " & Me.CitationID

        DoCmd.Hourglass True
        If Me.CitationType > 500 Then 'DB
            hDate = "\\ng-ncsdmaster\InfoSys\" & Me.refHearingDate.Column(1) & " " & Format(Me.refHearingDate, "mmmm dd, yyyy")
        Else
            hDate = "\\kcmogis\ng\Citations\" & Me.refHearingDate.Column(1) & " " & Format(Me.refHearingDate, "mmmm dd, yyyy")
        End If
            
        On Error GoTo ErrorHandler
        If Len(Dir(hDate, vbDirectory)) = 0 Then
            MkDir hDate
        End If
        
        sFile = "Dismissal letter " & Me.CaseNumber & " " & Me.comboAdmin.Column(0) & ".pdf"
        DoCmd.OutputTo acOutputReport, "reportDismissalLetter", acFormatPDF, hDate & "\" & sFile, , , , acExportQualityPrint
        DoCmd.Hourglass False
    End If
    Exit Sub
    
ErrorHandler:
    DoCmd.Hourglass False
    MsgBox "Unable to output PDF copy to protected server or folder", vbExclamation, "Access"
End Sub