Ms Access Report IsNull (1 Viewer)

PatAccess · Jun 20, 2018

Good day Smart People,

Can someone please look at this code and tell me what I'm doing wrong? I'm getting error code 94: Invalid use of Null

Private Sub Label93_Click()
Dim Hyperlink As String
If IsNull(Hyperlink) Then
MsgBox "No Certificate Available"
DoCmd.Close
Exit Sub
End If
If Not IsNull(Hyperlink) Then
Hyperlink = Me.Cert.Value
Application.FollowHyperlink (Hyperlink)
End If
End Sub

Thank you

Mark_ · Jun 20, 2018

Code:

Private Sub Label93_Click()
Dim Hyperlink As String

   If IsNull(Hyperlink) Then
      MsgBox "No Certificate Available"
      DoCmd.Close
      Exit Sub
   End If
   If Not IsNull(Hyperlink) Then
      Hyperlink = Me.Cert.Value
      Application.FollowHyperlink (Hyperlink)
   End If
End Sub

To start, Hyperlink will always have no value when you first test it. I believe your first check would be

Code:

IF Me.Cert = "" THEN

Can you show which line generates the error?

In general I prefer using nz(), that way I can check for both zero length strings as well as null values.

Code:

IF nz(Me.Cert,"") = "" THEN

June7 · Jun 20, 2018

Hyperlink is declared as string. String variable cannot hold Null, only variant type can.

String variables initiate as empty string.

Orthodox Dave · Jun 21, 2018

Hi PatAccess,

The others are both right but there is more.

I would suggest an alias for Hyperlink. Although it is not an Access reserved word, they might make it one later so be safe. Let's call it HLink

Mark's point is that you are declaring a variable then testing it before you have even put anything into it.

And June7's point is that you are testing the variable with the wrong test.

So the first bit should be:

Code:

Private Sub Label93_Click()
Dim HLink As String

   IF nz(Me.Cert,"") = "" THEN
      MsgBox "No Certificate Available"
      DoCmd.Close
      Exit Sub
   End If

You don't now need to test IF nz(Me.Cert,"") <> "" because you would already have exited the sub by now if that wasn't the case.

All you need for the rest is:

Code:

HLink = Me.Cert.Value
      Application.FollowHyperlink (HLink)

End Sub

Mark_ · Jun 21, 2018

Orthodox Dave said:
All you need for the rest is:

Code:

HLink = Me.Cert.Value Application.FollowHyperlink (HLink) End Sub

Wouldn't need that much.

Code:

Application.FollowHyperlink (Me.Cert)

would do the same without requiring a string to be declared and filled.

PatAccess · Jun 21, 2018

Thank you All for your help. It works, the MsgBox/pdf are now showing BUT then I get these error messages:

The object doesn't Contain the Automation object 'Qry_Training/Certifications-Active.'

You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.
Check the component's documentation for information on the properties and methods it makes available for Automation operations

I have no idea what the problem is. When I created this Hyperlink link it through a builder window. and then I placed the code on the click event. What did I do wrong

Mark_ · Jun 21, 2018

Can you post your code?
You have a reference to "Qry_Training/Certifications-Active" some place.

PatAccess · Jun 21, 2018

The Report Data source is that Query, also originally I went to the tools Group then chose Hyperlink, I then went to the hyperlink builder and chose that Query for the Cert field so this is what is showing on the Hyperlink Builder - Base URL: =[Qry_Training/Certifications-Active]![Cert]

Was I supposed to add that Hyperlink command or was I supposed to put the code into my cert field?

PatAccess · Jun 21, 2018

Nevermind...I was making it difficult for myself. I had the code on the wrong field.

But anybody knows how I can change the name of the field in the report instead of it showing the file path?
Thank you so much for all the help

Mark_ · Jun 21, 2018

What data are you saving in "Cert" and what are you trying to display?
I am going to guess that "Cert" has the full path and file name.

PatAccess · Jun 21, 2018

Correct Mark, It has the full path and file name.

Mark_ · Jun 21, 2018

What are you trying to display then?

PatAccess · Jun 21, 2018

Just the word "Certificate"

Mark_ · Jun 21, 2018

When you inserted the hyperlink, did you notice the "Text to display" at the top?
As the hyperlink is really a label, you can always change the controls ".caption" property to be what ever you would like to appear.

PatAccess · Jun 21, 2018

Thank you very much for all the help. Everything is working here

Ms Access Report IsNull (1 Viewer)

PatAccess

Registered User.

Mark_

Longboard on the internet

June7

AWF VIP

Orthodox Dave

Home Developer

Mark_

Longboard on the internet

PatAccess

Registered User.

Mark_

Longboard on the internet

PatAccess

Registered User.

PatAccess

Registered User.

Mark_

Longboard on the internet

PatAccess

Registered User.

Mark_

Longboard on the internet

PatAccess

Registered User.

Mark_

Longboard on the internet

PatAccess

Registered User.

Similar threads

Users who are viewing this thread