Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink Trap (A2000)

    I asked some time ago about trapping a Hyperlink error, and one suggestion was:-...

    "...Add code to the Click event of the txt box on your form that is bound to your hyperlink field like this :-...."

    If IsNull(Me!txtFileName) Then
    MsgBox ("Field is empty")
    End If


    What i would like to do, is when the user clicks on the hyperlink (in this case, the hyperlink just opens a word or excel document on the local intranet) if the hyperlink FAILS, (for whatever reason) and doesn't open the document, then add that document name to a
    table for manual correction.

    The above code, added to my onclick event of my subform that displays the hyperlink, appears to just trap normal regular errors,a nd not 'windows' file not found type of errors.

    I've searched the forum, and a few dozen access sites since my original post but nada <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

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

    Re: Hyperlink Trap (A2000)

    Steve,

    You could check whether the file exists:

    Dim strSQL As String
    If Dir(Me!txtFileName) = "" Then
    ' There is no file with this path/name
    strSQL = "INSERT INTO tblFileErrors (FileName) Values (" & _
    Chr(34) & Me!txtFileName & Chr(34) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
    End If

    The two occurrences of Chr(34) put quotes around the problem file name.

    To trap the case that the file exists, but can't be opened, is more difficult. Following a hyperlink is handled by Windows/Browser, not by Access. If it is important to you to handle errors in opening a file, consider using the Windows API function ShellExecute to open the documents, This function returns a value that indicates success/failure, so you can use it like in the above example.

    If you are interested, do a search in this forum for ShellExecute; you will find several examples.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink Trap (A2000)

    Hans,
    I had a bit of a problem with the first part of the code, for the hyperlink it was returning the document name, and then the hyperlink.
    for example,

    DOCUMENT NAME#R:/path/folder/file.doc#

    including the hashes.. so what i did is shown below, to strip out the rubbish so that DIR would work ok.

    -------------------------------------------------------------------------------------------------------------------------
    Private Sub Document_Name_Click()
    On Error Resume Next

    Dim strSQL As String, doc_name As String, lgt As Integer, doc_loc As String, doc_loc2 As String
    Dim lp As Integer, pos As Integer, doc_number As String


    doc_loc = (Me![Document Link])
    doc_number = (Me![Reference Number])

    pos = 0
    For lp = 1 To Len(doc_loc) - 1
    If Mid(doc_loc, lp, 1) = "#" Then pos = lp
    Next lp
    doc_loc2 = Mid(doc_loc, pos + 1)
    lgt = Len(doc_loc2)
    doc_loc2 = Left(doc_loc2, lgt - 1)

    If Dir(doc_loc2) = "" Then
    strSQL = "INSERT INTO tblFileErrors ( Problem ) Values (" & doc_number & ")"
    CurrentDb.Execute strSQL, dbFailOnError

    MsgBox strSQL
    End If

    End Sub
    -------------------------------------------------------------------------------------------------------------------------

    Well, it might not be elegant, but it appears to work ok, up to the SQL bit....

    The SQL part just wont append what i need into the table, at the moment it basically does nothing.

    I'm assuming that where it says 'Problem', thats the name of the field in tblFileErrors table, and doc_number is the text that i want appended into it.

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

    Re: Hyperlink Trap (A2000)

    Steve,

    As far as I can see, you should exit the loop to determine pos as soon as you find a hash character. Otherwise, the loop continues and pos will be the position of the last hash character. But VBA has a function that makes this much easier: InStr finds the first position of a string in another string. This can replace the entire For ... Next loop:

    pos = InStr(doc_loc, "#")
    doc_loc2 = Mid(doc_loc, pos + 1)
    lgt = Len(doc_loc2)
    doc_loc2 = Left(doc_loc2, lgt - 1)

    To see what goes wrong, you can put a stop at the start of Document_Name_Click (click in the left margin; you should see a brown/dark red bullet to the left of the instruction). When you open the form and click the button, the code will pause here. You can execute the code step by step by pressing F8. You can inspect the value of variables by hovering the mouse over them.

    You can also replace the instruction <font face="Georgia">CurrentDb.Execute strSQL, dbFailOnError</font face=georgia> temporarily by <font face="Georgia">DoCmd.RunSQL strSQL</font face=georgia>. This uses the Access interface instead of DAO to execute strSQL. You should get a confirmation message "You are about to append ... records" etc.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink Trap (A2000)

    Hans,
    The code up to the append part is working fine, it never reaches the second hash because of :-

    For lp = 1 To Len(doc_loc) - 1

    The minus one makes sure it never reaches the second hash.. Like i said its not elegant, I thank you for the Instr command tip though, much more efficient....

    anyway, the problem is that its not appending anything to the table, nada...
    I set up a messagebox to show me what the Sql string is saying, and i get exactly this text (that does nothing):-

    INSERT INTO tblFileErrors ( Problem ) Values (AD-001)

    I tried the second option you suggested using DoCmd, but this was exactly the same, so i'm guessing that the SQL part is slightly wrong.

    Code Now:-
    ------------------------------------------------------------------------------------------------------------------------------

    <font color=blue>Private Sub Document_Name_Click()
    On Error Resume Next
    Dim strSQL As String, doc_name As String, lgt As Integer, doc_loc As String, doc_loc2 As String
    Dim pos As Integer, doc_number As String

    doc_loc = (Me![Document Link])
    doc_number = (Me![Reference Number])

    pos = InStr(doc_loc, "#")
    doc_loc2 = Mid(doc_loc, pos + 1)
    lgt = Len(doc_loc2)
    doc_loc2 = Left(doc_loc2, lgt - 1)

    If Dir(doc_loc2) = "" Then
    strSQL = "INSERT INTO tblFileErrors ( Problem ) Values (" & doc_number & ")"
    'CurrentDb.Execute strSQL, dbFailOnError ' can use this command instead.
    DoCmd.RunSQL strSQL
    MsgBox strSQL
    End If

    End Sub</font color=blue>
    ------------------------------------------------------------------------------------------------------------------------------


    <font color=red> Update:</font color=red> This code gives me a data box on running with the letters AD in it.. "AD-001" is the doc_number (string) above. whatever i put in the box WILL get added to tblFileErrors. so it looks like the problem is the doc_number syntax (I took the chr(34)'s out as originally asked for, figuring they were only for explicit field name types.
    Ho-Hum time to get home - try again tomorrow!

    <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> <img src=/S/clown.gif border=0 alt=clown width=15 height=15> <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/please.gif border=0 alt=please width=31 height=23>

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

    Re: Hyperlink Trap (A2000)

    Steve,

    When I saw that you had taken out the Chr(34)'s, I assumed that doc_number had a numeric value, even if you declared it as a string. Now it turns out it is a string indeed, and hence you MUST surround the value in quotes - Chr(34) is a double quote ".

    Try

    strSQL = "INSERT INTO tblFileErrors ( Problem ) Values (" & Chr(34) & doc_number & Chr(34) & ")"

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink Trap (A2000)

    Thanks Hans, All working. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    <font color=blue> Summary:</font color=blue>


    The purpose of the above posts, is to detect when a hyperlink to a file does not work. adding the name of the file to a table for future analysis.
    You will still get the windows error message saying that the file could not be found etc. but you could add a messagebox stating that the error has been noted.

Posting Permissions

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