trying to use dlookup with text field but get a weird error

[sparks]

If Not IsNull(DLookup([Office_use], "tblDemographic ", "[Office_use] =
""" & Me.Text9 & """")) Then
Cancel = True
MsgBox "Duplicate Value is Not Allowed"
ActiveControl.U ndo
DoCmd.RunComman d acCmdUndo
End If

I try to do this but when you enter a text value such as A you get

"you canceled the previous operation"

and the debugger is on the IF line of the command so cancel is not
true or ??
never seen this kind of error before

[tina]

try

If Not DCount(1, "tblDemographic ", _
"[Office_use] = """ & Me.Text9 & """") < 1 Then

hth
"sparks" <js******@swbel l.netwrote in message
news:li******** *************** *********@4ax.c om...

If Not IsNull(DLookup([Office_use], "tblDemographic ", "[Office_use] =
""" & Me.Text9 & """")) Then
Cancel = True
MsgBox "Duplicate Value is Not Allowed"
ActiveControl.U ndo
DoCmd.RunComman d acCmdUndo
End If

I try to do this but when you enter a text value such as A you get

"you canceled the previous operation"

and the debugger is on the IF line of the command so cancel is not
true or ??
never seen this kind of error before

[fredg]

On Wed, 08 Nov 2006 12:51:55 GMT, sparks wrote:

If Not IsNull(DLookup([Office_use], "tblDemographic ", "[Office_use] =
""" & Me.Text9 & """")) Then
Cancel = True
MsgBox "Duplicate Value is Not Allowed"
ActiveControl.U ndo
DoCmd.RunComman d acCmdUndo
End If

I try to do this but when you enter a text value such as A you get

"you canceled the previous operation"

and the debugger is on the IF line of the command so cancel is not
true or ??

never seen this kind of error before

Each argument of a DLookUp must be a string. Your first arghument is
not a string,....(If Not IsNull(DLookup([Office_use],....).

[Office_use] needs to be enclosed in Quotes, as are the other
arguments.

If Not IsNull(DLookup( "[Office_use]", ...etc..) would most likely
work, but it would me simpler to just count.

If DCount("*","tbl Demographic", "[Office_use] = """ & Me.Text9 & """")

0 Then

MsgBox "Duplicate Value is Not Allowed".
Cancel = True
ActiveControl.U ndo
End If

As long as Cancel is an accepted argument in the sub procedure you are
writing this code, i.e. a Control's BeforeUpdate event, Cancel = True
is all you need. The DoCmd.RunComman d acCmdUndo should not be
necessary.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[sparks]

oh man I looked at that thing for an hr and never did see that

If Not IsNull(DLookup( "[Office_use]", "tblDemographic ", "[Office_use]
= '" & Me.Text9 & "'"))

what is weird if you put in 111 it works.

if you put in bbb you get
" you canceled the previous operation "

and if you put in 108b you get
" systax error (missing operator) in query expression '108b' "
this is what had me going 2 different errors based on what the leading
chr was.

thanks big time for the help

On Wed, 8 Nov 2006 08:53:16 -0800, fredg <fg******@examp le.invalid>
wrote:

>On Wed, 08 Nov 2006 12:51:55 GMT, sparks wrote:

>If Not IsNull(DLookup([Office_use], "tblDemographic ", "[Office_use] =
""" & Me.Text9 & """")) Then
Cancel = True
MsgBox "Duplicate Value is Not Allowed"
ActiveControl.U ndo
DoCmd.RunComman d acCmdUndo
End If

I try to do this but when you enter a text value such as A you get

"you canceled the previous operation"

and the debugger is on the IF line of the command so cancel is not
true or ??

never seen this kind of error before

Each argument of a DLookUp must be a string. Your first arghument is
not a string,....(If Not IsNull(DLookup([Office_use],....).

[Office_use] needs to be enclosed in Quotes, as are the other
arguments.

If Not IsNull(DLookup( "[Office_use]", ...etc..) would most likely
work, but it would me simpler to just count.

If DCount("*","tbl Demographic", "[Office_use] = """ & Me.Text9 & """")

>0 Then

MsgBox "Duplicate Value is Not Allowed".
Cancel = True
ActiveControl.U ndo
End If

As long as Cancel is an accepted argument in the sub procedure you are
writing this code, i.e. a Control's BeforeUpdate event, Cancel = True
is all you need. The DoCmd.RunComman d acCmdUndo should not be
necessary.

[Anthos]

The double " are confusing the operation

Try

If Not IsNull(DLookup([Office_use], "tblDemographic ", "[Office_use] =
'" & Me.Text9 & "')) Then
Cancel = True
MsgBox "Duplicate Value is Not Allowed"
ActiveControl.U ndo
DoCmd.RunComman d acCmdUndo
End If

Let me know how it goes.

Regards
Anthony