Invalid Use of Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having some problems with some VBA Code. I'm not very familiar with VBA,
so I'm hunting and pecking to get things to work. I have a button on a form
to check whether the values entered by the user exist in the DB or not. If
it exists, then current values from the DB will be displayed in a text box.
If it doesn't exist, I want to make a new command button visible. This is
the part that is always creating the "Invalid Use of Null" error. Any help
would be greatly appreciated.

Private Sub CheckDomain_Click()
Dim DNS As String, DI As Integer
DNS = DLookup("[TargetDNSName]", "[dbo_TargetDomain]", "[TargetDNSName]='"
& Nz(Me.NewDomainName, " ") & "'")
DI = DLookup("[DomainID]", "[dbo_TargetDomain]", "[TargetDNSName]='" &
Nz(Me.NewDomainName, 0) & "'")
If DNS = " " Then
Me.Submit.Visible = True
Else: Me.DomainIDs = DNS & " DI: " & DI
End If
End Sub
 
Hi Christopher,

you need to wrap the dLookup results in NZ also...

DNS = nz(DLookup(...),"")

if nothing is found, an error will occur when you try to make the assignment


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Christopher Robin said:
I'm having some problems with some VBA Code. I'm not very familiar
with VBA, so I'm hunting and pecking to get things to work. I have a
button on a form to check whether the values entered by the user
exist in the DB or not. If it exists, then current values from the
DB will be displayed in a text box. If it doesn't exist, I want to
make a new command button visible. This is the part that is always
creating the "Invalid Use of Null" error. Any help would be greatly
appreciated.

Private Sub CheckDomain_Click()
Dim DNS As String, DI As Integer
DNS = DLookup("[TargetDNSName]", "[dbo_TargetDomain]",
"[TargetDNSName]='" & Nz(Me.NewDomainName, " ") & "'")
DI = DLookup("[DomainID]", "[dbo_TargetDomain]", "[TargetDNSName]='"
& Nz(Me.NewDomainName, 0) & "'")
If DNS = " " Then
Me.Submit.Visible = True
Else: Me.DomainIDs = DNS & " DI: " & DI
End If
End Sub
Click to expand...

If Either of those DLookups doesn't find a match, it will return Null,
which can't be assigned directly to a String variable (e.g., DNS), or an
Integer variable (e.g., DI). You could get around that in several ways.
Probably the easiest would be to use Variant variables instead of a
String and an Integer:

'----- start of revised code -----
Private Sub CheckDomain_Click()

Dim DNS As Variant ' String or Null
Dim DI As Variant ' Integer or Null

DNS = DLookup( _
"[TargetDNSName]", _
"[dbo_TargetDomain]", _
"[TargetDNSName]='" & _
Nz(Me.NewDomainName, " ") & _
"'")

DI = DLookup( _
"[DomainID]", _
"[dbo_TargetDomain]", _
"[TargetDNSName]='" & _
Nz(Me.NewDomainName, 0) & _
"'")

If IsNull(DNS) Then
Me.Submit.Visible = True
Else
Me.DomainIDs = DNS & " DI: " & DI
End If

End Sub
'----- end of revised code -----
 
Hi - thanks for the responses. I will give them a try at work tomorrow.

Dirk Goldgar said:
Christopher Robin said:
I'm having some problems with some VBA Code. I'm not very familiar
with VBA, so I'm hunting and pecking to get things to work. I have a
button on a form to check whether the values entered by the user
exist in the DB or not. If it exists, then current values from the
DB will be displayed in a text box. If it doesn't exist, I want to
make a new command button visible. This is the part that is always
creating the "Invalid Use of Null" error. Any help would be greatly
appreciated.

Private Sub CheckDomain_Click()
Dim DNS As String, DI As Integer
DNS = DLookup("[TargetDNSName]", "[dbo_TargetDomain]",
"[TargetDNSName]='" & Nz(Me.NewDomainName, " ") & "'")
DI = DLookup("[DomainID]", "[dbo_TargetDomain]", "[TargetDNSName]='"
& Nz(Me.NewDomainName, 0) & "'")
If DNS = " " Then
Me.Submit.Visible = True
Else: Me.DomainIDs = DNS & " DI: " & DI
End If
End Sub
Click to expand...

If Either of those DLookups doesn't find a match, it will return Null,
which can't be assigned directly to a String variable (e.g., DNS), or an
Integer variable (e.g., DI). You could get around that in several ways.
Probably the easiest would be to use Variant variables instead of a
String and an Integer:

'----- start of revised code -----
Private Sub CheckDomain_Click()

Dim DNS As Variant ' String or Null
Dim DI As Variant ' Integer or Null

DNS = DLookup( _
"[TargetDNSName]", _
"[dbo_TargetDomain]", _
"[TargetDNSName]='" & _
Nz(Me.NewDomainName, " ") & _
"'")

DI = DLookup( _
"[DomainID]", _
"[dbo_TargetDomain]", _
"[TargetDNSName]='" & _
Nz(Me.NewDomainName, 0) & _
"'")

If IsNull(DNS) Then
Me.Submit.Visible = True
Else
Me.DomainIDs = DNS & " DI: " & DI
End If

End Sub
'----- end of revised code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Hi Dirk,

Your suggestion worked great. My command button is now working as intended.
Thank you very much.

Chris

Dirk Goldgar said:
Christopher Robin said:
I'm having some problems with some VBA Code. I'm not very familiar
with VBA, so I'm hunting and pecking to get things to work. I have a
button on a form to check whether the values entered by the user
exist in the DB or not. If it exists, then current values from the
DB will be displayed in a text box. If it doesn't exist, I want to
make a new command button visible. This is the part that is always
creating the "Invalid Use of Null" error. Any help would be greatly
appreciated.

Private Sub CheckDomain_Click()
Dim DNS As String, DI As Integer
DNS = DLookup("[TargetDNSName]", "[dbo_TargetDomain]",
"[TargetDNSName]='" & Nz(Me.NewDomainName, " ") & "'")
DI = DLookup("[DomainID]", "[dbo_TargetDomain]", "[TargetDNSName]='"
& Nz(Me.NewDomainName, 0) & "'")
If DNS = " " Then
Me.Submit.Visible = True
Else: Me.DomainIDs = DNS & " DI: " & DI
End If
End Sub
Click to expand...

If Either of those DLookups doesn't find a match, it will return Null,
which can't be assigned directly to a String variable (e.g., DNS), or an
Integer variable (e.g., DI). You could get around that in several ways.
Probably the easiest would be to use Variant variables instead of a
String and an Integer:

'----- start of revised code -----
Private Sub CheckDomain_Click()

Dim DNS As Variant ' String or Null
Dim DI As Variant ' Integer or Null

DNS = DLookup( _
"[TargetDNSName]", _
"[dbo_TargetDomain]", _
"[TargetDNSName]='" & _
Nz(Me.NewDomainName, " ") & _
"'")

DI = DLookup( _
"[DomainID]", _
"[dbo_TargetDomain]", _
"[TargetDNSName]='" & _
Nz(Me.NewDomainName, 0) & _
"'")

If IsNull(DNS) Then
Me.Submit.Visible = True
Else
Me.DomainIDs = DNS & " DI: " & DI
End If

End Sub
'----- end of revised code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Christopher Robin said:
Hi Dirk,

Your suggestion worked great. My command button is now working as
intended. Thank you very much.
Click to expand...

You're welcome. Crystal's approach would also have worked.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Quick and probably quite simple Invalid Use of Null Aftermath Ques 2
Access ACCESS DLOOKUP INVALID USE OF NULL 0
"Invalid use of Null" issue 4
Error 94 Invalid use of Null 4
Invalid Use of Null on Function parameters 2
There is an invalid use of the .dot or ! or invalid parenthses 10
Invalid use of null value error Access 2007 0
Invalid identifier following null constant 3

Back
Top