Having table totals appear on form in bound fields

[SteveC]

Worked like a charm. I did have FirstName and LastName fields but it was
simple to just add a new field to Staff Table for FullName so I used that!
I will start fresh in the morning on the other nagging problem

I also found the answer to why I could not get rid of or change
relationships. It had to do with a hidden table called MSOBjs or something
or other; I gave myself permissions to break links and got rid of ComputerID
on the Accessories table since I tie accessories like computers to the
location. I then had to do something or other to AccessoriesSbf to get them
to show on the Locations Form. Apparently, I cannot allow edits on the
subforms if I want them to show up; not sure why. That is for another day.
I got it to work. Thanks.

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

 

[John Vinson]

Worked like a charm. I did have FirstName and LastName fields but it was
simple to just add a new field to Staff Table for FullName so I used that!

Sorry... THAT IS THE WRONG WAY TO DO IT.

Calculate the FullName on the fly in the query:

SELECT Computers.*, Staff.Firstname & " " & Staff.LastName AS FullName
FROM ...


John W. Vinson[MVP]

 

[SteveC]

Hi John,

I knew it was not the right way, but it worked! It was late; I was tired. I
thought I would take a shortcut. Thanks!

 

[SteveC]

Thanks and answers below with additional questions! There are so many
objects and names.

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"

OK, I have this exactly for the ControlSource but the Subform properties
shows only Maintenance Tab. I changed it to MaintenanceSbf but it didn't
fix it. I have nothing about "Subform/Subreport:" there.

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
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Steve

John is quite right - delete that field! ... NOW!

There are two important principles here:

1. You should never duplicate data in multiple fields

2. You should never store more than one item of atomic data in a single
field.
(You can think of "atomic data" as meaning something that is individually
useful as a unit - for example, LastName - perhaps for sorting. The moment
you combine "John Smith" in a single field, you have one hell of a job
sorting by last name).

You can always combine atomic fields into an expression in a query, as John
indicated in his example:

Staff.Firstname & " " & Staff.LastName AS FullName
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Steve

OK, I have this exactly for the ControlSource but the Subform properties
shows only Maintenance Tab. I changed it to MaintenanceSbf but it didn't
fix it. I have nothing about "Subform/Subreport:" there.

Hmmm... it seems that you have changed the name of that tab page to
"MaintenanceSbf". The fact that you were able to do that suggests that your
subform control is NOT named "MaintenanceSbf" (you cannot have two controls
on a form, even of different types, with the same name). That would
DEFINITELY explain the #Name? problem.

Now, selecting a subform control, especially on a tab page, can be a little
bit fiddly. First, click on the tab to select it. The properties window
title should read "Page: <tab page name>". If the tab page is still named
"MaintenanceSbf", then change its name before proceeding - say to
"tpgMaintenance". Now, select the subform control by clicking ONLY ONCE on
the border of the subform. The properties window title should now be
"Subform/Subreport: <name of control>". If it says anything else, then
reselect the tab and try again. Once it is selected, change its name to
"MaintenanceSbf".

 

[SteveC]

OK, I see what you mean and have done it. It still didn't work. I renamed
the tabpage to tabMaintenance. I renamed the Subreport to
MaintenanceSubform (The control source for the TabPage is a form named
MaintenanceSbf, so I decided on another name change just to make sure.)

It didn't work, but I am clearer on things. Of course, I made sure I used
as the Control for txtMaintenanceCount
[MaintenanceSubform].[Form].[Recordset].[RecordCount]

It's a no go. Thanks.

Hi Steve

OK, I have this exactly for the ControlSource but the Subform properties
shows only Maintenance Tab. I changed it to MaintenanceSbf but it didn't
fix it. I have nothing about "Subform/Subreport:" there.

Hmmm... it seems that you have changed the name of that tab page to
"MaintenanceSbf". The fact that you were able to do that suggests that
your subform control is NOT named "MaintenanceSbf" (you cannot have two
controls on a form, even of different types, with the same name). That
would DEFINITELY explain the #Name? problem.

Now, selecting a subform control, especially on a tab page, can be a
little bit fiddly. First, click on the tab to select it. The properties
window title should read "Page: <tab page name>". If the tab page is
still named "MaintenanceSbf", then change its name before proceeding - say
to "tpgMaintenance". Now, select the subform control by clicking ONLY
ONCE on the border of the subform. The properties window title should now
be "Subform/Subreport: <name of control>". If it says anything else, then
reselect the tab and try again. Once it is selected, change its name to
"MaintenanceSbf".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Steve

We must be getting closer... hang in there!

OK, I see what you mean and have done it. It still didn't work. I
renamed the tabpage to tabMaintenance. I renamed the Subreport to
MaintenanceSubform (The control source for the TabPage is a form named
MaintenanceSbf, so I decided on another name change just to make sure.)

It didn't work, but I am clearer on things. Of course, I made sure I used
as the Control for txtMaintenanceCount
[MaintenanceSubform].[Form].[Recordset].[RecordCount]

Did that have an = sign in front of it?

=[MaintenanceSubform].[Form].[Recordset].[RecordCount]

 

[SteveC]

Yeah, but my way works. Now I have two fields with ?Name whatever. I am
about to throw in the towel. I DONT understand any of this. I am very
frustrated.

I try the query editor. I try using = before it; I try what you said and
what John said, and it don't work. I understand fields but I don't
understand queries. Why are they so damn hard?

In other places in the form where i have queries, I find two things. The
first says Table/Query; Underneath that it says Source and it has something
like the Staff.Firstname & " " & Staff.LastName AS FullName you gave me

BUT in my field all I have is Source and if I put in the query stuff, it
doesn't seem to know it is a query. It says no such field, of course.

You will have to tell me step by step what to do from scratch and just treat
me like an idiot. I have been at this database for 6weeks now.

Thanks.

Hi Steve

John is quite right - delete that field! ... NOW!

There are two important principles here:

1. You should never duplicate data in multiple fields

2. You should never store more than one item of atomic data in a single
field.
(You can think of "atomic data" as meaning something that is individually
useful as a unit - for example, LastName - perhaps for sorting. The
moment you combine "John Smith" in a single field, you have one hell of a
job sorting by last name).

You can always combine atomic fields into an expression in a query, as
John indicated in his example:

Staff.Firstname & " " & Staff.LastName AS FullName
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi John,

I knew it was not the right way, but it worked! It was late; I was tired.
I thought I would take a shortcut. Thanks!

 

[SteveC]

Yep, = was there.

Steve

We must be getting closer... hang in there!

OK, I see what you mean and have done it. It still didn't work. I
renamed the tabpage to tabMaintenance. I renamed the Subreport to
MaintenanceSubform (The control source for the TabPage is a form named
MaintenanceSbf, so I decided on another name change just to make sure.)

It didn't work, but I am clearer on things. Of course, I made sure I
used as the Control for txtMaintenanceCount
[MaintenanceSubform].[Form].[Recordset].[RecordCount]

Did that have an = sign in front of it?

=[MaintenanceSubform].[Form].[Recordset].[RecordCount]

 

[SteveC]

Thanks Graham for all your help. I see that I needed to use =Count(*) for
the MaintenanceSubForm box. Otherwise I had it right. I would never have
gotten there without your help. We also found a bug in Access2007, so I
feel like that accomplished something too.

Steve