Cancelled Previous Operation on DLookup or DCount

[AJ]

Hi,

I'm trying to prevent a duplicate entry of a number in a particular
field in a form, however, whenever I enter the data it gives me the
error "Run Time Error 2001" you cancelled the previous operation.

The weird thing is that I have this exact same code running in another
form just fine. The only thing different is that one is a txt field
and the other is an integer. I've tried both DLookup and DCount as
shown in my sample code below. Any ideas?

Private Sub SO__BeforeUpdate(Cancel As Integer)


If IsNull([SO#]) Then
Exit Sub
End If

Dim varTemp1 As Variant

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = Me![SO#]")

If varTemp1 = Me![SO#] Then
MsgBox "Duplicate!", vbOKOnly, "Duplicate SO Number Found"
End If
End Sub

______________________________________________________________

If DCount("*", "RMA INFO", "[SO#] = Me.SO_.Value") > 0 Then
MsgBox "You have entered a value that is already in the
table!"
Me.Undo
End If

______________________________________________________________

 

[Douglas J. Steele]

You need to put the reference to the control outside of the quotes.

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = " & Me![SO#])

That assumes SO# is numeric. If it's text, try

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = '" & Me![SO#] & "'")

Exagerated for clarity, that's

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = ' " & Me![SO#] & " ' ")

 

[AJ]

You need to put the reference to the control outside of the quotes.

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = " & Me![SO#])

That assumes SO# is numeric. If it's text, try

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = '" & Me![SO#] & "'")

Exagerated for clarity, that's

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = ' " & Me![SO#] & " ' ")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi,

I'm trying to prevent a duplicate entry of a number in a particular
field in a form, however, whenever I enter the data it gives me the
error "Run Time Error 2001" you cancelled the previous operation.

The weird thing is that I have this exact same code running in another
form just fine. The only thing different is that one is a txt field
and the other is an integer. I've tried both DLookup and DCount as
shown in my sample code below. Any ideas?

Private Sub SO__BeforeUpdate(Cancel As Integer)

If IsNull([SO#]) Then
Exit Sub
End If

Dim varTemp1 As Variant

varTemp1 = DLookup("[SO#]", "RMA INFO", "[SO#] = Me![SO#]")

If varTemp1 = Me![SO#] Then
MsgBox "Duplicate!", vbOKOnly, "Duplicate SO Number Found"
End If
End Sub

______________________________________________________________

If DCount("*", "RMA INFO", "[SO#] = Me.SO_.Value") > 0 Then
MsgBox "You have entered a value that is already in the
table!"
Me.Undo
End If

______________________________________________________________

That worked like a charm! Thanks so much. I knew I was just messing
up some syntax.

 

[George Nicholson]

Once you apply Doug's suggestion, don't forget to add the all important:
Cancel = True
if a match is found.

HTH,