renaming file

[KeythStone]

Hello to all,

What I'd like to do is publish a report to a folder on a shared drive. The below code allows this to happen.

Dim StrDocUPDINS As String
StrDocUPDINS = "N:\AR Management\Caliber\AIM Reports\UPDINS 2" & ".pdf"
If Dir(StrDocUPDINS) <> vbNullString Then
Kill StrDocUPDINS
End If
DoCmd.OpenReport "AIM (Assignment,DB)_UPDINS", acViewPreview, "", "", acIcon
DoCmd.OutputTo acOutputReport, "AIM (Assignment,DB)_UPDINS", "PDFFormat(*.pdf)", StrDocUPDINS, False, "", 0, acExportQualityPrint
DoCmd.Close acReport, "AIM (Assignment,DB)_UPDINS"


This code allows me to replace the file with the same filename (I believe): If Dir(StrDocUPDINS) <> vbNullString Then
Kill StrDocUPDINS
End If

Sometimes users are in the file and this kills the process. So we have two file names to account for this, one named UPDINS and another UPDINS 2.

Question:: How can I have it so if one file is being used Access will write to the other filename? I think it's a point of incorporating it into the If, then, else statement. Any suggestions??

Stay smooth,
KeythStone

 

[KeythStone]

maybe to rephrase:

What i would like is to output to replace a file with the same filename. But if someone is in the file at the time then the process is terminated. We're aware of people being in the files so we set up filename and filename 2. If someone's in filename we can manually replace filename 2.

What I'd like to do is do all of this automatically.
1. to publish to filename.
2. if filename is occupied, to publish as filename 2.

Any suggestions?

 

[boblarson]

Set an error handler and if the certain error number occurs (the one that occurs when someone is in the file) use a branch path (not normally good to do, but sometimes unavoidable).

 

[KeythStone]

Hey BobLarson and anyone else,

Yeah, it's not necessarily the error handling that'll help as much as it's the bypassing of the error i need to accomplish. That's what i thought the above code would do, bypass outputting to filename1 (cuz its in use) and output to filename2.

Example. Monday: publish to filename1. Tuesday: user in filename1, need to publish to filename2. Wednesday: user in filename2, need to publish to filename1. Ad nauseum. BUT on the next week I need to switch the pattern and the maintenance is distracting having to go into the module each week and rearrange the filename orders.

Thanks,
KeythStone

 

[boblarson]

The error handler could be as simple as:

    On Error GoTo err_handler
    Dim StrDocUPDINS As String
    Dim i      As Integer
 
    StrDocUPDINS = "N:\AR Management\Caliber\AIM Reports\UPDINS" & ".pdf"
 
Rename:
    If StrDocUPDINS = vbNullString Then
        StrDocUPDINS = "N:\AR Management\Caliber\AIM Reports\UPDINS 2" & ".pdf"
    End If
 
  ' If the report is open and a kill is attempted, it will generate
  ' an Error 70.  So we handle that in the error handler and redirect
  ' to the label Rename which then changes to the other name.  Like
  ' I said previously, we don't like using redirecting code but sometimes
  ' it does solve for some tricky issues.
    If Dir(StrDocUPDINS) <> vbNullString Then
        Kill StrDocUPDINS
    End If
 
 
    DoCmd.OpenReport "AIM (Assignment,DB)_UPDINS", acViewPreview, "", "", acIcon
 
    DoCmd.OutputTo acOutputReport, "AIM (Assignment,DB)_UPDINS", "PDFFormat(*.pdf)", StrDocUPDINS, False, "", 0, acExportQualityPrint
 
' best to use acSaveNo otherwise it may prompt the user and you 
' don't want design changes saved (which can include filters).
    DoCmd.Close acReport, "AIM (Assignment,DB)_UPDINS", acSaveNo
 
Proc_Exit:
    Exit Function
 
err_handler:
    Select Case Err.Number
        Case 70
            If i < 2 Then
                i = i + 1
                StrDocUPDINS = vbNullString
                Resume Rename
            End If
 
        Case Else
            MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
            Resume Proc_Exit
            Resume
    End Select