Totals in a form

[Guest]

I have a form that I need to automatically populate a count of the matching
type +1. I've tried for the value (or default value of TypeCount in a form):
=DCount("[EntryType]","[Components1]","[Components1]![EntryType]=[EntryType]"),
but this gives me a count of all of the EntryType in the table Components1.
It's not matching it to the EntryType on the current form. I also tried a
lookup on a make-table query (on the created table) to pull the count+1, but
it gives me only the 1st EntryType count. Am I coding this wrong? The
count+1 needs to go into the table value of TypeCount.

 

[Guest]

Your DCount syntax is incorrect. The value being searched for needs to be
outside the quotes and the table field should be first:
=DCount("[EntryType]","[Components1]","[EntryType] = " &
[Components1]![EntryType])

The above would be correct if [EntryType] is a numeric field. If it is a
text field, it needs to have quotes around it, but the reference to the
control still needs to be outside the quotes:
=DCount("[EntryType]","[Components1]","[EntryType] = '" &
[Components1]![EntryType] & "'")

 

[Guest]

I tried it your way, but it doesn't work either ... the sql that does work
for the value that i need is:
SELECT Count(Components1.EntryType)+1 AS ct
FROM Components1
WHERE (((Components1.EntryType)=[Forms].[frm_2].[EntryType]))
GROUP BY Components1.EntryType
ORDER BY Components1.EntryType;

The problem now is that it won't populate the field in the form unless i
select the value in the combo box .... i need it to do that automatically ....

Your DCount syntax is incorrect. The value being searched for needs to be
outside the quotes and the table field should be first:
=DCount("[EntryType]","[Components1]","[EntryType] = " &
[Components1]![EntryType])

The above would be correct if [EntryType] is a numeric field. If it is a
text field, it needs to have quotes around it, but the reference to the
control still needs to be outside the quotes:
=DCount("[EntryType]","[Components1]","[EntryType] = '" &
[Components1]![EntryType] & "'")

I have a form that I need to automatically populate a count of the matching
type +1. I've tried for the value (or default value of TypeCount in a form):
=DCount("[EntryType]","[Components1]","[Components1]![EntryType]=[EntryType]"),
but this gives me a count of all of the EntryType in the table Components1.
It's not matching it to the EntryType on the current form. I also tried a
lookup on a make-table query (on the created table) to pull the count+1, but
it gives me only the 1st EntryType count. Am I coding this wrong? The
count+1 needs to go into the table value of TypeCount.

 

[Guest]

This will. Your original post confused my a bit.
Assumming the current form is frm_2.
Put this in the control souce of the text box where you want to value to
appear.

=DCount("*","Components1","[EntryType] = """ & Forms!frm_2!EntryType & """")

I don't see any reference to a combo box in your original post. What has
that got to do with it?

As to your SQL. You can't use it to populate a control.

I tried it your way, but it doesn't work either ... the sql that does work
for the value that i need is:
SELECT Count(Components1.EntryType)+1 AS ct
FROM Components1
WHERE (((Components1.EntryType)=[Forms].[frm_2].[EntryType]))
GROUP BY Components1.EntryType
ORDER BY Components1.EntryType;

The problem now is that it won't populate the field in the form unless i
select the value in the combo box .... i need it to do that automatically ....

Your DCount syntax is incorrect. The value being searched for needs to be
outside the quotes and the table field should be first:
=DCount("[EntryType]","[Components1]","[EntryType] = " &
[Components1]![EntryType])

The above would be correct if [EntryType] is a numeric field. If it is a
text field, it needs to have quotes around it, but the reference to the
control still needs to be outside the quotes:
=DCount("[EntryType]","[Components1]","[EntryType] = '" &
[Components1]![EntryType] & "'")

I have a form that I need to automatically populate a count of the matching
type +1. I've tried for the value (or default value of TypeCount in a form):
=DCount("[EntryType]","[Components1]","[Components1]![EntryType]=[EntryType]"),
but this gives me a count of all of the EntryType in the table Components1.
It's not matching it to the EntryType on the current form. I also tried a
lookup on a make-table query (on the created table) to pull the count+1, but
it gives me only the 1st EntryType count. Am I coding this wrong? The
count+1 needs to go into the table value of TypeCount.

 

[Guest]

Ok.... it now shows the value in the text box .... how can I write that out
to the table Components1 (into the numentrytype) field when i move on to the
next newrec for input?

This will. Your original post confused my a bit.
Assumming the current form is frm_2.
Put this in the control souce of the text box where you want to value to
appear.

=DCount("*","Components1","[EntryType] = """ & Forms!frm_2!EntryType & """")

I don't see any reference to a combo box in your original post. What has
that got to do with it?

As to your SQL. You can't use it to populate a control.

I tried it your way, but it doesn't work either ... the sql that does work
for the value that i need is:
SELECT Count(Components1.EntryType)+1 AS ct
FROM Components1
WHERE (((Components1.EntryType)=[Forms].[frm_2].[EntryType]))
GROUP BY Components1.EntryType
ORDER BY Components1.EntryType;

The problem now is that it won't populate the field in the form unless i
select the value in the combo box .... i need it to do that automatically ....

Your DCount syntax is incorrect. The value being searched for needs to be
outside the quotes and the table field should be first:
=DCount("[EntryType]","[Components1]","[EntryType] = " &
[Components1]![EntryType])

The above would be correct if [EntryType] is a numeric field. If it is a
text field, it needs to have quotes around it, but the reference to the
control still needs to be outside the quotes:
=DCount("[EntryType]","[Components1]","[EntryType] = '" &
[Components1]![EntryType] & "'")


:

I have a form that I need to automatically populate a count of the matching
type +1. I've tried for the value (or default value of TypeCount in a form):
=DCount("[EntryType]","[Components1]","[Components1]![EntryType]=[EntryType]"),
but this gives me a count of all of the EntryType in the table Components1.
It's not matching it to the EntryType on the current form. I also tried a
lookup on a make-table query (on the created table) to pull the count+1, but
it gives me only the 1st EntryType count. Am I coding this wrong? The
count+1 needs to go into the table value of TypeCount.

 

[Guest]

Okay, had I known you wanted to write it to a table, I would not have
recommended the Control Source. Move the code I gave you to the default
value property and make the field you want to write it to the control source.
That is, if the field you want to write to is in Components1. If it is not,
then you need to write some code to put it in the table and field you want it
to go in.

If you need more, I will be happy to help, but please give me the whole
picture.

Ok.... it now shows the value in the text box .... how can I write that out
to the table Components1 (into the numentrytype) field when i move on to the
next newrec for input?

This will. Your original post confused my a bit.
Assumming the current form is frm_2.
Put this in the control souce of the text box where you want to value to
appear.

=DCount("*","Components1","[EntryType] = """ & Forms!frm_2!EntryType & """")

I don't see any reference to a combo box in your original post. What has
that got to do with it?

As to your SQL. You can't use it to populate a control.

I tried it your way, but it doesn't work either ... the sql that does work
for the value that i need is:
SELECT Count(Components1.EntryType)+1 AS ct
FROM Components1
WHERE (((Components1.EntryType)=[Forms].[frm_2].[EntryType]))
GROUP BY Components1.EntryType
ORDER BY Components1.EntryType;

The problem now is that it won't populate the field in the form unless i
select the value in the combo box .... i need it to do that automatically ....


:

Your DCount syntax is incorrect. The value being searched for needs to be
outside the quotes and the table field should be first:
=DCount("[EntryType]","[Components1]","[EntryType] = " &
[Components1]![EntryType])

The above would be correct if [EntryType] is a numeric field. If it is a
text field, it needs to have quotes around it, but the reference to the
control still needs to be outside the quotes:
=DCount("[EntryType]","[Components1]","[EntryType] = '" &
[Components1]![EntryType] & "'")


:

I have a form that I need to automatically populate a count of the matching
type +1. I've tried for the value (or default value of TypeCount in a form):
=DCount("[EntryType]","[Components1]","[Components1]![EntryType]=[EntryType]"),
but this gives me a count of all of the EntryType in the table Components1.
It's not matching it to the EntryType on the current form. I also tried a
lookup on a make-table query (on the created table) to pull the count+1, but
it gives me only the 1st EntryType count. Am I coding this wrong? The
count+1 needs to go into the table value of TypeCount.

 

[Guest]

I tried to enter the formula into the default area, and put the control
source as NumEntryType (from table components1) in the form. It will not
populate NumEntryType for me. I don't know what the heck I'm doing wrong ..
The entire form is based on the table components1.

Okay, had I known you wanted to write it to a table, I would not have
recommended the Control Source. Move the code I gave you to the default
value property and make the field you want to write it to the control source.
That is, if the field you want to write to is in Components1. If it is not,
then you need to write some code to put it in the table and field you want it
to go in.

If you need more, I will be happy to help, but please give me the whole
picture.

Ok.... it now shows the value in the text box .... how can I write that out
to the table Components1 (into the numentrytype) field when i move on to the
next newrec for input?

This will. Your original post confused my a bit.
Assumming the current form is frm_2.
Put this in the control souce of the text box where you want to value to
appear.

=DCount("*","Components1","[EntryType] = """ & Forms!frm_2!EntryType & """")

I don't see any reference to a combo box in your original post. What has
that got to do with it?

As to your SQL. You can't use it to populate a control.

:

I tried it your way, but it doesn't work either ... the sql that does work
for the value that i need is:
SELECT Count(Components1.EntryType)+1 AS ct
FROM Components1
WHERE (((Components1.EntryType)=[Forms].[frm_2].[EntryType]))
GROUP BY Components1.EntryType
ORDER BY Components1.EntryType;

The problem now is that it won't populate the field in the form unless i
select the value in the combo box .... i need it to do that automatically ....


:

Your DCount syntax is incorrect. The value being searched for needs to be
outside the quotes and the table field should be first:
=DCount("[EntryType]","[Components1]","[EntryType] = " &
[Components1]![EntryType])

The above would be correct if [EntryType] is a numeric field. If it is a
text field, it needs to have quotes around it, but the reference to the
control still needs to be outside the quotes:
=DCount("[EntryType]","[Components1]","[EntryType] = '" &
[Components1]![EntryType] & "'")


:

I have a form that I need to automatically populate a count of the matching
type +1. I've tried for the value (or default value of TypeCount in a form):
=DCount("[EntryType]","[Components1]","[Components1]![EntryType]=[EntryType]"),
but this gives me a count of all of the EntryType in the table Components1.
It's not matching it to the EntryType on the current form. I also tried a
lookup on a make-table query (on the created table) to pull the count+1, but
it gives me only the 1st EntryType count. Am I coding this wrong? The
count+1 needs to go into the table value of TypeCount.