By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

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

P: n/a
If Not IsNull(DLookup([Office_use], "tblDemographic", "[Office_use] =
""" & Me.Text9 & """")) Then
Cancel = True
MsgBox "Duplicate Value is Not Allowed"
ActiveControl.Undo
DoCmd.RunCommand 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

Nov 8 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
try

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

hth
"sparks" <js******@swbell.netwrote in message
news:li********************************@4ax.com...
If Not IsNull(DLookup([Office_use], "tblDemographic", "[Office_use] =
""" & Me.Text9 & """")) Then
Cancel = True
MsgBox "Duplicate Value is Not Allowed"
ActiveControl.Undo
DoCmd.RunCommand 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

Nov 8 '06 #2

P: n/a
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.Undo
DoCmd.RunCommand 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("*","tblDemographic", "[Office_use] = """ & Me.Text9 & """")
0 Then
MsgBox "Duplicate Value is Not Allowed".
Cancel = True
ActiveControl.Undo
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.RunCommand acCmdUndo should not be
necessary.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Nov 8 '06 #3

P: n/a
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******@example.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.Undo
DoCmd.RunCommand 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("*","tblDemographic", "[Office_use] = """ & Me.Text9 & """")
>0 Then
MsgBox "Duplicate Value is Not Allowed".
Cancel = True
ActiveControl.Undo
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.RunCommand acCmdUndo should not be
necessary.
Nov 8 '06 #4

P: n/a
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.Undo
DoCmd.RunCommand acCmdUndo
End If

Let me know how it goes.

Regards
Anthony

Nov 9 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.