DCount failure

[Scott]

I have a data input form based on a parameter query. Recently I put a
DCount in the event procedure of a field on the form to prevent from
duplicate entry. It does not work and produce run-time error '2001'. Is
there any way to get rid of this issue allowing both workable.

Thanks,

Scott

 

[Rick Brandt]

I have a data input form based on a parameter query. Recently I put a
DCount in the event procedure of a field on the form to prevent from
duplicate entry. It does not work and produce run-time error '2001'.
Is there any way to get rid of this issue allowing both workable.

What is your DCount() code that you are calling and where are you calling it?

 

[Scott]

Rick,

The DCount code is:-

If DCount("*", "qry pro", "[Case No] = '" & Me!txtCaseNo & "'") > 0 Then

Thanks,

Scott

 

[Rick Brandt]

Rick,

The DCount code is:-

If DCount("*", "qry pro", "[Case No] = '" & Me!txtCaseNo & "'") > 0
Then

So Case No is text correct?

Can you run this in the immediate window with a literal value instead of the
control reference?

If it works with a literal and not your reference then the reference doesn't
contain what you think it does. Is it a Combo or List box by any chance?

 

[Douglas J. Steele]

The problem's the table (or query) name. Since there's a space in it, square
brackets are required:

If DCount("*", "[qry pro]", "[Case No] = '" & Me!txtCaseNo & "'") > 0


Yeah, I know that the error message ("You canceled the previous operation.")
makes no sense in this context!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Rick,

The DCount code is:-

If DCount("*", "qry pro", "[Case No] = '" & Me!txtCaseNo & "'") > 0 Then

Thanks,

Scott

What is your DCount() code that you are calling and where are you calling
it?

 

[Rick Brandt]

The problem's the table (or query) name. Since there's a space in it,
square brackets are required:

If DCount("*", "[qry pro]", "[Case No] = '" & Me!txtCaseNo & "'") > 0


Yeah, I know that the error message ("You canceled the previous
operation.") makes no sense in this context!

Actually in a domain aggregate function with the quotes around the name, the
square brackets are not required.

 

[Scott]

Rick,

Case No is the text data. It seems the space in the Query or Table name
enclosed with square brackets works fine.

Did you mean I replace the criteria with "[Case No] = '0001'")? I tried it
and it worked correctly with parameter query. What is wrong with this
setup?

Thanks,

Scott

Rick,

The DCount code is:-

If DCount("*", "qry pro", "[Case No] = '" & Me!txtCaseNo & "'") > 0
Then

So Case No is text correct?

Can you run this in the immediate window with a literal value instead of
the control reference?

If it works with a literal and not your reference then the reference
doesn't contain what you think it does. Is it a Combo or List box by any
chance?

 

[Rick Brandt]

Rick,

Case No is the text data. It seems the space in the Query or Table
name enclosed with square brackets works fine.

Did you mean I replace the criteria with "[Case No] = '0001'")? I
tried it and it worked correctly with parameter query. What is wrong
with this setup?

I'm confused by this response. Did you get it working or not?

Any time you are struggling to get a complex expression working it is a good
idea to break it down into its simpler components and see what happens. For
example if I Have the expression...

DCount("FieldName", "TableName", "SomeField = '" Some form reference" & "'")

....that isn't working I can break it down by first eliminating the third
argument altogether...

DCount("FieldName", "TableName")

If that works I know the problem is with the third argument. In that case I try
a simpler expression there to see if that gives me the results I expect...

DCount("FieldName", "TableName", "SomeField = 'AValue'")

If that works then I know that it is the form reference causing the problem. I
was just suggestiing this as a way for you to figure out what your expression's
problem was.

 

[Douglas J. Steele]

The problem's the table (or query) name. Since there's a space in it,
square brackets are required:

If DCount("*", "[qry pro]", "[Case No] = '" & Me!txtCaseNo & "'") > 0


Yeah, I know that the error message ("You canceled the previous
operation.") makes no sense in this context!

Actually in a domain aggregate function with the quotes around the name,
the square brackets are not required.

Son of a gun. I've never known that to be the case before!

 

[Rick Brandt]

The problem's the table (or query) name. Since there's a space in
it, square brackets are required:

If DCount("*", "[qry pro]", "[Case No] = '" & Me!txtCaseNo & "'")
0


Yeah, I know that the error message ("You canceled the previous
operation.") makes no sense in this context!

Actually in a domain aggregate function with the quotes around the
name, the square brackets are not required.

Son of a gun. I've never known that to be the case before!

The subtleties of behavior and syntax regarding spaces in field and table names
is probably lost on lots of people who are smart enough not to have spaces there
in the first place

 

[Duane Hookom]

I think this behavior might have changed from VERY early versions of Access.

--
Duane Hookom
MS Access MVP

Douglas J. Steele wrote:
The problem's the table (or query) name. Since there's a space in
it, square brackets are required:

If DCount("*", "[qry pro]", "[Case No] = '" & Me!txtCaseNo & "'")
0


Yeah, I know that the error message ("You canceled the previous
operation.") makes no sense in this context!

Actually in a domain aggregate function with the quotes around the
name, the square brackets are not required.

Son of a gun. I've never known that to be the case before!

The subtleties of behavior and syntax regarding spaces in field and table
names is probably lost on lots of people who are smart enough not to have
spaces there in the first place

 

[Scott]

Rick,

Thanks for your advice. As mentioned in my last reply, it works if I change
the criteria to a fixed value from a variable, the error has gone. However,
it is outside my knowledge where is wrong as the original criteria also
works if I remove the parameter query. It seems to me the criteria does not
like parameter query or vice versa.

Scott

Rick,

Case No is the text data. It seems the space in the Query or Table
name enclosed with square brackets works fine.

Did you mean I replace the criteria with "[Case No] = '0001'")? I
tried it and it worked correctly with parameter query. What is wrong
with this setup?

I'm confused by this response. Did you get it working or not?

Any time you are struggling to get a complex expression working it is a
good idea to break it down into its simpler components and see what
happens. For example if I Have the expression...

DCount("FieldName", "TableName", "SomeField = '" Some form reference" &
"'")

...that isn't working I can break it down by first eliminating the third
argument altogether...

DCount("FieldName", "TableName")

If that works I know the problem is with the third argument. In that case
I try a simpler expression there to see if that gives me the results I
expect...

DCount("FieldName", "TableName", "SomeField = 'AValue'")

If that works then I know that it is the form reference causing the
problem. I was just suggestiing this as a way for you to figure out what
your expression's problem was.

 

[Rick Brandt]

Rick,

Thanks for your advice. As mentioned in my last reply, it works if I change
the criteria to a fixed value from a variable, the error has gone. However,
it is outside my knowledge where is wrong as the original criteria also works
if I remove the parameter query. It seems to me the criteria does not like
parameter query or vice versa.

I fail to see why having a parameter in the form's query should make any
difference. If it works with a literal value then you either have the form
reference wrong or that control on the form has the wrong value in it.

Is the domain (table/query) that you are using in the DCount() function the same
parameter query? It should be the source table that the query is based on.

 

[Scott]

Rick,

Bingo! Once I changed the domain to the table name instead of the query
name, it works correctly with the parameter query. Although I am also at
loss why it happens, I tried to enter a number of case numbers that in the
database and the database identifies all successfully. Thanks a million!

Scott

 

[Rick Brandt]

I think this behavior might have changed from VERY early versions of
Access.

Quite possible. I only learned that the brackets weren't required several
months ago.