Invalid Use of Null

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
 
S

strive4peace

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
*
 
D

Dirk Goldgar

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 -----
 
G

Guest

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...
 
G

Guest

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...
 
D

Dirk Goldgar

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
Error 94 Invalid use of Null 4
"Invalid use of Null" issue 4
Access ACCESS DLOOKUP INVALID USE OF NULL 0
Invalid Use of Null on Function parameters 2
There is an invalid use of the .dot or ! or invalid parenthses 10
DSum w/Between = Invalid use of Null 12
Invalid use of Null ----> Help Please 1

Top