Summing it all up

  • Thread starter Thread starter grep
  • Start date Start date
G

grep

I have a form, frmOrders, with a subform sfrmOrderDetail. On frmOrders,
I'd like to have a calculated field which tallies up all the
detail-items' Costs. The pertinent fields on the subform are OrderID
(which is a link field to OrderID from the main page), Qty and Cost.

I can't seem to get it right.

Any suggestions?

grep
 
Open the Northwind sample database that installed with Access.
Look at the Orders form and its subform.
It does what you want.

The subform is based on a query where they use a calculated field to get the
extended price.

In the Footer section of the subform, they use an expression with =Sum() to
get the total.

Then the main form picks up that total from the subform.

Note that the main form does not *store* the total of the order in its
table. The total should be calculated on the fly, as Northwind does.
 
Actually, Allen, I'm having trouble with the same thing, and I have a
problem with the way they did it with Northwind.

In my subform, I have fields: [Qty] and [UnitPrice], and then a
calculated field [ExtPrice] which equals [Qty]*[UnitPrice]. In the
footer of the subform, just like Northwinds, I put [Subtotal], which is
=Sum([ExtPrice]).

On my main form, I have a field also called [Subtotal], which is
=VQuoteDetails.Form!ExtPrice (where VQuoteDetails is the name of the
subform object on the main form.) Again, this is like what they did in NW.

Now here's the problem. When I run the form, when I get to the first
record in the subform, I put Qty=2 and UnitPrice=22.30. This gave me an
ExtPrice of 44.60. On my mainform, Subtotal shows 44.60. I now go to the
second record in the subform. My mainform's Subtotal shows 0. I add some
values. Qty=2, UnitPrice=54.75. ExtPrice shows 109.60, as does my
mainform's Subtotal's field. The problem is that Subtotal on the
mainform *should* show 154.10, but it doesn't. It shows whatever the
value of the subform's Subtotal would be for a given record.

What am I doing wrong?

grep
 
Whoops - my bad. I was setting the main form's [Subtotal] to the
subform's [ExtPrice] by accident. Now I've got it right and it gives me
#Error. I checked the subform itself, and in Single view, the [Subtotal]
field gives me an error too.

I was able to get it to work using two queries, but what a pain!

grep
 
Some things to check:
1. In the subform, check the Name of the Subtotal text box, and set its
Format property to Currency so Access knows the data type.

2. In the main form, double check the Name property of the subform control
(not necessarily the same as its Source Object).

3. In the text box that gives the error, check:
- you included the equal sign;
- the subform name is correct;
- you included the ".Form" bit;
- you got the name of the subform text box right (Subtotal?);
- you set the Format property to Yes.

4. If the subform is read-only, or its AllowAdditions property is set to No,
it will go completely blank when there are no new records to display. Then
referring to the non-existent text box in the subform will cause #Error.

5. If the calcuation is actually more complex, make sure it cannot cause an
error such as division by zero, or invalid typecasting.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

grep said:
Whoops - my bad. I was setting the main form's [Subtotal] to the subform's
[ExtPrice] by accident. Now I've got it right and it gives me #Error. I
checked the subform itself, and in Single view, the [Subtotal] field gives
me an error too.

I was able to get it to work using two queries, but what a pain!

grep

Allen said:
Open the Northwind sample database that installed with Access.
Look at the Orders form and its subform.
It does what you want.

The subform is based on a query where they use a calculated field to get
the extended price.

In the Footer section of the subform, they use an expression with =Sum()
to get the total.

Then the main form picks up that total from the subform.

Note that the main form does not *store* the total of the order in its
table. The total should be calculated on the fly, as Northwind does.
Click to expand...
Click to expand...
 
Hmm... Still get an error.

1. Changed the Format property to Currency.
2. Check - I had it right, and since I'm using the Expression Builder to
construct the actual reference piece (below), I know I didn't misspell it.
3. All correct, except that I'm not sure what you meant by "set the
Format property to Yes". The field is a Currency field. How could I set
it to Yes?

Thanks, Allen.

grep
 
No. Set the Format to Currency, of course.

If it still doesn't figure, open the Immediate Window (Ctrl+G), and work
with it there until you get the reference right. In that context you can't
use Me, so try:
Forms("NameOfYourMainFormHere")![NameOfYourSubformControlHere].ControlType
That should give you 112

From there try going further:
Forms("NameOfYourMainFormHere")![NameOfYourSubformControlHere].Form![Subtotal]

You should be able to track down which part is the wrong name.

BTW, if you have not done so yet, make sure that you uncheck the boxes
under:
Tools | Options | General | Name AutoCorrect
The compact the database:
Tools | Database Utilitites | Compact
Explanation of the problems this "feature" can cause you:
http://members.iinet.net.au/~allenbrowne/bug-03.html
 
Allen,

Thanks for your continuing help. Here's what I've done/got:

I had not turned off the AutoCorrect "features", but have now done so.
I'll check out your descriptions later. Also, I compacted the database.

The Immediate Window, as per your suggestions, did come through with a
ControlType of 112, however, the latter attempts turned up one of two
errors, and I'm not sure what either mean in this context:

1. No current record
2. #3011 - The Microsoft Jet database engine could not find the object
". Make sure the object exists and that you spell its name and path name
correctly.

Now, I'm not sure what would cause the former error at all. I don't get
that error when I try to reach other fields on the same subform.

As for the latter, I looked around for an extraneous " somewhere, but
was unable to find one.

grep
 
What line of code gives you these 2 errors?

What version of Access is this?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

grep said:
Allen,

Thanks for your continuing help. Here's what I've done/got:

I had not turned off the AutoCorrect "features", but have now done so.
I'll check out your descriptions later. Also, I compacted the database.

The Immediate Window, as per your suggestions, did come through with a
ControlType of 112, however, the latter attempts turned up one of two
errors, and I'm not sure what either mean in this context:

1. No current record
2. #3011 - The Microsoft Jet database engine could not find the object ".
Make sure the object exists and that you spell its name and path name
correctly.

Now, I'm not sure what would cause the former error at all. I don't get
that error when I try to reach other fields on the same subform.

As for the latter, I looked around for an extraneous " somewhere, but was
unable to find one.

grep

Allen said:
No. Set the Format to Currency, of course.

If it still doesn't figure, open the Immediate Window (Ctrl+G), and work
with it there until you get the reference right. In that context you
can't use Me, so try:

Forms("NameOfYourMainFormHere")![NameOfYourSubformControlHere].ControlType
That should give you 112

From there try going further:

Forms("NameOfYourMainFormHere")![NameOfYourSubformControlHere].Form![Subtotal]

You should be able to track down which part is the wrong name.

BTW, if you have not done so yet, make sure that you uncheck the boxes
under:
Tools | Options | General | Name AutoCorrect
The compact the database:
Tools | Database Utilitites | Compact
Explanation of the problems this "feature" can cause you:
http://members.iinet.net.au/~allenbrowne/bug-03.html
Click to expand...
Click to expand...
 
The code is: ?Forms("frmVendorQuotes")![VQuoteDetails].Form![Subtotal]
I'm running Access XP.

grep
 
If that line in the Immediate window gives you the error message:
The Microsoft Jet database engine could not find the object ".
with just a quote mark where the name of the object should be, then your
database is partially corrupt, and it was probably caused by Name
AutoCorrect.

To rebuild it:
1. Compact the database: Tools | Database Utilities | Compact.

2. Decompile a copy of the database by entering something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

3. Test. If the problem still exists, then recreate the database by
following the 6 steps from the first symptom in this article:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

grep said:
The code is: ?Forms("frmVendorQuotes")![VQuoteDetails].Form![Subtotal]
I'm running Access XP.

grep

Allen said:
What line of code gives you these 2 errors?

What version of Access is this?
Click to expand...
Click to expand...
 
Sounds serious. I tried what you suggested thusfar, and the message is
different now... still doesn't work, but now it just either says:

1. Error 3021: No current record
2. Error 0: Reserved Error

Mostly I get the 3021 error. I'm not sure what causes the switch. I'll
take a look at the corruption document, but why would it say no current
record?

grep
 
Error 0 usually means that you have a procedure with error handling, and you
forgot the Exit Sub bit, so it actually executes the error handler (i.e. the
error hanlder is executing, but there is no error).

No Current Record means you are trying to work with a recordset while no
record is current. That can happen if:
- there are no records;
- you are at BOF;
- you are at EOF;
- you have run a search or find that had no match;
and so on.
It can also be triggered by a bad index, but that is usually fixed by a
compact/repair.

In Access 2002 with Service Pack 3, it can also be triggered by code that
runs during/just after a record deletion.

In your case, I was guessing the cause was corruption because Access seemed
confused about what object it was talking about. If the error still occurs
after the corruption is solved, it could be one of the above.

You could also try a decompile:
Enter something like this at the command prompt while Access is not running.
It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
 
Allen,

Now I'm not going to try to explain this, but I looked back at a form I
did before (I'd forgotten about it), in which I'd tried to deal with the
same problem. Again, I did the actual calculation in the subform, and
then just used a reference to that field in my main form's field. Only
in that form, it worked.

The difference was as follows:

OldForm
-------
Subform.ExtPrice = [qty]*[price]
Subform.Subtotal = sum([qty]*[price])


NewForm
-------
SubForm.ExtPrice = [qty]*[unitprice]
SubForm.Subtotal = sum([ExtPrice])

As soon as I changed the new form's Subtotal field to =
sum([qty]*[unitprice]), it started working.

My thought is that ExtPrice is a calculated field. It's not really
stored anywhere, per se, so from record to record, it's got a different
temporary value. Qty and UnitPrice are stored fields - real fields - so
the sum() function has what to work with.

In fact, that would explain something else I thought was strange. In the
NW database model, the subform has an [ExtendedPrice] field, which has a
control source of ExtendedPrice. I'd wondered why they would do that,
considering that it's really something that can be calculated on the
fly. Now I think I understand it better.

Thanks for all your help.

grep
 
Back
Top