Hi Connie
What data type is lngL? If it is declared "As Long" then it cannot contain
a Null, and the first line will fail if DLookup returns Null because it
finds no matching records.
If it is impossible for LookingID to be zero, then you can use Nz() again:
lngL = Nz(DLookup("LookingID", "LookingContacts", "ContactID = " &
Nz([ContactID], 0)), 0)
This will greatly simplify the next line:
If (lngL< > 0) And IsNull(DLookup("EndDate", "LookingEvent", "LookingID = "
& lngL)) Then
Best wishes,
Graham
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Thursday, 6 October 2011 10:00
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Invalid use of Null
>
>
> In the AfterUpdate Event for entering the buyer (ContactID) in a subform
of the Sales form I have code that is checking to see if that buyer was
involved in a looking event. If so, the LookingEvent form is opened and they
are asked to enter an ending date.
>
> It is working in two of three cases:
> -If there is a contact in the LookingContacts table and there is no end
date entered in the LookingEvent table
> -If there is a contact in Looking Contacts table and there is an end date
entered in the LookingEvent table
>
> I am getting the Invalid Use of Null error message if the contact is not
in the LookingContacts table and there is no LookingID associated with him
and therefore no record in the LookingEvents table.
>
> I don't think I have to use the NZ in the lngL statement since entering
the ContactID is what prompts the code to begin.
>
> Code is below. Thanks!!!
> Connie
>
> lngL = DLookup("LookingID", "LookingContacts", "ContactID = " &
Nz([ContactID], 0))
>
> If ((Not IsNull(lngL)) And (lngL > 0) And (IsNull(DLookup("EndDate",
"LookingEvent", "LookingID = " & Nz(lngL, 0))))) Then
> DoCmd.OpenForm "LookingEvent", , , ("LookingID = " & lngL)
> MsgBox "Are these contacts no longer buyers?" _
> & vbNewLine & "Then enter an ending date"
> End If
Tidak ada komentar:
Posting Komentar