Automatically Calculating a Field Entry with DCount

[Guest]

I'm using Access 2003 and would like to automatically calculate the entry for
one field in a form based on the count of records associated with an entry in
another field in the table on which the form is based. I think I should be
able to do that with DCount, but can't get it to work.

From perusing the posts on this forum it appears that more detail than less
is requested, so here is some additional detail:

1. I have one field in the form for the Product, in which the user selects
the desired product from a list box. The list box displays both a product
code and product name, and the value of the field is bound to the code.

2. In another field I want to automatically calculate the record number
associated with the product selected in the Product field. So, assume that I
already have four records for product ABC and three records for product XYZ.
When I enter a new record for product ABC, I want the counter in this field
to automatically be populated with the number 5.

If the table underlying the form is Orders, I thought I could accomplish
this by specifying the control source for the record field as follows:

=DCount("[Product]", "[Orders]", "[Product] ='" & Forms![Orders]![Product]
&"'")

But that doesn't seem to work.

Can you please either correct my logic/syntax, or suggest another way to
accomplish this?

Thanks!

 

[Guest]

Hi, Scott.

Try (cutting & pasting):

=DCount("[Product]","[Orders]","[Product] =" & Forms!Orders!Product) + 1

However, this will simply calculate a value to display in this control, it
will not store the value in a field. To do this, you will need code in the
AfterUpdate event of the Product control (bound to the Product field):

Me![YourRecordNumberControl] = DCount("[Product]","[Orders]","[Product] =" &
Forms!Orders!Product) + 1

Hope that helps.
Sprinks

 

[Guest]

Sorry I have a small problem.. Is "ORDER" the form or the table
because I have a field named PRNumber that I want to put a counter on
everytime there is a record with the same PRNumber.

this is what I have but I am getting an error in my textbox (#Name?)
=DCount("[PRNumber]","[NamePlate]","[PRNumber] = " &
Forms!NamePlate!PRNumber) +1
And for the after update
Me!(Field I want to store value)
=DCount("[PRNumber]","[NamePlate]","[PRNumber] = " &
Forms!NamePlate!PRNumber) +1

Hi, Scott.

Try (cutting & pasting):

=DCount("[Product]","[Orders]","[Product] =" & Forms!Orders!Product) + 1

However, this will simply calculate a value to display in this control, it
will not store the value in a field. To do this, you will need code in the
AfterUpdate event of the Product control (bound to the Product field):

Me![YourRecordNumberControl] = DCount("[Product]","[Orders]","[Product] =" &
Forms!Orders!Product) + 1

Hope that helps.
Sprinks

I'm using Access 2003 and would like to automatically calculate the entry for
one field in a form based on the count of records associated with an entry in
another field in the table on which the form is based. I think I should be
able to do that with DCount, but can't get it to work.

From perusing the posts on this forum it appears that more detail than less
is requested, so here is some additional detail:

1. I have one field in the form for the Product, in which the user selects
the desired product from a list box. The list box displays both a product
code and product name, and the value of the field is bound to the code.

2. In another field I want to automatically calculate the record number
associated with the product selected in the Product field. So, assume that I
already have four records for product ABC and three records for product XYZ.
When I enter a new record for product ABC, I want the counter in this field
to automatically be populated with the number 5.

If the table underlying the form is Orders, I thought I could accomplish
this by specifying the control source for the record field as follows:

=DCount("[Product]", "[Orders]", "[Product] ='" & Forms![Orders]![Product]
&"'")

But that doesn't seem to work.

Can you please either correct my logic/syntax, or suggest another way to
accomplish this?

Thanks!

 

[Guest]

Sorry nameplate is my Table

Hi, Scott.

Try (cutting & pasting):

=DCount("[Product]","[Orders]","[Product] =" & Forms!Orders!Product) + 1

However, this will simply calculate a value to display in this control, it
will not store the value in a field. To do this, you will need code in the
AfterUpdate event of the Product control (bound to the Product field):

Me![YourRecordNumberControl] = DCount("[Product]","[Orders]","[Product] =" &
Forms!Orders!Product) + 1

Hope that helps.
Sprinks

I'm using Access 2003 and would like to automatically calculate the entry for
one field in a form based on the count of records associated with an entry in
another field in the table on which the form is based. I think I should be
able to do that with DCount, but can't get it to work.

From perusing the posts on this forum it appears that more detail than less
is requested, so here is some additional detail:

1. I have one field in the form for the Product, in which the user selects
the desired product from a list box. The list box displays both a product
code and product name, and the value of the field is bound to the code.

2. In another field I want to automatically calculate the record number
associated with the product selected in the Product field. So, assume that I
already have four records for product ABC and three records for product XYZ.
When I enter a new record for product ABC, I want the counter in this field
to automatically be populated with the number 5.

If the table underlying the form is Orders, I thought I could accomplish
this by specifying the control source for the record field as follows:

=DCount("[Product]", "[Orders]", "[Product] ='" & Forms![Orders]![Product]
&"'")

But that doesn't seem to work.

Can you please either correct my logic/syntax, or suggest another way to
accomplish this?

Thanks!

 

[Guest]

BigRed,

Sorry, but I gave you the wrong syntax. The second parameter should
evaluate to a string expression equal to a table name, and so should not have
brackets around it. Although Access allows you to omit them around form and
control names, I and many other developers choose to include them for
clarity.

Try:

=DCount("[PRNumber]","NamePlate","[PRNumber] = " &
Forms![NamePlate]![PRNumber]) +1

Also, if Forms![NamePlate]![PRNumber] evaluates to a string expression,
single quotes are required around it, so the expression would be:

=DCount("[PRNumber]","NamePlate","[PRNumber] = " & "'" &
Forms![NamePlate]![PRNumber] & "'") +1

Hope that helps.
Sprinks

Sorry I have a small problem.. Is "ORDER" the form or the table
because I have a field named PRNumber that I want to put a counter on
everytime there is a record with the same PRNumber.

this is what I have but I am getting an error in my textbox (#Name?)
=DCount("[PRNumber]","[NamePlate]","[PRNumber] = " &
Forms!NamePlate!PRNumber) +1
And for the after update
Me!(Field I want to store value)
=DCount("[PRNumber]","[NamePlate]","[PRNumber] = " &
Forms!NamePlate!PRNumber) +1

Hi, Scott.

Try (cutting & pasting):

=DCount("[Product]","[Orders]","[Product] =" & Forms!Orders!Product) + 1

However, this will simply calculate a value to display in this control, it
will not store the value in a field. To do this, you will need code in the
AfterUpdate event of the Product control (bound to the Product field):

Me![YourRecordNumberControl] = DCount("[Product]","[Orders]","[Product] =" &
Forms!Orders!Product) + 1

Hope that helps.
Sprinks

I'm using Access 2003 and would like to automatically calculate the entry for
one field in a form based on the count of records associated with an entry in
another field in the table on which the form is based. I think I should be
able to do that with DCount, but can't get it to work.

From perusing the posts on this forum it appears that more detail than less
is requested, so here is some additional detail:

1. I have one field in the form for the Product, in which the user selects
the desired product from a list box. The list box displays both a product
code and product name, and the value of the field is bound to the code.

2. In another field I want to automatically calculate the record number
associated with the product selected in the Product field. So, assume that I
already have four records for product ABC and three records for product XYZ.
When I enter a new record for product ABC, I want the counter in this field
to automatically be populated with the number 5.

If the table underlying the form is Orders, I thought I could accomplish
this by specifying the control source for the record field as follows:

=DCount("[Product]", "[Orders]", "[Product] ='" & Forms![Orders]![Product]
&"'")

But that doesn't seem to work.

Can you please either correct my logic/syntax, or suggest another way to
accomplish this?

Thanks!