Re: Invalid Use of Null

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Christopher Robin" <ChristopherRobin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in message news:1107FED9-DB27-4805-992C-AFA4F8A8B4DA@xxxxxxxxxxxxx
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

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)


.

Relevant Pages

  • Re: Dlookup in variable
    ... I am programmatically trying to set the controlsource of a ... > When I try to apply my Dlookup, ... > Private Sub Combo4_AfterUpdate ... > Function varvalAs String ...
    (microsoft.public.access.formscoding)
  • Re: Invalid Use of Null
    ... Dim DNS As String, DI As Integer ... DNS = DLookup(_ ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.formscoding)
  • Dlookup in variable
    ... I am programmatically trying to set the controlsource of a field ... When I try to apply my Dlookup, ... Private Sub Combo4_AfterUpdate ... Function varvalAs String ...
    (microsoft.public.access.formscoding)
  • Re: Dlookup in variable
    ... you need to pass some quotes inside the string. ... > When I try to apply my Dlookup, ... > Private Sub Combo4_AfterUpdate ...
    (microsoft.public.access.formscoding)
  • Re: Getting a Type Mismatch error with this Dlookup function on a form
    ... "ProjectName" in the DLookUp looks a bit suspicious to me from the naming ... > Private Sub Form_Load ... > Dim strUserLevel As String, ...
    (microsoft.public.access.formscoding)