Count Errors With Records

[Rob Dykstra]

Hello,

I am having a problem with a text box in the Form Footer. The text box has a
control source of =Count(*). I have a command button that when pressed
returns the records of a table as the record source of the form. For some
reason I am getting the #Error message in the text box, even when there are
records present in the Detail section of the form. I am at a loss, and was
wondering if anyone has encountered this before and knew what the solution
might be. I would appreciate it, thanks!

-Rob-

 

[Michel Walsh]

Hi,

Can be something else but in a report, if a control, in the report, has the
name of a field but is not bind to that field, that makes an error.


Example, open Northwind, make a new report on table Categories. Bring the
field CategoryName in the de detail section, nothing else. That should work.
Now, edit the report in design view and change the control, called
CategoryName (not the label, the control), bound to the field of the same
name, and now, "bind" it by typing:


=[CategoryName] & "+"



in it. Now that the control CategoryName is bound to SOMETHING ELSE that the
field of the same name that its name, run the report and observe the error.


Back in design mode, change the control name from CategoryName to something
else that is NOT A FIELD name, and the error disappear.


Conclusion: if you have a control name = to a field name, they must be
bound together and to nothing else. If you hand edit a report generated by a
wizard, by adding some decoration, here, we added a "+", but could have
been:

=[FirstName] & " " & [LastName]

bound to a control name FirstName, then, we are likely to get an error
unless you also change the control name.


Not evident, but once we know...




Hoping it may help,
Vanderghast, Access MVP

 

[Rob Dykstra via AccessMonster.com]

Michel,

The name of the control is txtTtlNmbrTires, and is not the name of any field
in the record source. Additionally, I also have other total and sum fields
for Price, Mileage, etc with control names of txtTtlxxx and txtAvgxxx, so as
to distinguish them from any fields. These also return a #Error when the
form is run. This is one of the easiest things to do, I've done it for other
apps, and I'm completely stumped as to why it's not working on this form.

-Rob-

Hi,

Can be something else but in a report, if a control, in the report, has the
name of a field but is not bind to that field, that makes an error.

Example, open Northwind, make a new report on table Categories. Bring the
field CategoryName in the de detail section, nothing else. That should work.
Now, edit the report in design view and change the control, called
CategoryName (not the label, the control), bound to the field of the same
name, and now, "bind" it by typing:

=[CategoryName] & "+"

in it. Now that the control CategoryName is bound to SOMETHING ELSE that the
field of the same name that its name, run the report and observe the error.

Back in design mode, change the control name from CategoryName to something
else that is NOT A FIELD name, and the error disappear.

Conclusion: if you have a control name = to a field name, they must be
bound together and to nothing else. If you hand edit a report generated by a
wizard, by adding some decoration, here, we added a "+", but could have
been:

=[FirstName] & " " & [LastName]

bound to a control name FirstName, then, we are likely to get an error
unless you also change the control name.

Not evident, but once we know...

Hoping it may help,
Vanderghast, Access MVP

Hello,

[quoted text clipped - 10 lines]

-Rob-

 

[Rob Dykstra via AccessMonster.com]

OK, I feel pretty stupid, one of the sum controls had a misspelled field in
it and it was affecting all the other sum and total controls. As soon as a
fixed the spelling in the one, the rest all worked. That's pretty odd that
one misspelled control would affect all the others like that, I would think
it should have only affected the one. Oh well, it's working now. Thanks for
pointing me in the right direction, towards the spelling in the control
source.

-Rob-

Hi,

Can be something else but in a report, if a control, in the report, has the
name of a field but is not bind to that field, that makes an error.

Example, open Northwind, make a new report on table Categories. Bring the
field CategoryName in the de detail section, nothing else. That should work.
Now, edit the report in design view and change the control, called
CategoryName (not the label, the control), bound to the field of the same
name, and now, "bind" it by typing:

=[CategoryName] & "+"

in it. Now that the control CategoryName is bound to SOMETHING ELSE that the
field of the same name that its name, run the report and observe the error.

Back in design mode, change the control name from CategoryName to something
else that is NOT A FIELD name, and the error disappear.

Conclusion: if you have a control name = to a field name, they must be
bound together and to nothing else. If you hand edit a report generated by a
wizard, by adding some decoration, here, we added a "+", but could have
been:

=[FirstName] & " " & [LastName]

bound to a control name FirstName, then, we are likely to get an error
unless you also change the control name.

Not evident, but once we know...

Hoping it may help,
Vanderghast, Access MVP

Hello,

[quoted text clipped - 10 lines]

-Rob-