DCount issue

[Guest]

I made a form that consist of two textboxes and a subform thats based
off of a query. I placed this code to count the number of rows that
contain a certain value (rec_num) in the subform, but i get a "You
cancelled the previous operation" error message. Before the form
opens, it prompts you for the rec_num. This is the code.


Private Sub Text9_Click()

Dim x As Integer
x = DCount("[agreement_num]", "ammend")

Text9 = x
End Sub

 

[Guest]

There is nothing in your code that references rec_num. That, however, has
nothing to do with the error you are getting. This error is usually raised
when one of the arguments can't be resolved.

x = DCount("[agreement_num]", "ammend")

Your code is looking for a field named [agreement_num] is a table named
ammend. One of those two is incorrect. Even if they were correct, it will
return a count of all the records in the table because there is no Where
condition. It is faster to use the * for the first argument of the Dcount
and a field name really has no meaning to the DCount. It isn't incorrect to
use a field name, jst impracticle. You need to read up on how the DCount
works.

What you really want is

=DCount("*", "Name Of Your Table", "FieldToCompare = " & Value to Compare
With)

 

[Guest]

Sorry, i meant ot put rec_num there instead of agreement_num. Im trying to
focus DCount on counting that field only. Ammend is a query.

There is nothing in your code that references rec_num. That, however, has
nothing to do with the error you are getting. This error is usually raised
when one of the arguments can't be resolved.

x = DCount("[agreement_num]", "ammend")

Your code is looking for a field named [agreement_num] is a table named
ammend. One of those two is incorrect. Even if they were correct, it will
return a count of all the records in the table because there is no Where
condition. It is faster to use the * for the first argument of the Dcount
and a field name really has no meaning to the DCount. It isn't incorrect to
use a field name, jst impracticle. You need to read up on how the DCount
works.

What you really want is

=DCount("*", "Name Of Your Table", "FieldToCompare = " & Value to Compare
With)

I made a form that consist of two textboxes and a subform thats based
off of a query. I placed this code to count the number of rows that
contain a certain value (rec_num) in the subform, but i get a "You
cancelled the previous operation" error message. Before the form
opens, it prompts you for the rec_num. This is the code.


Private Sub Text9_Click()

Dim x As Integer
x = DCount("[agreement_num]", "ammend")

Text9 = x
End Sub

 

[Guest]

The where condition of the DCount is not a query, it is the same as the WHERE
portion of an SQL statement without the word Where. Look up the DCount
function in VBA Help. It is called the Criteria argument. You have to tell
it what you want to count. As I stated before, putting a field name in the
DCount serves no pupose other than to slow it down. I know Help shows using a
field name, but since you are returning nothing from that field, it has no
meaning. Believe me, using "*" is much faster.

Sorry, i meant ot put rec_num there instead of agreement_num. Im trying to
focus DCount on counting that field only. Ammend is a query.

There is nothing in your code that references rec_num. That, however, has
nothing to do with the error you are getting. This error is usually raised
when one of the arguments can't be resolved.

x = DCount("[agreement_num]", "ammend")

Your code is looking for a field named [agreement_num] is a table named
ammend. One of those two is incorrect. Even if they were correct, it will
return a count of all the records in the table because there is no Where
condition. It is faster to use the * for the first argument of the Dcount
and a field name really has no meaning to the DCount. It isn't incorrect to
use a field name, jst impracticle. You need to read up on how the DCount
works.

What you really want is

=DCount("*", "Name Of Your Table", "FieldToCompare = " & Value to Compare
With)

I made a form that consist of two textboxes and a subform thats based
off of a query. I placed this code to count the number of rows that
contain a certain value (rec_num) in the subform, but i get a "You
cancelled the previous operation" error message. Before the form
opens, it prompts you for the rec_num. This is the code.


Private Sub Text9_Click()

Dim x As Integer
x = DCount("[agreement_num]", "ammend")

Text9 = x
End Sub

 

[Rick Brandt]

The where condition of the DCount is not a query, it is the same as
the WHERE portion of an SQL statement without the word Where. Look up
the DCount function in VBA Help. It is called the Criteria argument.
You have to tell it what you want to count. As I stated before,
putting a field name in the DCount serves no pupose other than to
slow it down. I know Help shows using a field name, but since you are
returning nothing from that field, it has no meaning. Believe me,
using "*" is much faster.

There is a difference though.

DCount("FieldName"... will not include in the count rows where FieldName is
Null. DCount("*"... will. As long as the latter is the desired behavior
then I agree that * is the better choice.

 

[Guest]

Interesting, I did quite a bit of reading on that and the Count() in SQL,
particullary regarding optimization, and that point was never mentioned.

 

[Guest]

I understand what you're saying about the where portion. I didn't add the
where portion because the query prompts you to input data (in my case
rec_num). I thought Dcount could function in all queries,but i found out that
it doesnt function with quieries that require user input before you see the
results such as mine. Since Dcount doesn't do this, what's an alternative
solution?

The where condition of the DCount is not a query, it is the same as the WHERE
portion of an SQL statement without the word Where. Look up the DCount
function in VBA Help. It is called the Criteria argument. You have to tell
it what you want to count. As I stated before, putting a field name in the
DCount serves no pupose other than to slow it down. I know Help shows using a
field name, but since you are returning nothing from that field, it has no
meaning. Believe me, using "*" is much faster.

Sorry, i meant ot put rec_num there instead of agreement_num. Im trying to
focus DCount on counting that field only. Ammend is a query.

There is nothing in your code that references rec_num. That, however, has
nothing to do with the error you are getting. This error is usually raised
when one of the arguments can't be resolved.

x = DCount("[agreement_num]", "ammend")

Your code is looking for a field named [agreement_num] is a table named
ammend. One of those two is incorrect. Even if they were correct, it will
return a count of all the records in the table because there is no Where
condition. It is faster to use the * for the first argument of the Dcount
and a field name really has no meaning to the DCount. It isn't incorrect to
use a field name, jst impracticle. You need to read up on how the DCount
works.

What you really want is

=DCount("*", "Name Of Your Table", "FieldToCompare = " & Value to Compare
With)


:

I made a form that consist of two textboxes and a subform thats based
off of a query. I placed this code to count the number of rows that
contain a certain value (rec_num) in the subform, but i get a "You
cancelled the previous operation" error message. Before the form
opens, it prompts you for the rec_num. This is the code.


Private Sub Text9_Click()

Dim x As Integer
x = DCount("[agreement_num]", "ammend")

Text9 = x
End Sub