Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Mar 2013
    Posts
    26

    Talking Unanswered: Opening files using a Button solved.

    I have used the google maps code to open image files on my harddrive using field values.
    It could be used for any file type as required, just change the file extension from .jpg.

    It works perfectly unless there is no file named as per the feilds, then I get and error Single Step Macro; Err No 31665.

    How can I get an error msg box to appear with "No file this name!" instead of the macro error??

    Here is the code I adjusted to use;

    Function OpenJPG(Address, City, State, Zip, Country)

    Dim strAddress As String
    strAddress = Nz(Address)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(City)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(State)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Zip)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Country)
    strAddress = strAddress & IIf(strAddress = "", "", ".jpg")
    If strAddress = "" Then
    MsgBox "There is no address to map."
    Else
    Application.FollowHyperlink "C:\Users\MyDocs\Pictures\" & strAddress
    End If

    End Function

  2. #2
    Join Date
    Mar 2013
    Posts
    26
    Its OK, I've worked it out. Its as follows;

    Function OpenJPG(Address, City, State, Zip, Country)

    On Error GoTo EndNow

    Dim strAddress As String
    strAddress = Nz(Address)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(City)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(State)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Zip)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Country)
    strAddress = strAddress & IIf(strAddress = "", "", ".jpg")
    If strAddress = "" Then
    MsgBox "There is no address to map."
    Else
    Application.FollowHyperlink "C:\Users\MyDocs\Pictures\" & strAddress
    End If

    Exit Function

    EndNow:
    MsgBox "There is no PDF", vbInformation, "No PDF Found!"

    End Function

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could have spared the use of an error trapping mechanism:
    Code:
    If strAddress = "" Then
        MsgBox "There is no address to map."
    ElseIf Len(Dir(strAddress)) = 0 Then
        MsgBox "There is no PDF", vbInformation, "No PDF Found!"
    Else
        Application.FollowHyperlink "C:\Users\MyDocs\Pictures\" & strAddress
    End If
    Have a nice day!

  4. #4
    Join Date
    Mar 2013
    Posts
    26
    Quote Originally Posted by Sinndho View Post
    You could have spared the use of an error trapping mechanism:
    Code:
    If strAddress = "" Then
        MsgBox "There is no address to map."
    ElseIf Len(Dir(strAddress)) = 0 Then
        MsgBox "There is no PDF", vbInformation, "No PDF Found!"
    Else
        Application.FollowHyperlink "C:\Users\MyDocs\Pictures\" & strAddress
    End If
    Sinndho,
    This is exactly what I was looking for.

    Thank you for taking the time to reply to this post.

    Kind regards, Kerrmuir.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Mar 2013
    Posts
    26
    Sinndho,
    Your method did not work for me. It just gives the error message box you made every time whether there is a target file or not. ...?

    Tried to alter the code but same result. :-(

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Dir() function needs the full path. If this full path is not uncluded in strAddress, then you must use:
    Code:
    ElseIf Len(Dir("C:\Users\MyDocs\Pictures\" & strAddress) ) = 0 Then
    Have a nice day!

  8. #8
    Join Date
    Mar 2013
    Posts
    26
    What is the benifit of using this method opposed to the error trapping method?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Call it a programming style if you want, but I tend not to rely on error trapping mechanisms in the normal execution flow of a program. An error is an exception, something that the programmer was not able to foresee and that the program cannot handle. In this case, it's easy enough to test for the existence of a file before trying to open it.
    Have a nice day!

  10. #10
    Join Date
    Mar 2013
    Posts
    26
    I see. ;-)
    I have used this now and it works a treat, thanks again for your help and advice.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  12. #12
    Join Date
    Mar 2013
    Posts
    26
    Heres one to test your metal...
    Is there a way to show that the link has a file in existence before executing the comand. This would be good to save clicking on it jjust to get the message box that says there is no file to fetch.
    What I mean is if there is no file the link would be greyed out or something, and if there is a file of that name the button would look different in some way.
    It could even be just a text hyperlink that is a different colour.
    Let me know what ideas you may have to do this..

    Kind Regards.
    Last edited by kerrmuir; 03-31-13 at 17:34.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so test for existence of the file
    you can do it using the file scripting object
    There may be another method within Access but Im not certain.

    one thing to bear in mind with FSO is that it can annoy some AV programs as they can interpret FSO activity as possible malicious
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Mar 2013
    Posts
    26
    The script above tests for existence of the file but only once you action it.
    What I would like is some kind if indication on the form whether or not there is a file for a record by just looking at its state.

    I realise this may be a bit much to ask for but it would be such a cool feature and would help the database users greatly.

    Thanks again for taking your time to respond to this post.
    Kerrmuir.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you call the function OpenJPG(Address, City, State, Zip, Country), where do Address, City, State, Zip, Country parameters come from?

    If they are values extracted from controls on a form (or from fields in that form RecordSet) and if the function is called when you click on a command button, it would be fairly easy to check whether the file exists or not in the Form_Current event. Depending on the result of the test, the command button could be enabled or disabled.
    Have a nice day!

Posting Permissions

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