If Then Else Statement NOT working

Hi,
The following code is for "On Got Focus" event of a form which list the PDF Document Reference Titles in sequential order.

CODE:
Private Sub cmdShowRef_GotFocus()
    If IsNull(txtPDFScanLoc) Then
            MsgBox "Document is not available due to unknown filename/path."
    Else
            cmdShowRef.HyperlinkAddress = txtPDFScanLoc
    End If
End Sub

Example: The form in question lists the PDF Document Titles in a sequential list as shown below:

1. Title for Document A
2. Title for Document B
3. Title for Document C
and so on

When the user hovers the mouse pointer over the form (i.e. the list of PDF Document Titles) the mouse pointer changes to hand and when the user clicks over any of the listed document title given the hyperlink for that PDF document exist or populated in the database (i.e. txtPDFScanLoc field holds the path for the PDF Dcoument) the PDF document for the listed Title is opened otherwise a message is displayed.

The issue is when there is no hyperlink populated in the DB for any one of the listed PDF Document Titles on the form (i.e. IsNull(txtPDFScanLoc) computes to TRUE, the desired message is displayed but once you click OK button to close the message dialog box the last opened PDF document opens which is NOT intended.

Example: User first clicks over 1. Title for Document A, since the hyperlink is populated which links to the PDF document the PDF document is opened. But when the user clicks over 2. Title for Document B, whose hyperlink is not populated (i.e. IsNull(txtPDFScanLoc) computes to TRUE with desired message box but once the user clicks OK button to close the message dialog box, the last opened PDF document opens again (i.e. for the 1. Title for Document A).

Logically the code should jumps to End If and should NOT check Else condition...Is there a way for the code to exit out without stepping into Else condition?
Blue FinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
rather that setting the hyperlink address of the button, you should use the Application.FollowHyperlink method:

Private Sub cmdShowRef_GotFocus()
    If IsNull(txtPDFScanLoc) Then
            MsgBox "Document is not available due to unknown filename/path."
    Else
            application.FollowHyperlink me.txtPDFScanLoc
    End If
End Sub

Open in new window

What is happening now, is when you find one that has the hyperlink you are setting the hyperlink address associated with the button, but you never "un-set" that value, so it is retained from the previous record.  You could probably also resolve the problem by adding:

cmdShowRef.HyperlinkAddress = NULL

to the first part of the IF statement.
0
Blue FinAuthor Commented:
Hi Dale,

After implementing the modification to the code you suggested the following is happening:

Updated Code:
Private Sub cmdShowRef_GotFocus()
    If IsNull(txtPDFScanLoc) Then
            MsgBox "Document is not available due to unknown filename/path."
            'cmdShowRef.HyperlinkAddress = Null
    Else
            'cmdShowRef.HyperlinkAddress = txtPDFScanLoc
            Application.FollowHyperlink Me.txtPDFScanLoc
    End If
End Sub

Observations:
1. 'cmdShowRef.HyperlinkAddress = Null is giving me invalid use of Null
2. By using Application.FollowHyperlink Me.txtPDFScanLoc, now NO PDF document opens if no hyperlink exist but now the pointer does not change to the hand when I hover over the form.
3. Also the button which opens the FORM that lists the PDF Document Titles for the user to click now opens the PDF Document associated with the 1. Title for Document A. I tried to close the form and again once I open the form the PDF Document associated with the 1. Title for Document A opens up...i.e. by default the hyperlink associated with first PDF Document Title opens.

Code which Opens the FORM which lists the Titles:
Private Sub cmdViewRef_Click()
On Error GoTo Err_cmdViewRef_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "SbfViewReference"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewRef_Click:
    Exit Sub

Err_cmdViewRef_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewRef_Click
   
End Sub

Thanks
0
Dale FyeOwner, Developing Solutions LLCCommented:
Start out by opening the form, and changing the HyperlinkAddress of the button (delete what is there).

If you want the hand, then you will have to use the HyperlinkAddress property of the button.

Instead of setting it to NULL, try setting it to an empty string:

cmdShowRef.HyperlinkAddress = ""
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Blue FinAuthor Commented:
Thanks Dale!!!

The following code works as intended, cmdShowRef.HyperlinkAddress = " " does the trick.

CODE:
Private Sub cmdShowRef_GotFocus()
    If IsNull(txtPDFScanLoc) Then
            MsgBox "Document is not available due to unknown filename/path."
            cmdShowRef.HyperlinkAddress = ""
    Else
            cmdShowRef.HyperlinkAddress = txtPDFScanLoc
    End If
End Sub
0
Dale FyeOwner, Developing Solutions LLCCommented:
Blue Fin,

It appears that you selected your own comment as the answer to your question.

Click the "Report Question" text at the bottom left of your original post.  Request that the admins reopen the question so that you can select the correct response as the answer to your question.
0
Blue FinAuthor Commented:
Apologies Dale

This time around I selected you response/comment.

Please let me know if you want me to do anything further to rectify this issue.

Regards:
Blue Fin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.