Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    321
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Set Hyperlink with Code (2003 SP2)

    I have a situation that I will be facing shortly and don't have any idea if it's possible or how to go about it. We will soon be scanning all our orders that are received by mail to a .tif or .pdf file and then hyperlink that letter to the corresponding order in Access. We use a 6-digit order number which I am planning on being the first part of the file name of the order.

    Can anyone tell me, is it possible to have a macro or run some code that will automatically search for the first 6 digits of a file name and assign a hyper link to a record, or do I need some specialized software for that?

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

    Re: Set Hyperlink with Code (2003 SP2)

    It would be easiest if all files are stored in a single, fixed folder, and if the filename is simply the order number followed by the extension .tif (or .pdf; all files should be of the same type), if you prefer with a fixed part before or after the order number. In that case, you don't need to store a hyperlink in the table, you can place a command button on the form used to view/edit the orders with code like this:

    Private Sub cmdDocument_Click()
    On Error GoTo ErrHandler
    Application.FollowHyperlink "Cocs" & Me.OrderNumber & ".tif"
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Modify as needed.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    321
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Hyperlink with Code (2003 SP2)

    Thanks, HansV. We haven't discussed exactly what our naming convention will be, I initially had thought I would include the person's name and possibly state/country in the name, but I can see what you mean that just the order number for the file name would be the simplest. Your suggestion gives me something to go off of. I'm hoping within the next month to implement that in our database. I may be checking back about this issue.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    321
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Hyperlink with Code (2003 SP2)

    Edited by HansV to replace bitmap in zip file with .png version of same

    Good morning, (Kansas time anyway!). I did a little work on that and it seems to work fine. The problem I am running into is that I get this message from Microsoft Office every time. I tried using DoCmd.SetWarnings False, but that doesn't do it. Will I always have to OK opening a file?

    Another problem I run into is that our scanner automatically adds "0001" to the file name. I don't think there is a setting to change that. Is there a way to have the code look for the left 6 characters in the file name?
    Attached Images Attached Images

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

    Re: Set Hyperlink with Code (2003 SP2)

    See How to disable hyperlink warning messages in Office 2003 about the warning.

    If the filename always has .0001 tacked on, you could use something like

    Application.FollowHyperlink "Cocs" & Me.OrderNumber & "0001.tif"

    It it is variable, it becomes more complicated.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    321
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Hyperlink with Code (2003 SP2)

    Thanks for that link on Security and the hint on adding 0001 in front of .tif. That part works.

    Now my problem is, in talking it over with my superintendent, we feel like it is quite important to use the OrderID and the person's name in the file name, for the reason that we won't always be searching for a file through the database. I realize that will be trickier, but how tricky? I think we're settled that the OrderID would be the first 6 characters. Example: John Brown from New York. Order Number 820537. File name: 820537johnbrown.tif or 820537_johnbrown.tif.

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

    Re: Set Hyperlink with Code (2003 SP2)

    You'd use something like

    Application.FollowHyperlink "Cocs" & Me.OrderNumber & "_" & Me.FirstName & Me.LastName & ".tif"

    where the & "_" is to be used only if you include an underscore in the file name.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    321
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Hyperlink with Code (2003 SP2)

    Works. Thanks.

Posting Permissions

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