Query with two or more conditions

[Guest]

The procedure below is intended to identify double entry if both the supplier
and invoice no of the new entry are the same as previous records. But it is
not working properly. Please help.

Private Sub Invoice_No_BeforeUpdate(Cancel As Integer)
Dim x
x = DLookup("[invoice no]", "

", "[invoice no] Like '" & Me![invoice
no] & "'")
Dim y
y = Dlookup ("[supplier]", "

", "[supplier] Like '" & Me![supplier] &
"'")
If IsNull(x) and IsNull(y) = False Then
Dim Msg, Style, Title, Mystring, Response
Msg = "A C C E P T A B L E ?"
Title = "R E P E A T E D E N T R Y"
Style = vbYesNo + vbCritical + vbDefaultButton2
Response = MsgBox(Msg, Style, Title, Response, Mystring)
If Response = vbYes Then
Mystring = "Yes"
Else
Mystring = "No"
Cancel = True
End If
End If
End Sub

 

[Sylvain Lafontaine]

Drop Y and replace X with:

x = DLookup("[invoice no]", "

", "[invoice no] Like '" & Me![invoice
no] & "' and [supplier] Like '" & Me![supplier] & "'")

 

[Steve Schapel]

Alan,

Try it like this...
If Not IsNull(x) and Not IsNull(y) Then

There is also something odd about the MyString variable, in that it is
used in the MsgBox function before it is given a value, and after it is
given a value, nothing is done with it. So you might have another look
at that, but I can't figure out what you are trying to achieve with that
part.

 

[Steve Schapel]

Yes, this is better than what I put in my other post. Also, the Like is
not really appropriate, it should be = like this...
x = DLookup("[invoice no]", "

", "[invoice no]='" & Me![invoice
no] & "' And [supplier]='" & Me![supplier] & "'")
Then...
If Not IsNull(x) Then

 

[Guest]

I have done it. Thanks a lot to both of you