DCount failure

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
 
Scott said:
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.
Click to expand...

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

The DCount code is:-

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

Thanks,

Scott
 
Scott said:
Rick,

The DCount code is:-

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

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?
 
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)


Scott said:
Rick,

The DCount code is:-

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

Thanks,

Scott

Rick Brandt said:
What is your DCount() code that you are calling and where are you calling
it?
Click to expand...
Click to expand...
 
Douglas said:
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!
Click to expand...

Actually in a domain aggregate function with the quotes around the name, the
square brackets are not required.
 
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 Brandt said:
Scott said:
Rick,

The DCount code is:-

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

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?
Click to expand...
 
Scott said:
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?
Click to expand...

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 said:
Douglas said:
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!
Click to expand...

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

Son of a gun. I've never known that to be the case before!
 
Douglas said:
Rick Brandt said:
Douglas said:
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!
Click to expand...

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

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

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 :-)
 
I think this behavior might have changed from VERY early versions of Access.

--
Duane Hookom
MS Access MVP

Rick Brandt said:
Douglas said:
Rick Brandt said:
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.
Click to expand...

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

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 :-)
Click to expand...
 
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 Brandt said:
Scott said:
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?
Click to expand...

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.
Click to expand...
 
Scott said:
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.
Click to expand...

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.
 
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
 
Duane said:
I think this behavior might have changed from VERY early versions of
Access.
Click to expand...

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