• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 59
  • Last Modified:

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?
0
Blue Fin
Asked:
Blue Fin
  • 3
  • 3
1 Solution
 
Dale FyeCommented:
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 FyeCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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 FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now