FileName Error

[Tophan]

I am trying to run a code that will print a report to pdf and save in the specified folder, however I keep getting an error with the file name. Where am I going wrong?

Also, is there a way of adding a hyperlink to the folder in the message box?

Private Sub CmdPrintJob_Click()
Dim strWhere As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[ContractName] = """ & Me.[ContractName] & """"
        DoCmd.OpenReport "RPTJobSetUp", acViewPreview, , strWhere
    End If

Dim FileName As String
Dim FilePath As String

[COLOR="Red"]FileName = Me.ContractName
[/COLOR]FilePath = "J:\Job Set-Up Sheets\" & FileName & ".pdf"
DoCmd.OutputTo acOutputReport, "RPTJobSetUp", acFormatPDF, FilePath
Debug.Print FilePath
MsgBox "Your new job set-up has been successfully created and saved to J:\Job Set-Up Sheets ", vbInformation, "New Job Set-Up"

End Sub

 

[pbaldy]

What's in the textbox named ContractName? Offhand, a Null value will cause an error as a String variable can't hold Null. That control obviously has to exist as well.

 

[pbaldy]

Oh, and the error description is always handy.

 

[Tophan]

Hi.

The error description is Run-Time Error 2467: The expression you entered refers to an object that is closed or doesn't exist

The ContractName text box is a required field and primary key so there is always info in that field

 

[pbaldy]

Are you sure that's the name of the textbox, not the caption and not the field in the control source? The error implies that it doesn't exist on the form the code is in.

 

[Tophan]

Yes...definitely the textbox name and not a caption. I thought I was misspelling it and even copied it from the properties box and pasted into the code and still got an error

 

[pbaldy]

Can you attach the db here?

 

[Tophan]

The db won't upload...it is exceeding the max. file size and I am getting the below message

Your submission could not be processed because a security token was missing.

If this occurred unexpectedly, please inform the administrator and describe the action you performed before you received this error.

Would you accept a Dropbox link?

 

[pbaldy]

If it won't upload after compacting and zipping:

https://www.access-programmers.co.uk/forums/showthread.php?t=140587

you can email it to

pbaldy
gmail
com

 

[Tophan]

The database is 8MB even after the compress and repair. I will email it to you shortly.

Thanks.

 

[pbaldy]

Curious. I expected to find it had a different name, but you are correct. It seems to lose its identity between the line setting strWhere and that one. I put that line first and it seems to work fine:

Private Sub CmdPrintJob_Click()
Dim strWhere As String
Dim FileName As String
Dim FilePath As String

FileName = Me.ContractName
...

I'll have to ponder why this happens. By the way, the file would have been small enough to attach here if you zipped it.

 

[pbaldy]

Ah, I found the culprit. In the report that opens, you close this form in the open event and then open it again in the close event. Since that line was after the line opening the report, the form was "closed" even though its code was still running.

 

[Tophan]

Oh...I think I understand. So I should keep the form open but maybe minimize it, let the report run and save to the folder and on closing the report, maximize/restore the form?

 

[pbaldy]

You could toggle its visible property. That would also leave it on the record it was on before. As is, it comes back to a new record (though perhaps that's what you want). It's okay to close/open it, you just need to get any values needed off of it before you close it. The variable held its value, but trying to refer to the form control results in an error.

 

[Tophan]

I don't know how to toggle the visibility...sorry. I tried the minimize window and that seems to be working (sort of). No error messages and the file saved in the specified folder.

Is there a way to add a hyperlink to the folder in the message box?

 

[pbaldy]

I just mean you would set its Visible property to false and then back to true. I've never tried to put a hyperlink in the text, but offhand I doubt it would work. You could use a Yes/No message box that asks if the user wants to open the file, and open it if they answer yes.

 

[Tophan]

Ok thanks. Really appreciate your help.

 

[pbaldy]

Happy to help!

 

[Tophan]

Hi...Just giving you an update. So I made the changes to the code and added a couple lines to minimize the Job Set-Up form (see below in red).

The report opens and saves to the specified folder. On closing the report I created a simple dialog box that asks the user if they would like to view the file in the folder or if to return to the form.

Private Sub CmdPrintJob_Click()
Dim strWhere As String
Dim FileName As String
Dim FilePath As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[ContractName] = """ & Me.[ContractName] & """"
        DoCmd.OpenReport "RPTJobSetUp", acViewPreview, , strWhere
        [COLOR="Red"]DoCmd.SelectObject acForm, Me.Name, False
        DoCmd.Minimize
[/COLOR]    End If

FileName = Me.ContractName
FilePath = "J:\Job Set-Up Sheets\" & FileName & ".pdf"
DoCmd.OutputTo acOutputReport, "RPTJobSetUp", acFormatPDF, FilePath
Debug.Print FilePath
MsgBox "Your new job set-up has been successfully created and saved to J:\Job Set-Up Sheets ", vbInformation, "New Job Set-Up"

End Sub

 

[pbaldy]

Glad you got it working.