I Need HELP!!!!!!!!!!!!

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

Guest

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varADDRESS As Variant

If Me.NewRecord Then

varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and [STREET1] = '" &
Me.STREET1 _
& "' And IsNull([R_FOUND])")

If Not IsNull(varADDRESS) Then

If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS] = '" & varADDRESS & "'"
bolCheckDuplicate = True
End If

End If

End If

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate

End Sub

I have the above listed code in one of my forms. Right now as it stands,
when the user inputs an address, and it already exists, the user is notified
of this, and then taken to the existing data. This part works fine.

I want to go one step further. If the problem at that existing address is
fixed (and you know this because [R_Found] has a date) and the user enters
that address a couple months later, I don't want the user to get the message
that the data exists. I only want the message to pop up if the [R_Found] of
existing data is Null. I tried to to this by coding And IsNull([R_FOUND]),
but its not working. In the existing data the [R_FOUND] field is not Null.
When I enter the address, I still get the message box saying the data already
exists.

Can someone help me?

Thanks.
 
Instead of coding:
IF ISNULL(field) then...
try
IF NZ(field,vbNUllString) = vbNullString then

that may work, if you are sure the field contains a null.
- Dorian
 
Back
Top