Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Open Report (2003)

    We have a form which is used to record alloy samples sent for testing. Two controls on the form are Serial No, and Release Note No. The serial No is unique, but the Release Note No is not. When we get a report back, file name is in the format of M51694H226333862166. The last four digits are always the Serial No 2166, and the four before that, the release note no (3386). I want to put a button on the form, that will open the report from a specified location, based on the values of these two fields, without the operator having to type them in. The value of the Release Note No, could be in another record, and contained in another report, thats why I need both fields to match.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Open Report (2003)

    What kind of file is the report?
    How can we determine the name of the report if we only know the last 8 digits in a 19 characters long name?

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Report (2003)

    It's a pdf report.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Open Report (2003)

    Thanks. Could you also answer my other question, please?

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Report (2003)

    That is the problem! I want to illiminate the user having to type in the report number, otherwise it would be easy.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Open Report (2003)

    The following code will open the first file it finds that meets the specifications:
    <code>
    Private Sub cmdOpenReport_Click()
    ' Modify as needed, but keep trailing backslash
    Const strPath = "F:Reports"
    Dim strFile As String
    strFile = Dir(strPath & "*" & Me.[Release Note No] & Me.[Serial No] & ".pdf")
    If strFile = "" Then
    ' File not found, display warning
    MsgBox "No report found for this Release Note No and Serial No.", vbExclamation
    Else
    ' File found, open it in default PDF reader
    Application.FollowHyperlink strPath & strFile
    End If
    End Sub
    </code>
    cmdOpenReport is the name of the command button, and the constant strPath contains the complete path of the folder containing the PDF files, including a trailing backslash.
    Replace [Release Note No] and [Serial No] with the real names of the fields/controls.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Report (2003)

    Sorry about the delay, but only just got round to testing your code. It works brilliantly. Thanks again Hans.

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Report (2003)

    Because this worked so brilliantly, I was asked if I could do it for another set of reports, which I did, and again it worked. What I didn't realise until recently, is that the Release Note No could be duplicated. The report comes through with the suppliers own filename, so what I have been doing is saving it with the Release Note numbers it refers to, which in some cases is two numbers. This worked fine, but if there are two many items to fit on one report, they send another. So what I thought would work is changing the file name slightly. So "3385 3386.pdf", which opens the report, to "3385 3386(2).pdf", which doesn't. Ideally, I would like to open both reports at the same time, as a two page document, as they both relate to the same Release Note No's. The code I changed is

    strFile = Dir(strPath & "*" & Me.[HT Release Note No] & "*" & ".pdf")

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Open Report (2003)

    I'm getting confused. In the original question, the file name was a long string in which the Release Note No formed the next to last set of 4 characters. Now it appears to be a short name with another structure. If you want us to help you, you should provide clear, consistent and complete information.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Open Report (2003)

    BTW, combining two PDF files into one is beyond the scope of this forum. I know it can be done manually, but I have no idea whether it can be done on the fly using code.

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Report (2003)

    Sorry Hans. I thought that because the question was related, I would use the same thread. This is a different report, that when issued to us, the filename has no connection to what we need, so I am saving the report with what I thought was appropriate information. To make things clearer - the supplier sends us a report (4213) which contains information on numerous release notes (3385,3386,3387). Sometimes, because the release notes contain more tests that what will fit on one report, they send another (4214). What I am trying to do is, save the report with an appropriate file name that will open up the report. Perhaps I am trying to do the impossible.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Open Report (2003)

    If I understand you correctly, one PDF file can apply to multiple release notes, and multiple PDF files can relate to a single release note.

    You could use your idea of naming the files like "3385 3386.pdf", "3385 3386(2).pdf", etc. and change the code as follows:
    <code>
    Private Sub cmdOpenReport_Click()
    ' Modify as needed, but keep trailing backslash
    Const strPath = "F:Reports"
    Dim strFile As String
    strFile = Dir(strPath & "*" & Me.[HT Release Note No] & "*" & ".pdf")
    Do While Not strFile = ""
    ' Open file in default PDF reader
    Application.FollowHyperlink strPath & strFile
    ' Find the next one
    strFile = Dir
    Loop
    End Sub
    </code>
    This will loop through all PDF files whose name contains the release note number, and open them all. As I mentioned in my previous reply, I don't know how to combine the files into one PDF file.

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Report (2003)

    Thanks Hans. Combining the pdf's wasn't that important, but we have had staff that have seen one report with the relevant release note and thought that was it, so opening both reports will do just as well as combining them. Thanks again Hans. I'm sorry I didn't make my objective clear to start.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •