Having table totals appear on form in bound fields

[SteveC]

Using Access 2007, I have a form with a subform. The subform is seen in
datasheet view and I have used the built in total mechanism to show the
total count (computers) and sum total (purchase price). This works nicely.
But since the totals are in the subforms in tab pages, they are not always
visible. I would like to show these totals in a field in the main form.
And to make it a bit more complicated, I would like to add the purchase
price from one subform (computers) with another tabbed subform (accessories)
to show the total expenditure per location.

Thanks. I am finishing up, so will try not to bother y'all with further
questions for a while.

 

[Graham Mandeno]

Hi Steve

You can bind the controlsource of a textbox on your main form to the value
of another textbox on a subform.

For example, create a textbox on your main form named txtComputersTotalPrice
and set its ControlSource to:

=[sbfComputers].Form![txtTotalPrice]

(where sbfComputers is the name of the subform control containing your
"Computers" subform, and txtTotalPrice is the name of the textbox on that
form containing the =Sum() expression)

You can do the same thing for the Accessories total and then add a third
textbox with ControlSource:

=[txtComputersTotalPrice] + [txtAccessoriesTotalPrice]

 

[SteveC]

Thanks. It does not seem to work.

When I make the textbox controlsource
=[sbfAccessories].Form![txtTotalPrice], Access insists on putting Form! into
brackets as .[Form]! Even so, I get #Name? as a response.

I tried the same thing for Computers and it didn't work either.

Hi Steve

You can bind the controlsource of a textbox on your main form to the value
of another textbox on a subform.

For example, create a textbox on your main form named
txtComputersTotalPrice and set its ControlSource to:

=[sbfComputers].Form![txtTotalPrice]

(where sbfComputers is the name of the subform control containing your
"Computers" subform, and txtTotalPrice is the name of the textbox on that
form containing the =Sum() expression)

You can do the same thing for the Accessories total and then add a third
textbox with ControlSource:

=[txtComputersTotalPrice] + [txtAccessoriesTotalPrice]

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using Access 2007, I have a form with a subform. The subform is seen in
datasheet view and I have used the built in total mechanism to show the
total count (computers) and sum total (purchase price). This works
nicely.
But since the totals are in the subforms in tab pages, they are not
always
visible. I would like to show these totals in a field in the main form.
And to make it a bit more complicated, I would like to add the purchase
price from one subform (computers) with another tabbed subform
(accessories)
to show the total expenditure per location.

Thanks. I am finishing up, so will try not to bother y'all with further
questions for a while.

 

[SteveC]

=[Forms]![Accessories Subform]![SumAccessoriesCost]

This is how the query editor wrote the controlsource for
TotalAccessoriesCost; it still doesn't work though.

Thanks. It does not seem to work.

When I make the textbox controlsource
=[sbfAccessories].Form![txtTotalPrice], Access insists on putting Form!
into brackets as .[Form]! Even so, I get #Name? as a response.

I tried the same thing for Computers and it didn't work either.

Hi Steve

You can bind the controlsource of a textbox on your main form to the
value of another textbox on a subform.

For example, create a textbox on your main form named
txtComputersTotalPrice and set its ControlSource to:

=[sbfComputers].Form![txtTotalPrice]

(where sbfComputers is the name of the subform control containing your
"Computers" subform, and txtTotalPrice is the name of the textbox on that
form containing the =Sum() expression)

You can do the same thing for the Accessories total and then add a third
textbox with ControlSource:

=[txtComputersTotalPrice] + [txtAccessoriesTotalPrice]

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using Access 2007, I have a form with a subform. The subform is seen in
datasheet view and I have used the built in total mechanism to show the
total count (computers) and sum total (purchase price). This works
nicely.
But since the totals are in the subforms in tab pages, they are not
always
visible. I would like to show these totals in a field in the main form.
And to make it a bit more complicated, I would like to add the purchase
price from one subform (computers) with another tabbed subform
(accessories)
to show the total expenditure per location.

Thanks. I am finishing up, so will try not to bother y'all with further
questions for a while.

 

[Graham Mandeno]

Hi Steve

See inline...

=[Forms]![Accessories Subform]![SumAccessoriesCost]

This is how the query editor wrote the controlsource for
TotalAccessoriesCost; it still doesn't work though.

Do you mean the expression builder? It is wrong! This reference would be
correct only if the form "Accessories Subform" were opened as a main form.

When I make the textbox controlsource
=[sbfAccessories].Form![txtTotalPrice], Access insists on putting Form!
into brackets as .[Form]! Even so, I get #Name? as a response.

Yes, the expression parser adds the brackets even thoough they are not
necessary. This is not a problem.

Note that I said:

=[sbfComputers].Form![txtTotalPrice]

(where sbfComputers is the name of the subform control containing your
"Computers" subform, and txtTotalPrice is the name of the textbox on
that form containing the =Sum() expression)

It sounds like you have just copied my expression without adjusting the
names of the controls.

First, check the Name property of the subform control containing Accessories
Subform. Chances are it is "Accessories Subform". Change it to
"sbfAccessories" (there are lots of reasons for avoiding blanks and
non-alphanumeric characters in object names).

Then, check the name of the textbox containing the total on the subform. It
seems it is "SumAccessoriesCost". That's fine.

Now, set the controlsource of your textbox on the main form to:

=[sbfAccessories].[Form]![SumAccessoriesCost]

(the brackets are optional - they will be provided for you anyway.

Go through the same process for the Computers subform. Then you can add a
third textbox to sum the other two.

 

[SteveC]

Hi Graham,

Thanks so much. I followed your advice and got it to work, but there are
new problems. It works but when I go back to the Form after closing it, the
#Name? thing comes up again. I found that if I opened the form Divisions
first, it would work. My workaround is a macro that opens and closes
Divisions when I open Locations. LOL. What a kludge!

There is some macro stuff built in that I don't understand. I started with
a canned asset database template from MS and these macros have wreaked havoc
off and on. I have edited most of them and gotten rid of some, but getting
rid of them has its dangers.

Divisions is nothing but a simple form for adding departments, e.g,
academic, administrative, library, and computer labs. Just so I can do
reports later. The Computers are identitified by ComputerID, so this
Divisions isn't even a relationship. DivisionID on table Divisions is
related to DivsionID on the Computers table.

OnLoad Divisions runs AssetsMacros. There isn't anything there except some
macros for opening forms from another form. I don't get it.

Getting back to the orginal question, one thing I notice is when there are
no computers for a location, the field reads "#Error!" The Accessoris field
is just blank. Both should be $0.00. Thanks for your suggestions.

Steve

Hi Steve

See inline...

=[Forms]![Accessories Subform]![SumAccessoriesCost]

This is how the query editor wrote the controlsource for
TotalAccessoriesCost; it still doesn't work though.

Do you mean the expression builder? It is wrong! This reference would be
correct only if the form "Accessories Subform" were opened as a main form.

When I make the textbox controlsource
=[sbfAccessories].Form![txtTotalPrice], Access insists on putting Form!
into brackets as .[Form]! Even so, I get #Name? as a response.

Yes, the expression parser adds the brackets even thoough they are not
necessary. This is not a problem.

Note that I said:

=[sbfComputers].Form![txtTotalPrice]

(where sbfComputers is the name of the subform control containing your
"Computers" subform, and txtTotalPrice is the name of the textbox on
that form containing the =Sum() expression)

It sounds like you have just copied my expression without adjusting the
names of the controls.

First, check the Name property of the subform control containing
Accessories Subform. Chances are it is "Accessories Subform". Change it
to "sbfAccessories" (there are lots of reasons for avoiding blanks and
non-alphanumeric characters in object names).

Then, check the name of the textbox containing the total on the subform.
It seems it is "SumAccessoriesCost". That's fine.

Now, set the controlsource of your textbox on the main form to:

=[sbfAccessories].[Form]![SumAccessoriesCost]

(the brackets are optional - they will be provided for you anyway.

Go through the same process for the Computers subform. Then you can add a
third textbox to sum the other two.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Steve

Inline again...

Thanks so much.

You're welcome

I followed your advice and got it to work, but there are new problems. It
works but when I go back to the Form after closing it, the #Name? thing
comes up again. I found that if I opened the form Divisions first, it
would work. My workaround is a macro that opens and closes Divisions when
I open Locations. LOL. What a kludge!

It's possible that there is a timing issue when the form is loading, so it's
trying to calculate the value in the textbox on the main form before the
subform has completed loading. Possibly opening and closing the Divisions
form is giving it time to get its act together before it renders the
textboxes on the main form.

Try adding this code to the Form_Current event procedure of your main form:

DoEvents
txtSumAccessoriesCost.Requery
txtSumComputersCost.Requery

(where txtSumAccessoriesCost and txtSumComputersCost are the names of the
two textboxes on your main form)

There is some macro stuff built in that I don't understand. I started
with a canned asset database template from MS and these macros have
wreaked havoc off and on. I have edited most of them and gotten rid of
some, but getting rid of them has its dangers.

I'm not familiar with that template database, but I would be very suspicious
of a template that uses lots of macros.

Divisions is nothing but a simple form for adding departments, e.g,
academic, administrative, library, and computer labs. Just so I can do
reports later. The Computers are identitified by ComputerID, so this
Divisions isn't even a relationship. DivisionID on table Divisions is
related to DivsionID on the Computers table.

OnLoad Divisions runs AssetsMacros. There isn't anything there except
some macros for opening forms from another form. I don't get it.

Cleaning up something like this can be a real pain. There are two possible
angles of approach:

1. Leave it alone on the principle of "if it ain't broke, don't fix it."

2. Take a copy of the form you are tinkering with (for backup) and then
remove stuff that seems to be unnecessary, testing after each step to see if
you've broken anything. If you're happy after a few modifications, take
another backup copy before continuing.

Getting back to the orginal question, one thing I notice is when there are
no computers for a location, the field reads "#Error!" The Accessoris
field is just blank. Both should be $0.00. Thanks for your suggestions.

OK. I think this is probably because you don't have AllowAdditions on your
subform. This means that there are no records to count or sum, not even a
new empty one. (Personally I think this is a bug in Access - both Count and
Sum should return zero in this case, or at least Null).

Anyway, the way around it is as follows:

1. Add a textbox to your main form named txtComputerCount. Set its
ControlSource to:
=[sbfComputers].[Form].[Recordset].[RecordCount]
(note that you are now using the form's recordset to count the records,
not the Count function)
If you wish, you can make this textbox invisible.

2. Change the ControlSource of your txtSumComputersCost textbox from:
=[sbfComputers].[Form]![SumComputersCost]
to
=IIf([txtComputerCount]=0, 0,
[sbfComputers].[Form]![SumComputersCost])
(in other words, show the sum if there are records, otherwise show 0)

If you're lucky, you might find that the Requery code in Form_Current is now
unnecessary

 

[SteveC]

Thanks again Graham. I was able to clean up the form and get rid of the
macros (don't ask me how; I stabbed in the dark and won.). I have it all
working pretty well. I cannot get the total expenditures of maintenance and
computer cost to add when there is nothing in maintenance, which is the
norm. I can live without it. I was going to try your txtcount thing, but I
don't want to count the computers but to add the costs. I just don't
understand. Here is what you said.

Anyway, the way around it is as follows:

1. Add a textbox to your main form named txtComputerCount. Set its
ControlSource to:
=[sbfComputers].[Form].[Recordset].[RecordCount]
(note that you are now using the form's recordset to count the records,
not the Count function)
If you wish, you can make this textbox invisible.

2. Change the ControlSource of your txtSumComputersCost textbox from:
=[sbfComputers].[Form]![SumComputersCost]
to
=IIf([txtComputerCount]=0, 0,
[sbfComputers].[Form]![SumComputersCost])
(in other words, show the sum if there are records, otherwise show 0)

The Purchase cost is fine as every record has one; it is the Maintenance
Cost which is zero for all of them right now that I am having problems with.
I bought a thick book on Access and it is worthless. Everything I read in
it, I understand, but there aren't the things I want to know in it.

Hi Steve

Inline again...

Thanks so much.

You're welcome

I followed your advice and got it to work, but there are new problems.
It works but when I go back to the Form after closing it, the #Name?
thing comes up again. I found that if I opened the form Divisions first,
it would work. My workaround is a macro that opens and closes Divisions
when I open Locations. LOL. What a kludge!

It's possible that there is a timing issue when the form is loading, so
it's trying to calculate the value in the textbox on the main form before
the subform has completed loading. Possibly opening and closing the
Divisions form is giving it time to get its act together before it renders
the textboxes on the main form.

Try adding this code to the Form_Current event procedure of your main
form:

DoEvents
txtSumAccessoriesCost.Requery
txtSumComputersCost.Requery

(where txtSumAccessoriesCost and txtSumComputersCost are the names of the
two textboxes on your main form)

There is some macro stuff built in that I don't understand. I started
with a canned asset database template from MS and these macros have
wreaked havoc off and on. I have edited most of them and gotten rid of
some, but getting rid of them has its dangers.

I'm not familiar with that template database, but I would be very
suspicious of a template that uses lots of macros.

Divisions is nothing but a simple form for adding departments, e.g,
academic, administrative, library, and computer labs. Just so I can do
reports later. The Computers are identitified by ComputerID, so this
Divisions isn't even a relationship. DivisionID on table Divisions is
related to DivsionID on the Computers table.

OnLoad Divisions runs AssetsMacros. There isn't anything there except
some macros for opening forms from another form. I don't get it.

Cleaning up something like this can be a real pain. There are two
possible angles of approach:

1. Leave it alone on the principle of "if it ain't broke, don't fix it."

2. Take a copy of the form you are tinkering with (for backup) and then
remove stuff that seems to be unnecessary, testing after each step to see
if you've broken anything. If you're happy after a few modifications,
take another backup copy before continuing.

Getting back to the orginal question, one thing I notice is when there
are no computers for a location, the field reads "#Error!" The
Accessoris field is just blank. Both should be $0.00. Thanks for your
suggestions.

OK. I think this is probably because you don't have AllowAdditions on
your subform. This means that there are no records to count or sum, not
even a new empty one. (Personally I think this is a bug in Access - both
Count and Sum should return zero in this case, or at least Null).

Anyway, the way around it is as follows:

1. Add a textbox to your main form named txtComputerCount. Set its
ControlSource to:
=[sbfComputers].[Form].[Recordset].[RecordCount]
(note that you are now using the form's recordset to count the records,
not the Count function)
If you wish, you can make this textbox invisible.

2. Change the ControlSource of your txtSumComputersCost textbox from:
=[sbfComputers].[Form]![SumComputersCost]
to
=IIf([txtComputerCount]=0, 0,
[sbfComputers].[Form]![SumComputersCost])
(in other words, show the sum if there are records, otherwise show 0)

If you're lucky, you might find that the Requery code in Form_Current is
now unnecessary
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[SteveC]

By the way, it helped to make sure I could do additions but when I change
Data Entry to Yes, all my records disappear in the Computers form. I wonder
why?

Thanks again Graham. I was able to clean up the form and get rid of the
macros (don't ask me how; I stabbed in the dark and won.). I have it all
working pretty well. I cannot get the total expenditures of maintenance
and computer cost to add when there is nothing in maintenance, which is
the norm. I can live without it. I was going to try your txtcount thing,
but I don't want to count the computers but to add the costs. I just
don't understand. Here is what you said.

Anyway, the way around it is as follows:

1. Add a textbox to your main form named txtComputerCount. Set its
ControlSource to:
=[sbfComputers].[Form].[Recordset].[RecordCount]
(note that you are now using the form's recordset to count the
records, not the Count function)
If you wish, you can make this textbox invisible.

2. Change the ControlSource of your txtSumComputersCost textbox from:
=[sbfComputers].[Form]![SumComputersCost]
to
=IIf([txtComputerCount]=0, 0,
[sbfComputers].[Form]![SumComputersCost])
(in other words, show the sum if there are records, otherwise show 0)

The Purchase cost is fine as every record has one; it is the Maintenance
Cost which is zero for all of them right now that I am having problems
with. I bought a thick book on Access and it is worthless. Everything I
read in it, I understand, but there aren't the things I want to know in
it.

Hi Steve

Inline again...

Thanks so much.

You're welcome

I followed your advice and got it to work, but there are new problems.
It works but when I go back to the Form after closing it, the #Name?
thing comes up again. I found that if I opened the form Divisions
first, it would work. My workaround is a macro that opens and closes
Divisions when I open Locations. LOL. What a kludge!

It's possible that there is a timing issue when the form is loading, so
it's trying to calculate the value in the textbox on the main form before
the subform has completed loading. Possibly opening and closing the
Divisions form is giving it time to get its act together before it
renders the textboxes on the main form.

Try adding this code to the Form_Current event procedure of your main
form:

DoEvents
txtSumAccessoriesCost.Requery
txtSumComputersCost.Requery

(where txtSumAccessoriesCost and txtSumComputersCost are the names of the
two textboxes on your main form)

There is some macro stuff built in that I don't understand. I started
with a canned asset database template from MS and these macros have
wreaked havoc off and on. I have edited most of them and gotten rid of
some, but getting rid of them has its dangers.

I'm not familiar with that template database, but I would be very
suspicious of a template that uses lots of macros.

Divisions is nothing but a simple form for adding departments, e.g,
academic, administrative, library, and computer labs. Just so I can do
reports later. The Computers are identitified by ComputerID, so this
Divisions isn't even a relationship. DivisionID on table Divisions is
related to DivsionID on the Computers table.

OnLoad Divisions runs AssetsMacros. There isn't anything there except
some macros for opening forms from another form. I don't get it.

Cleaning up something like this can be a real pain. There are two
possible angles of approach:

1. Leave it alone on the principle of "if it ain't broke, don't fix it."

2. Take a copy of the form you are tinkering with (for backup) and then
remove stuff that seems to be unnecessary, testing after each step to see
if you've broken anything. If you're happy after a few modifications,
take another backup copy before continuing.

Getting back to the orginal question, one thing I notice is when there
are no computers for a location, the field reads "#Error!" The
Accessoris field is just blank. Both should be $0.00. Thanks for your
suggestions.

OK. I think this is probably because you don't have AllowAdditions on
your subform. This means that there are no records to count or sum, not
even a new empty one. (Personally I think this is a bug in Access - both
Count and Sum should return zero in this case, or at least Null).

Anyway, the way around it is as follows:

1. Add a textbox to your main form named txtComputerCount. Set its
ControlSource to:
=[sbfComputers].[Form].[Recordset].[RecordCount]
(note that you are now using the form's recordset to count the
records, not the Count function)
If you wish, you can make this textbox invisible.

2. Change the ControlSource of your txtSumComputersCost textbox from:
=[sbfComputers].[Form]![SumComputersCost]
to
=IIf([txtComputerCount]=0, 0,
[sbfComputers].[Form]![SumComputersCost])
(in other words, show the sum if there are records, otherwise show 0)

If you're lucky, you might find that the Requery code in Form_Current is
now unnecessary
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[John Vinson]

By the way, it helped to make sure I could do additions but when I change
Data Entry to Yes, all my records disappear in the Computers form. I wonder
why?

Because that's what Data Entry mode is designed to do: allow entry of
new records, but conceal existing ones.

If you want to see old records... don't use Data Entry mode.

John W. Vinson[MVP]

 

[SteveC]

Thanks John. I had wondered about making forms for new data entry. I just
didn't understand the meaning of data entry in this context.

 

[Graham Mandeno]

Hi Steve

The idea of using a textbox to count the records in the subform is simply so
you can get around the problem of "#Error!" when there are no records to
sum. You don't need to *display* the txtCount textbox if you don't want
to - just set its Visible property to False.

If there are records in your subform, but all of them have Null in the
Maintenance field, then =Sum([Maintenance]) will return Null also. Then, if
you try to add Null to a number, that also will return Null. The trick is
to use the Nz function to convert the Null to zero, and then you can add it.
So change the SumMaintenance control source to:
=Nz(Sum([Maintenance]), 0)

As John explained, there is a difference between DataEntry and
AllowAdditions. DataEntry suppresses the display of existing records,
allowing you only to add new ones. If DataEntry is False and AllowAdditions
is True, then you can view/edit existing records *and* add new ones.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks again Graham. I was able to clean up the form and get rid of the
macros (don't ask me how; I stabbed in the dark and won.). I have it all
working pretty well. I cannot get the total expenditures of maintenance
and computer cost to add when there is nothing in maintenance, which is
the norm. I can live without it. I was going to try your txtcount thing,
but I don't want to count the computers but to add the costs. I just
don't understand. Here is what you said.

Anyway, the way around it is as follows:

1. Add a textbox to your main form named txtComputerCount. Set its
ControlSource to:
=[sbfComputers].[Form].[Recordset].[RecordCount]
(note that you are now using the form's recordset to count the
records, not the Count function)
If you wish, you can make this textbox invisible.

2. Change the ControlSource of your txtSumComputersCost textbox from:
=[sbfComputers].[Form]![SumComputersCost]
to
=IIf([txtComputerCount]=0, 0,
[sbfComputers].[Form]![SumComputersCost])
(in other words, show the sum if there are records, otherwise show 0)

The Purchase cost is fine as every record has one; it is the Maintenance
Cost which is zero for all of them right now that I am having problems
with. I bought a thick book on Access and it is worthless. Everything I
read in it, I understand, but there aren't the things I want to know in
it.

Hi Steve

Inline again...

Thanks so much.

You're welcome

I followed your advice and got it to work, but there are new problems.
It works but when I go back to the Form after closing it, the #Name?
thing comes up again. I found that if I opened the form Divisions
first, it would work. My workaround is a macro that opens and closes
Divisions when I open Locations. LOL. What a kludge!

It's possible that there is a timing issue when the form is loading, so
it's trying to calculate the value in the textbox on the main form before
the subform has completed loading. Possibly opening and closing the
Divisions form is giving it time to get its act together before it
renders the textboxes on the main form.

Try adding this code to the Form_Current event procedure of your main
form:

DoEvents
txtSumAccessoriesCost.Requery
txtSumComputersCost.Requery

(where txtSumAccessoriesCost and txtSumComputersCost are the names of the
two textboxes on your main form)

There is some macro stuff built in that I don't understand. I started
with a canned asset database template from MS and these macros have
wreaked havoc off and on. I have edited most of them and gotten rid of
some, but getting rid of them has its dangers.

I'm not familiar with that template database, but I would be very
suspicious of a template that uses lots of macros.

Divisions is nothing but a simple form for adding departments, e.g,
academic, administrative, library, and computer labs. Just so I can do
reports later. The Computers are identitified by ComputerID, so this
Divisions isn't even a relationship. DivisionID on table Divisions is
related to DivsionID on the Computers table.

OnLoad Divisions runs AssetsMacros. There isn't anything there except
some macros for opening forms from another form. I don't get it.

Cleaning up something like this can be a real pain. There are two
possible angles of approach:

1. Leave it alone on the principle of "if it ain't broke, don't fix it."

2. Take a copy of the form you are tinkering with (for backup) and then
remove stuff that seems to be unnecessary, testing after each step to see
if you've broken anything. If you're happy after a few modifications,
take another backup copy before continuing.

Getting back to the orginal question, one thing I notice is when there
are no computers for a location, the field reads "#Error!" The
Accessoris field is just blank. Both should be $0.00. Thanks for your
suggestions.

OK. I think this is probably because you don't have AllowAdditions on
your subform. This means that there are no records to count or sum, not
even a new empty one. (Personally I think this is a bug in Access - both
Count and Sum should return zero in this case, or at least Null).

Anyway, the way around it is as follows:

1. Add a textbox to your main form named txtComputerCount. Set its
ControlSource to:
=[sbfComputers].[Form].[Recordset].[RecordCount]
(note that you are now using the form's recordset to count the
records, not the Count function)
If you wish, you can make this textbox invisible.

2. Change the ControlSource of your txtSumComputersCost textbox from:
=[sbfComputers].[Form]![SumComputersCost]
to
=IIf([txtComputerCount]=0, 0,
[sbfComputers].[Form]![SumComputersCost])
(in other words, show the sum if there are records, otherwise show 0)

If you're lucky, you might find that the Requery code in Form_Current is
now unnecessary
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[SteveC]

OK, Graham, I have wasted enough of your time, and you have my deepest
appreciation. What you wrote worked like a charm on my Locations Form for
accessories and computers cost. However, it does not seem to work on my
Computers Form with Maintenance Subform.

Current Configuration: It works if totals are not null

On MaintenanceSbf, I have a field that is labeled Total Maintenance Cost and
the Control Source is =Sum[MaintenanceCost])
On Computers form, I have a field that is labeled Total Maintenance and the
Control Source is
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID]))

I tried to change it to:

On MaintenanceSbf, Total Maintenance Cost and the Control Source is
=Nz(Sum([MaintenanceCost]), 0)
On Computers form, I have a field that is labeled Total Maintenance and the
Control Source is
[MaintenanceSbf].Form![Total Maintenance Cost]

This begets #Name?

I think the problem may be something to do with ComputerID stuff. Thanks
again, and I promise this is my last post.

 

[Graham Mandeno]

Hi Steve

Let's get a few things straight here. I understand that:

1. Your "Computers form" is a main form

2. It contains a subform control named "MaintenanceSbf"

3. This subform control has both LlinkMasterFields and LinkChildFields set
to "ComputerID"

4. The subform contained in that control has a field named "MaintenanceCost"

5. You have a textbox in the footer of that subform named "Total Maintenance
Cost" (BTW, I advise against using non-alphanumeric characters, including
spaces, in fieldnames or control names).

6. The control source of this textbox is:
=Nz(Sum([MaintenanceCost]), 0)

7. On the main form you have a textbox whose control source is:
=[MaintenanceSbf].[Form]![Total Maintenance Cost]

Please tell me if ANY of the premises above is incorrect.

Now some questions: What do you see in (a) the total textbox on the subform
and (b) the total textbox on the main form in each of the following cases:

1. The current computer has one or more maintenance records with a non-null
value for MaintenanceCost?

2. The current computer has one or more maintenance records, but they all
have a null value for MaintenanceCost?

3. The current computer does not have any related maintenance records?

Am I correct that the problem occurs only in the third case?

If so, then add another textbox to your main form named
"txtMaintenanceCount". Set its control source to the following:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

Now, change the controlsource of the textbox in (7) above to:
=IIf([txtMaintenanceCount]=0, 0, [MaintenanceSbf].[Form]![Total
Maintenance Cost])

Try it and report back.

Don't feel that you're wasting anybody's time. We do this because we like
to help. I would only consider I had wasted my time if you gave up before
reaching a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, Graham, I have wasted enough of your time, and you have my deepest
appreciation. What you wrote worked like a charm on my Locations Form for
accessories and computers cost. However, it does not seem to work on my
Computers Form with Maintenance Subform.

Current Configuration: It works if totals are not null

On MaintenanceSbf, I have a field that is labeled Total Maintenance Cost
and
the Control Source is =Sum[MaintenanceCost])
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID]))

I tried to change it to:

On MaintenanceSbf, Total Maintenance Cost and the Control Source is
=Nz(Sum([MaintenanceCost]), 0)
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
[MaintenanceSbf].Form![Total Maintenance Cost]

This begets #Name?

I think the problem may be something to do with ComputerID stuff. Thanks
again, and I promise this is my last post.

Hi Steve

The idea of using a textbox to count the records in the subform is simply
so you can get around the problem of "#Error!" when there are no records
to sum. You don't need to *display* the txtCount textbox if you don't
want to - just set its Visible property to False.

If there are records in your subform, but all of them have Null in the
Maintenance field, then =Sum([Maintenance]) will return Null also. Then,
if you try to add Null to a number, that also will return Null. The
trick is to use the Nz function to convert the Null to zero, and then you
can add it. So change the SumMaintenance control source to:
=Nz(Sum([Maintenance]), 0)

As John explained, there is a difference between DataEntry and
AllowAdditions. DataEntry suppresses the display of existing records,
allowing you only to add new ones. If DataEntry is False and
AllowAdditions is True, then you can view/edit existing records *and* add
new ones.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[SteveC]

Answers below, and thanks!

Hi Steve

Let's get a few things straight here. I understand that:

1. Your "Computers form" is a main form Yes

2. It contains a subform control named "MaintenanceSbf" Yes

3. This subform control has both LlinkMasterFields and LinkChildFields set

to "ComputerID"Yes (

4. The subform contained in that control has a field named
"MaintenanceCost"

5. You have a textbox in the footer of that subform named "Total
Maintenance Cost" (BTW, I advise against using non-alphanumeric
characters, including spaces, in fieldnames or control names). I changed
it to TotalMaintenanceCost

6. The control source of this textbox is:
=Nz(Sum([MaintenanceCost]), 0)

7. On the main form you have a textbox whose control source is:
=[MaintenanceSbf].[Form]![Total Maintenance Cost]

Please tell me if ANY of the premises above is incorrect.

Now some questions: What do you see in (a) the total textbox on the
subform and (b) the total textbox on the main form in each of the
following cases:

1. The current computer has one or more maintenance records with a
non-null value for MaintenanceCost?

2. The current computer has one or more maintenance records, but they all
have a null value for MaintenanceCost?

3. The current computer does not have any related maintenance records?

Am I correct that the problem occurs only in the third case? Yes

If so, then add another textbox to your main form named
"txtMaintenanceCount". Set its control source to the following:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

Now, change the controlsource of the textbox in (7) above to:
=IIf([txtMaintenanceCount]=0, 0, [MaintenanceSbf].[Form]![Total
Maintenance Cost])

Try it and report back. It didn't work. The txtMaintenanceCount returns
Name? and the box (7 above) is also Name?

Don't feel that you're wasting anybody's time. We do this because we like
to help. I would only consider I had wasted my time if you gave up before
reaching a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, Graham, I have wasted enough of your time, and you have my deepest
appreciation. What you wrote worked like a charm on my Locations Form
for
accessories and computers cost. However, it does not seem to work on my
Computers Form with Maintenance Subform.

Current Configuration: It works if totals are not null

On MaintenanceSbf, I have a field that is labeled Total Maintenance Cost
and
the Control Source is =Sum[MaintenanceCost])
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID]))

I tried to change it to:

On MaintenanceSbf, Total Maintenance Cost and the Control Source is
=Nz(Sum([MaintenanceCost]), 0)
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
[MaintenanceSbf].Form![Total Maintenance Cost]

This begets #Name?

I think the problem may be something to do with ComputerID stuff. Thanks
again, and I promise this is my last post.

Hi Steve

The idea of using a textbox to count the records in the subform is
simply so you can get around the problem of "#Error!" when there are no
records to sum. You don't need to *display* the txtCount textbox if you
don't want to - just set its Visible property to False.

If there are records in your subform, but all of them have Null in the
Maintenance field, then =Sum([Maintenance]) will return Null also.
Then, if you try to add Null to a number, that also will return Null.
The trick is to use the Nz function to convert the Null to zero, and
then you can add it. So change the SumMaintenance control source to:
=Nz(Sum([Maintenance]), 0)

As John explained, there is a difference between DataEntry and
AllowAdditions. DataEntry suppresses the display of existing records,
allowing you only to add new ones. If DataEntry is False and
AllowAdditions is True, then you can view/edit existing records *and*
add new ones.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Steve

It's hard to see what you added below. The convention is to put inline
answers on new lines without the > prefix.

Nevertheless, I have trouble understanding why you would get "#Name?" under
some circumstances (no records) and not others. "#Error" I could
understand, but not "#Name?".

Are you *sure* that txtMaintenanceCount and the TotalMaintenanceCost textbox
on your main form both show the correct values if there is at least one
record in the subform?

You say that txtMaintenanceCount returns "#Name?". That implies its
ControlSource is referring to something whose name can't be resolved.

Can you confirm that its ControlSource is:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

You never said which version of Access you are using. Is it prior to Access
2000? If so, then a Form object does not have a Recordset property, so you
must use RecordsetClone instead:
=[MaintenanceSbf].[Form].[RecordsetClone].[RecordCount]


--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Answers below, and thanks!

Hi Steve

Let's get a few things straight here. I understand that:

1. Your "Computers form" is a main form Yes

2. It contains a subform control named "MaintenanceSbf" Yes

3. This subform control has both LlinkMasterFields and LinkChildFields
set

to "ComputerID"Yes (

4. The subform contained in that control has a field named
"MaintenanceCost"

5. You have a textbox in the footer of that subform named "Total
Maintenance Cost" (BTW, I advise against using non-alphanumeric
characters, including spaces, in fieldnames or control names). I changed
it to TotalMaintenanceCost

6. The control source of this textbox is:
=Nz(Sum([MaintenanceCost]), 0)

7. On the main form you have a textbox whose control source is:
=[MaintenanceSbf].[Form]![Total Maintenance Cost]

Please tell me if ANY of the premises above is incorrect.

Now some questions: What do you see in (a) the total textbox on the
subform and (b) the total textbox on the main form in each of the
following cases:

1. The current computer has one or more maintenance records with a
non-null value for MaintenanceCost?

2. The current computer has one or more maintenance records, but they all
have a null value for MaintenanceCost?

3. The current computer does not have any related maintenance records?

Am I correct that the problem occurs only in the third case? Yes

If so, then add another textbox to your main form named
"txtMaintenanceCount". Set its control source to the following:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

Now, change the controlsource of the textbox in (7) above to:
=IIf([txtMaintenanceCount]=0, 0, [MaintenanceSbf].[Form]![Total
Maintenance Cost])

Try it and report back. It didn't work. The txtMaintenanceCount returns
Name? and the box (7 above) is also Name?

Don't feel that you're wasting anybody's time. We do this because we
like
to help. I would only consider I had wasted my time if you gave up before
reaching a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, Graham, I have wasted enough of your time, and you have my deepest
appreciation. What you wrote worked like a charm on my Locations Form
for
accessories and computers cost. However, it does not seem to work on my
Computers Form with Maintenance Subform.

Current Configuration: It works if totals are not null

On MaintenanceSbf, I have a field that is labeled Total Maintenance Cost
and
the Control Source is =Sum[MaintenanceCost])
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID]))

I tried to change it to:

On MaintenanceSbf, Total Maintenance Cost and the Control Source is
=Nz(Sum([MaintenanceCost]), 0)
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
[MaintenanceSbf].Form![Total Maintenance Cost]

This begets #Name?

I think the problem may be something to do with ComputerID stuff.
Thanks
again, and I promise this is my last post.

Hi Steve

The idea of using a textbox to count the records in the subform is
simply so you can get around the problem of "#Error!" when there are no
records to sum. You don't need to *display* the txtCount textbox if
you
don't want to - just set its Visible property to False.

If there are records in your subform, but all of them have Null in the
Maintenance field, then =Sum([Maintenance]) will return Null also.
Then, if you try to add Null to a number, that also will return Null.
The trick is to use the Nz function to convert the Null to zero, and
then you can add it. So change the SumMaintenance control source to:
=Nz(Sum([Maintenance]), 0)

As John explained, there is a difference between DataEntry and
AllowAdditions. DataEntry suppresses the display of existing records,
allowing you only to add new ones. If DataEntry is False and
AllowAdditions is True, then you can view/edit existing records *and*
add new ones.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[SteveC]

Sorry about my failure to make clear. I am confused anyhow. I am using
Access 2007; never had it before. I think Access is too damn complicated.
Anyhow, I decided to do try to leanr this version. I am saving file in
Access 2003 format and making sure it works with 2003, which it does.

Answers below

Hi Steve

It's hard to see what you added below. The convention is to put inline
answers on new lines without the > prefix.

Nevertheless, I have trouble understanding why you would get "#Name?"
under some circumstances (no records) and not others. "#Error" I could
understand, but not "#Name?".

Are you *sure* that txtMaintenanceCount and the TotalMaintenanceCost
textbox on your main form both show the correct values if there is at
least one record in the subform?

txtMaintenanceCount shows #Name? no matter what. It doesn't work at all.

When I have
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID])) for TotalMaintenanceCost box (7), it will work it will show
the correct values if I have at least one record and if I have an AMOUNT of
some kind (the amount can be 0.00 but not blank (null?). Is null blank?
Null is nothing right, so nothing means blank, not zero? I am not a
mathematician. So I may be confused.

You say that txtMaintenanceCount returns "#Name?". That implies its
ControlSource is referring to something whose name can't be resolved.

Can you confirm that its ControlSource is:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

You never said which version of Access you are using. Is it prior to
Access 2000? If so, then a Form object does not have a Recordset
property, so you must use RecordsetClone instead:
=[MaintenanceSbf].[Form].[RecordsetClone].[RecordCount]


--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Answers below, and thanks!

Hi Steve

Let's get a few things straight here. I understand that:

1. Your "Computers form" is a main form Yes

2. It contains a subform control named "MaintenanceSbf" Yes

3. This subform control has both LlinkMasterFields and LinkChildFields
set
to "ComputerID"Yes (

4. The subform contained in that control has a field named
"MaintenanceCost"

5. You have a textbox in the footer of that subform named "Total
Maintenance Cost" (BTW, I advise against using non-alphanumeric
characters, including spaces, in fieldnames or control names). I
changed it to TotalMaintenanceCost

6. The control source of this textbox is:
=Nz(Sum([MaintenanceCost]), 0)

7. On the main form you have a textbox whose control source is:
=[MaintenanceSbf].[Form]![Total Maintenance Cost]

Please tell me if ANY of the premises above is incorrect.

Now some questions: What do you see in (a) the total textbox on the
subform and (b) the total textbox on the main form in each of the
following cases:

1. The current computer has one or more maintenance records with a
non-null value for MaintenanceCost?

2. The current computer has one or more maintenance records, but they
all
have a null value for MaintenanceCost?

3. The current computer does not have any related maintenance records?

Am I correct that the problem occurs only in the third case? Yes

If so, then add another textbox to your main form named
"txtMaintenanceCount". Set its control source to the following:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

Now, change the controlsource of the textbox in (7) above to:
=IIf([txtMaintenanceCount]=0, 0, [MaintenanceSbf].[Form]![Total
Maintenance Cost])

Try it and report back. It didn't work. The txtMaintenanceCount
returns Name? and the box (7 above) is also Name?

Don't feel that you're wasting anybody's time. We do this because we
like
to help. I would only consider I had wasted my time if you gave up
before
reaching a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


OK, Graham, I have wasted enough of your time, and you have my deepest
appreciation. What you wrote worked like a charm on my Locations Form
for
accessories and computers cost. However, it does not seem to work on
my
Computers Form with Maintenance Subform.

Current Configuration: It works if totals are not null

On MaintenanceSbf, I have a field that is labeled Total Maintenance
Cost
and
the Control Source is =Sum[MaintenanceCost])
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID]))

I tried to change it to:

On MaintenanceSbf, Total Maintenance Cost and the Control Source is
=Nz(Sum([MaintenanceCost]), 0)
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
[MaintenanceSbf].Form![Total Maintenance Cost]

This begets #Name?

I think the problem may be something to do with ComputerID stuff.
Thanks
again, and I promise this is my last post.

Hi Steve

The idea of using a textbox to count the records in the subform is
simply so you can get around the problem of "#Error!" when there are
no
records to sum. You don't need to *display* the txtCount textbox if
you
don't want to - just set its Visible property to False.

If there are records in your subform, but all of them have Null in the
Maintenance field, then =Sum([Maintenance]) will return Null also.
Then, if you try to add Null to a number, that also will return Null.
The trick is to use the Nz function to convert the Null to zero, and
then you can add it. So change the SumMaintenance control source to:
=Nz(Sum([Maintenance]), 0)

As John explained, there is a difference between DataEntry and
AllowAdditions. DataEntry suppresses the display of existing records,
allowing you only to add new ones. If DataEntry is False and
AllowAdditions is True, then you can view/edit existing records *and*
add new ones.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[SteveC]

Graham,

Perhaps this info will also help. The Control Source for my Computers form
is:

SELECT Computers.*, Locations.StaffID FROM Locations RIGHT JOIN Computers ON
Locations.LocationID=Computers.LocationID;

This is because when I choose a room to add computers to or to add
maintenance, it is tied to the location. Each location is assigned to a
person, so I have a field from Staff Table (StaffID). This field shows when
I select the location; I cannot change it, but for some reason, it only
shows correctly, if it is as a drop down list (though locked) (Note: I
would prefer just to have a simple text field as the drop down button makes
it look as though it is selectable. Another dilemma). Anyhow to get the
Staff ID to show requires I do a query and this query changes the form's
Control Source to what I have above. Could this be the reason, the
maintenance Cost doesn't show? I don't see why since it will do correctly
when there is an amount other than null for the Maintenance Cost.

Thanks again for your patience.

Hi Steve

It's hard to see what you added below. The convention is to put inline
answers on new lines without the > prefix.

Nevertheless, I have trouble understanding why you would get "#Name?"
under some circumstances (no records) and not others. "#Error" I could
understand, but not "#Name?".

Are you *sure* that txtMaintenanceCount and the TotalMaintenanceCost
textbox on your main form both show the correct values if there is at
least one record in the subform?

You say that txtMaintenanceCount returns "#Name?". That implies its
ControlSource is referring to something whose name can't be resolved.

Can you confirm that its ControlSource is:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

You never said which version of Access you are using. Is it prior to
Access 2000? If so, then a Form object does not have a Recordset
property, so you must use RecordsetClone instead:
=[MaintenanceSbf].[Form].[RecordsetClone].[RecordCount]


--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Answers below, and thanks!

Hi Steve

Let's get a few things straight here. I understand that:

1. Your "Computers form" is a main form Yes

2. It contains a subform control named "MaintenanceSbf" Yes

3. This subform control has both LlinkMasterFields and LinkChildFields
set
to "ComputerID"Yes (

4. The subform contained in that control has a field named
"MaintenanceCost"

5. You have a textbox in the footer of that subform named "Total
Maintenance Cost" (BTW, I advise against using non-alphanumeric
characters, including spaces, in fieldnames or control names). I
changed it to TotalMaintenanceCost

6. The control source of this textbox is:
=Nz(Sum([MaintenanceCost]), 0)

7. On the main form you have a textbox whose control source is:
=[MaintenanceSbf].[Form]![Total Maintenance Cost]

Please tell me if ANY of the premises above is incorrect.

Now some questions: What do you see in (a) the total textbox on the
subform and (b) the total textbox on the main form in each of the
following cases:

1. The current computer has one or more maintenance records with a
non-null value for MaintenanceCost?

2. The current computer has one or more maintenance records, but they
all
have a null value for MaintenanceCost?

3. The current computer does not have any related maintenance records?

Am I correct that the problem occurs only in the third case? Yes

If so, then add another textbox to your main form named
"txtMaintenanceCount". Set its control source to the following:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

Now, change the controlsource of the textbox in (7) above to:
=IIf([txtMaintenanceCount]=0, 0, [MaintenanceSbf].[Form]![Total
Maintenance Cost])

Try it and report back. It didn't work. The txtMaintenanceCount
returns Name? and the box (7 above) is also Name?

Don't feel that you're wasting anybody's time. We do this because we
like
to help. I would only consider I had wasted my time if you gave up
before
reaching a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


OK, Graham, I have wasted enough of your time, and you have my deepest
appreciation. What you wrote worked like a charm on my Locations Form
for
accessories and computers cost. However, it does not seem to work on
my
Computers Form with Maintenance Subform.

Current Configuration: It works if totals are not null

On MaintenanceSbf, I have a field that is labeled Total Maintenance
Cost
and
the Control Source is =Sum[MaintenanceCost])
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID]))

I tried to change it to:

On MaintenanceSbf, Total Maintenance Cost and the Control Source is
=Nz(Sum([MaintenanceCost]), 0)
On Computers form, I have a field that is labeled Total Maintenance and
the
Control Source is
[MaintenanceSbf].Form![Total Maintenance Cost]

This begets #Name?

I think the problem may be something to do with ComputerID stuff.
Thanks
again, and I promise this is my last post.

Hi Steve

The idea of using a textbox to count the records in the subform is
simply so you can get around the problem of "#Error!" when there are
no
records to sum. You don't need to *display* the txtCount textbox if
you
don't want to - just set its Visible property to False.

If there are records in your subform, but all of them have Null in the
Maintenance field, then =Sum([Maintenance]) will return Null also.
Then, if you try to add Null to a number, that also will return Null.
The trick is to use the Nz function to convert the Null to zero, and
then you can add it. So change the SumMaintenance control source to:
=Nz(Sum([Maintenance]), 0)

As John explained, there is a difference between DataEntry and
AllowAdditions. DataEntry suppresses the display of existing records,
allowing you only to add new ones. If DataEntry is False and
AllowAdditions is True, then you can view/edit existing records *and*
add new ones.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Steve

Sorry - I haven't deserted you - just been away for the weekend.

Sorry about my failure to make clear. I am confused anyhow. I am using
Access 2007; never had it before.

I don't have a working version of Access 2007 installed just at the moment.
I removed it from a Virtual PC on one computer and am about to install the
latest beta on another. When I have done that I'll do some tests, but I
can't imagine the behaviour has changed.

I think Access is too damn complicated. Anyhow, I decided to do try to
leanr this version. I am saving file in Access 2003 format and making
sure it works with 2003, which it does.

I assure you, it would take far longer to create a similar application using
another development platform, such as dot-net

txtMaintenanceCount shows #Name? no matter what. It doesn't work at all.

OK - I say again:

That implies its ControlSource is referring to something whose name can't
be resolved.

Can you confirm that its ControlSource is:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

Where "MaintenanceSbf" is the *name of the control which contains your
subform*.

(Click on the border of your subform and the properties window should show
"Subform/Subreport: MaintenanceSbf"

When I have
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID])) for TotalMaintenanceCost box (7), it will work it will
show the correct values if I have at least one record and if I have an
AMOUNT of some kind (the amount can be 0.00 but not blank (null?). Is
null blank? Null is nothing right, so nothing means blank, not zero? I am
not a mathematician. So I may be confused.

DSum will return Null if (a) there are no records to select with that
ComputerID or (b) if every record selected has a Null value for [Maintenance
Cost].

Null means "no value". It appears as a blank field. It is different from
zero which is "a value of zero". It is also different from a zero-length
string ("") which also appears as a blank field (for a text field) but which
actually has a value of "a string with no characters in it".

If you perform any arithmetic or logical calculation involving Null then you
will get Null.
For example, 35 + Null = Null, True OR Null = Null

However, aggregate functions such as Sum and DSum ignore Null values, so if
ANY of the values being summed is NOT Null then your result will not be
Null.

Hope that's clear as mud

 

[Graham Mandeno]

Hi Steve

The reason you need a combo box to show the staff member's name is that the
Staff table is not included in your query, so you need the RowSource of the
combo box to do the lookup and translate the StaffID value into a
displayable name. You can get around that by adding the Staff table to your
query. I don't know what your field names are, but it should look something
like this:

SELECT Computers.*, Staff.StaffName FROM
(Staff RIGHT JOIN Locations ON Staff.StaffID = Locations.StaffID)
RIGHT JOIN Computers ON Locations.LocationID = Computers.LocationID;

You can then bind StaffName to a textbox and make it disabled and locked so
users don't think they can change it.

Note that you need the RIGHT JOINs only if there are some computers that
don't have a LocationID. If this is not the case, then use INNER JOINs
instead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Perhaps this info will also help. The Control Source for my Computers
form is:

SELECT Computers.*, Locations.StaffID FROM Locations RIGHT JOIN Computers
ON Locations.LocationID=Computers.LocationID;

This is because when I choose a room to add computers to or to add
maintenance, it is tied to the location. Each location is assigned to a
person, so I have a field from Staff Table (StaffID). This field shows
when I select the location; I cannot change it, but for some reason, it
only shows correctly, if it is as a drop down list (though locked) (Note:
I would prefer just to have a simple text field as the drop down button
makes it look as though it is selectable. Another dilemma). Anyhow to
get the Staff ID to show requires I do a query and this query changes the
form's Control Source to what I have above. Could this be the reason, the
maintenance Cost doesn't show? I don't see why since it will do correctly
when there is an amount other than null for the Maintenance Cost.

Thanks again for your patience.

Hi Steve

It's hard to see what you added below. The convention is to put inline
answers on new lines without the > prefix.

Nevertheless, I have trouble understanding why you would get "#Name?"
under some circumstances (no records) and not others. "#Error" I could
understand, but not "#Name?".

Are you *sure* that txtMaintenanceCount and the TotalMaintenanceCost
textbox on your main form both show the correct values if there is at
least one record in the subform?

You say that txtMaintenanceCount returns "#Name?". That implies its
ControlSource is referring to something whose name can't be resolved.

Can you confirm that its ControlSource is:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

You never said which version of Access you are using. Is it prior to
Access 2000? If so, then a Form object does not have a Recordset
property, so you must use RecordsetClone instead:
=[MaintenanceSbf].[Form].[RecordsetClone].[RecordCount]


--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Answers below, and thanks!
Hi Steve

Let's get a few things straight here. I understand that:

1. Your "Computers form" is a main form Yes

2. It contains a subform control named "MaintenanceSbf" Yes

3. This subform control has both LlinkMasterFields and LinkChildFields
set
to "ComputerID"Yes (

4. The subform contained in that control has a field named
"MaintenanceCost"

5. You have a textbox in the footer of that subform named "Total
Maintenance Cost" (BTW, I advise against using non-alphanumeric
characters, including spaces, in fieldnames or control names). I
changed it to TotalMaintenanceCost

6. The control source of this textbox is:
=Nz(Sum([MaintenanceCost]), 0)

7. On the main form you have a textbox whose control source is:
=[MaintenanceSbf].[Form]![Total Maintenance Cost]

Please tell me if ANY of the premises above is incorrect.

Now some questions: What do you see in (a) the total textbox on the
subform and (b) the total textbox on the main form in each of the
following cases:

1. The current computer has one or more maintenance records with a
non-null value for MaintenanceCost?

2. The current computer has one or more maintenance records, but they
all
have a null value for MaintenanceCost?

3. The current computer does not have any related maintenance records?

Am I correct that the problem occurs only in the third case? Yes

If so, then add another textbox to your main form named
"txtMaintenanceCount". Set its control source to the following:
=[MaintenanceSbf].[Form].[Recordset].[RecordCount]

Now, change the controlsource of the textbox in (7) above to:
=IIf([txtMaintenanceCount]=0, 0, [MaintenanceSbf].[Form]![Total
Maintenance Cost])

Try it and report back. It didn't work. The txtMaintenanceCount
returns Name? and the box (7 above) is also Name?

Don't feel that you're wasting anybody's time. We do this because we
like
to help. I would only consider I had wasted my time if you gave up
before
reaching a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


OK, Graham, I have wasted enough of your time, and you have my deepest
appreciation. What you wrote worked like a charm on my Locations Form
for
accessories and computers cost. However, it does not seem to work on
my
Computers Form with Maintenance Subform.

Current Configuration: It works if totals are not null

On MaintenanceSbf, I have a field that is labeled Total Maintenance
Cost
and
the Control Source is =Sum[MaintenanceCost])
On Computers form, I have a field that is labeled Total Maintenance
and
the
Control Source is
=IIf(IsNull([ComputerID]),0,DSum("[MaintenanceCost]","Maintenance","[ComputerID]="
& [ComputerID]))

I tried to change it to:

On MaintenanceSbf, Total Maintenance Cost and the Control Source is
=Nz(Sum([MaintenanceCost]), 0)
On Computers form, I have a field that is labeled Total Maintenance
and
the
Control Source is
[MaintenanceSbf].Form![Total Maintenance Cost]

This begets #Name?

I think the problem may be something to do with ComputerID stuff.
Thanks
again, and I promise this is my last post.

Hi Steve

The idea of using a textbox to count the records in the subform is
simply so you can get around the problem of "#Error!" when there are
no
records to sum. You don't need to *display* the txtCount textbox if
you
don't want to - just set its Visible property to False.

If there are records in your subform, but all of them have Null in
the
Maintenance field, then =Sum([Maintenance]) will return Null also.
Then, if you try to add Null to a number, that also will return Null.
The trick is to use the Nz function to convert the Null to zero, and
then you can add it. So change the SumMaintenance control source to:
=Nz(Sum([Maintenance]), 0)

As John explained, there is a difference between DataEntry and
AllowAdditions. DataEntry suppresses the display of existing
records,
allowing you only to add new ones. If DataEntry is False and
AllowAdditions is True, then you can view/edit existing records *and*
add new ones.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand