Solved Hyperlink opens wrong file

[chuckcoleman]

Hi, I'm stumped. My code does two things.

1. It creates a PDF copy of a report and places that in a folder
2. It adds to the [Attachments Table] a row for the customer ID, the Job ID, the Hyperlink and the RawFileName which is the text of the file name

When the code runs, it displays the report correctly. After I view the report, when I open a form which has a simple query of the files in the [Attachments Table], it shows them correctly. When I hover over the file I'm interested in opening, it shows the correct file name. However, when I click on the Hyperlink, it always opens the PREVIOUS file. I can't figure out why it is opening the previous file and not the one I clicked on. In the attached picture, if I click on the top record that ends in 48-36.pdf, it opens the previous file that ends in 45-11.pdf. I'm stumped.

Thoughts?

   Dim DateForQuote As String
   Dim DestPath As String
   Dim CustomersID As Integer
   Dim CustomerIDDetail As String
   Dim JobIDNbr As String
   Dim strSelectedFile As String
   Dim strSelectedFileSubDirectory As String
   Dim strHyperlinkFile As String
   Dim filename As String
   Dim srcFile As String
  
   On Error GoTo PrintPreviewQuote_Err
   DateForQuote = ""
   DateForQuote = Format(Now, "mm-dd-yyyy hh-mm-ss")
   MsgBox ("1-The DateForQuote is: " & DateForQuote)
    If IsNull(Me.JobIDX) Then
     MsgBox "There aren't any details for this job.  You have to enter information in the Description field to get a quote."
     Exit Sub
    Else
     Me.SelectedX = -1
     DoCmd.RunCommand acCmdSaveRecord
     srcFile = "Quote Report with Contacts"
     DestPath = "C:\Wood\Attachments\"
     CustomersID = Forms![Detail Form]![CustomerIDY]
     CustomerIDDetail = Forms![Detail Form]![CustomerIDX]
     JobIDNbr = Forms![Detail Form]![JobIDX]
     strSelectedFile = CustomerIDDetail & "-" & JobIDNbr & "-" & DateForQuote & ".pdf"
     MsgBox ("2-strSelectedFile is: " & strSelectedFile)
     strSelectedFileSubDirectory = "C:\Attachments" ' & LNameLookUp & "-" & [CustomerIDX] & "-" & [JobIDX] & ".pdf"
     strHyperlinkFile = "C:\Wood\Attachments\" & strSelectedFile
     ShowPDF = False
     filename = Application.CurrentProject.Path & strSelectedFileSubDirectory
     MsgBox ("3b-strHyperLinkFIle is: " & strHyperlinkFile)
     DoCmd.OutputTo acOutputReport, srcFile, "*.pdf", strHyperlinkFile, ShowPDF, "", 0, acExportQualityPrint
     DoCmd.SetWarnings False
     DoCmd.RunSQL "INSERT INTO [Attachments Table] (JobID, CustomerID, FileN, RawFileName) VALUES" _
     & "(' " & JobIDNbr & " ', ' " & CustomersID & " ', ' " & strSelectedFile & "#" & strHyperlinkFile & "#" & " ', ' " & strSelectedFile & " ')"
     DoCmd.SetWarnings True
     DoCmd.OpenReport "Quote Report with Contacts", acViewPreview
     DoCmd.SelectObject acForm, "Detail Form"
     DoCmd.RunCommand acCmdSaveRecord
     DoCmd.SelectObject acReport, "Quote Report with Contacts"
     Me.Text57.Requery
     Me.SelectedX = 0
    End If

 

[Gasman]

Have you tried walking through your code, line by line?

That is always my first place to start.

 

[Pat Hartman]

I must be missing something. I don't see a FollowHyperlink. Also, any particular reason you are not using a bound form?

 

[chuckcoleman]

Pat, I'm sorry for my delayed response, I was called away. I'm not familiar with FollowHyperLink although I looked a few examples. Can you please shed some light on that vs. what I have in my code? Also, the code I provided is from a command button on a form so I don't know if that addresses your question.

The really puzzling thing to me is that in the code when the DoCmd.Output runs and later it runs DoCmd.OpenReport that is based on a query, that is accurate. However, when I look the actual PDF that was created by using Windows Explorer, the file name is correct but the value of one of the fields displayed in the PDF is from a previous running of this code. I've even tried setting to "" the [DateForQuote] field at the start of the code. I'm confused.

 

[bastanu]

Can you please post the entire code; you have variables not declared in the piece you posted (ShowPDF, LNameLookUp)?

Here is an updated version of your code, see if it makes any difference:

Dim DateForQuote As String
Dim DestPath As String
Dim CustomersID As Long 'Integer safer to use long in case you go over the integer limit
Dim CustomerIDDetail As String
Dim JobIDNbr As String
Dim strSelectedFile As String
Dim strSelectedFileSubDirectory As String
Dim strHyperlinkFile As String
Dim filename As String
Dim srcFile As String
 
On Error GoTo PrintPreviewQuote_Err
  
DateForQuote = ""
DateForQuote = Format(Now(), "mm-dd-yyyy hh-mm-ss")

MsgBox ("1-The DateForQuote is: " & DateForQuote)

If IsNull(Me.JobIDX) Then
    MsgBox "There aren't any details for this job.  You have to enter information in the Description field to get a quote."
    Exit Sub
Else
    Me.SelectedX = -1
    'DoCmd.RunCommand acCmdSaveRecord 'not very reliable, use Dirty=False
    me.Dirty=False
    srcFile = "Quote Report with Contacts"
    DestPath = "C:\Wood\Attachments\"
    CustomersID = Forms![Detail Form]![CustomerIDY]
    CustomerIDDetail = Forms![Detail Form]![CustomerIDX]
    JobIDNbr = Forms![Detail Form]![JobIDX]
    
    strSelectedFile = CustomerIDDetail & "-" & JobIDNbr & "-" & DateForQuote & ".pdf"
    MsgBox ("2-strSelectedFile is: " & strSelectedFile)
    
    strSelectedFileSubDirectory = "C:\Attachments" ' & LNameLookUp & "-" & Me.[CustomerIDX] & "-" & Me.[JobIDX] & ".pdf"  'this is a different folder than DestPath - missing the Wood subfolder; also added Me. to CustomerIDX and JobIDX
    strHyperlinkFile = "C:\Wood\Attachments\" & strSelectedFile
    
    ShowPDF = False
    filename = Application.CurrentProject.Path & strSelectedFileSubDirectory
    
    MsgBox ("3b-strHyperLinkFIle is: " & strHyperlinkFile)
    
    DoCmd.OutputTo acOutputReport, srcFile, "*.pdf", strHyperlinkFile, ShowPDF, "", 0, acExportQualityPrint
    
    'replace the following lines with db.execute to catch any potential errors
    'DoCmd.SetWarnings False
    'DoCmd.RunSQL "INSERT INTO [Attachments Table] (JobID, CustomerID, FileN, RawFileName) VALUES" _
    ' & "(' " & JobIDNbr & " ', ' " & CustomersID & " ', ' " & strSelectedFile & "#" & strHyperlinkFile & "#" & " ', ' " & strSelectedFile & " ')"
    'DoCmd.SetWarnings True
    CurrentDb.Execute "INSERT INTO [Attachments Table] (JobID, CustomerID, FileN, RawFileName) VALUES" _
    ' & "(' " & JobIDNbr & " ', ' " & CustomersID & " ', ' " & strSelectedFile & "#" & strHyperlinkFile & "#" & " ', ' " & strSelectedFile & " ')",dbFailOnError
    
    DoCmd.OpenReport "Quote Report with Contacts", acViewPreview
    DoCmd.SelectObject acForm, "Detail Form"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SelectObject acReport, "Quote Report with Contacts"
    Me.Text57.Requery
    Me.SelectedX = 0
End If

 

[chuckcoleman]

Hi Bastanu,

The LNameLookUp and ShowPDF were after a ' (remark) so they were remarked out. I will look at your revisions after I post this and give you an update. Thank you!

Chuck

Private Sub PrintPreviewQuote_Click()
   Dim DateForQuote As String
   Dim DestPath As String
   Dim CustomersID As Integer
   Dim CustomerIDDetail As String
   Dim JobIDNbr As String
   Dim strSelectedFile As String
   Dim strSelectedFileSubDirectory As String
   Dim strHyperlinkFile As String
   Dim filename As String
   Dim srcFile As String
  
   On Error GoTo PrintPreviewQuote_Err
    DateForQuote = ""
    DateForQuote = Format(Now, "mm-dd-yyyy hh-mm-ss")
    If IsNull(Me.JobIDX) Then
     MsgBox "There aren't any details for this job.  You have to enter information in the Description field to get a quote."
     Exit Sub
    Else
     Me.SelectedX = -1
     DoCmd.RunCommand acCmdSaveRecord
     srcFile = "Quote Report with Contacts"
     DestPath = "C:\Wood\Attachments\"
     CustomersID = Forms![Detail Form]![CustomerIDY]
     CustomerIDDetail = Forms![Detail Form]![CustomerIDX]
     JobIDNbr = Forms![Detail Form]![JobIDX]
     strSelectedFile = CustomerIDDetail & "-" & JobIDNbr & "-" & DateForQuote & ".pdf"
     strSelectedFileSubDirectory = "C:\Attachments"
     strHyperlinkFile = "C:\Wood\Attachments\" & strSelectedFile
     ShowPDF = False
     filename = Application.CurrentProject.Path & strSelectedFileSubDirectory
     DoCmd.OutputTo acOutputReport, srcFile, "*.pdf", strHyperlinkFile, ShowPDF, "", 1, acExportQualityPrint
     DoCmd.SetWarnings False
     DoCmd.RunSQL "INSERT INTO [Attachments Table] (JobID, CustomerID, FileN, RawFileName) VALUES" _
     & "(' " & JobIDNbr & " ', ' " & CustomersID & " ', ' " & strSelectedFile & "#" & strHyperlinkFile & "#" & " ', ' " & strSelectedFile & " ')"
     DoCmd.SetWarnings False
     DoCmd.OpenReport "Quote Report with Contacts", acViewPreview
     Me.SelectedX = 0
     DoCmd.SelectObject acForm, "Detail Form"
     DoCmd.RunCommand acCmdSaveRecord
     Me.Text57.Requery
    End If
    
PrintPreviewQuote_Exit:
    Exit Sub

PrintPreviewQuote_Err:
    Dim Msg As String
    Msg = Err.Number & ": " & Err.Description & " Let Chuck know-Number 17"
    MsgBox Msg
    Resume PrintPreviewQuote_Exit
End Sub

 

[chuckcoleman]

I changed the code per your suggestion. No change. The display name of the PDF that's created is accurate but the data in the PDF is from a PDF that was created earlier. Below you see the Quote Nbr that ends in 50-50. If you look at the second screenshot that I captured by clicking in Windows Explorer the PDF that end in 50-50, you will see the Quote Nbr now ends in 49-41 which was the second PDF in the Saved File Locations dialog box, (same as in Windows Explorer). YIKES!

======================================================================

===========================================================================

r.

 

[bastanu]

Then you need to inspect the report and specifically its record source, it seems like the parameter used in that is not being refreshed. Can you post a pic or the SQL of the recordsource?

Cheers,

 

[chuckcoleman]

If I manually open the report, it’s fine. It’s the PDF that is created that’s the problem. The PDF’s file name is correct but the Quote Nbr in the PDF is incorrect. The code to create the PDF and the code to append the PDF file name to the Attachment Table is in my code. I just can’t figure out how the quote nbr in the PDF is changed.

 

[bastanu]

That is my point, it is the report itself that is the problem. I shouldn't say the report only, it is probably what feeds the report. Without seeing the report we have no way of helping you, so if you can't provide a small db sample with the report at least show us the report's record source, the report in design view with some of the pertinent properties showing (such as the quote # textbox control source).....

Cheers,

 

[Gasman]

If I manually open the report, it’s fine. It’s the PDF that is created that’s the problem. The PDF’s file name is correct but the Quote Nbr in the PDF is incorrect. The code to create the PDF and the code to append the PDF file name to the Attachment Table is in my code. I just can’t figure out how the quote nbr in the PDF is changed.

So the data in the pdf file named 50-50 is actually the data for 49-41? NOT 50-50 ?

Tip: If you use the same data for each record, it is very hard to determine if everything is correct?

 

[Pat Hartman]

You were setting a field you called hyperlink and that is the name of your thread but were not actually using a hyperlink. That has a different meaning. You were just using the variable as an argument to the OutputTo method which is totally different from following a hyperlink.

Try closing the report after the OutputTo method. You don't want to leave it open anyway and that may be what is causing the problem with the OutputTo. It may be picking up some other report if there are multiples open in memory.

 

[chuckcoleman]

Thank you everyone for contributing. I finally got it working. First, I added a new field to the underlying table and called that field, RawFileName. Then, early in the code I declare "DateForQuote". As soon as I establish it, which is a combination of a job number, date and time, I set the new field equal to the just established value and save it to the table. Now things work as I need them to. Thank you all.