Subform Total not working when list is longer then window

[Ktx7ca]

HI Every One

I'm having an odd ocurance with a subform

On the main form I have a field that you enter in an item number. The item discription and cost are added to the subform table and is requeryed so the new item is displayed .

In the subform footer I have a field doing a sum on the cost field and I display that total on the main form.

All this works Great untill I get to bottom of the subform window with items. as soon I pass the end of the window the totals stop working. I can still enter more items with no problems, scroll up and down to see all the items, Just the total is now 0

if I delete items untill total amount of items is not longer the the subform window the total starts working again

It makes no differance as to the length of the subform window big or small.

Subform:
set to continious form
vertical srcoll bar only
Sum of field in subform footer

any Idea greatly apreceiated

 

[dhookom]

Can you share your text box control sources, code, and anything else that is significant?

Duane
Hook'D on Access
MS Access MVP

 

[Ktx7ca]

on the subform footer I have a text box called Plsubtotal
in the control source for that text box I have =sum([price])

onthe main form I have A text box ItemNum in it;s afterupdate event

I add a new record that has a discription and a price.
requery the subform
and update the subtoal box on the main form with
Me.Subtotal = Forms![SF-main![Sbfwindow]![Plsubtotal]

Every thing works great untill the number of lines displayed on the sreen reaches the bottom of of the subform window at which time my subtotal textbox (on the main form) shows 0. You can still add more items to the subform and they display just fine, but the subtotal stays at 0.

if you delete lines(items) untill they are no longer longer then the subform window the subtoal comes back and is correct.

 

[dhookom]

I'm not sure why you have any code. Your expression for Me.Subtotal is clearly missing a ]. You should be able to simply have a control source of a text box on your main form like:

=[Name of Control Hosting Subform].Form.[Name of control on subform]

This text box should automatically update following the Update of records contained in the subform.


Duane
Hook'D on Access
MS Access MVP

 

[Ktx7ca]

my apologies I must have deleted the bracket when I posted the code as it's in my code

your solution does work ,but I still need to access subform fields for for some VBA calculations and once I get past the bottomof the display window I can no longer access the fields,they report as 0

 

[dhookom]

What "VBA calculations"? I previously asked for "code" but you didn't provide any.

Duane
Hook'D on Access
MS Access MVP

 

[Ktx7ca]

I just added this so all I had to send before was what I sent

I added second Sum textbox to my subform footer PLTax1Total

Public Function CalcInvoice()

If Forms![SF-Main]![Tax1Check] = True Then
Forms![SF-Main]![Tax1Calc] = Forms![SF-Main]![sbfwindow]![PLTax1Total] * (Forms![Setup]![Tax1Rate] / 100)
Else
Forms![SF-Main]![Tax1Calc] = 0
End If

End Function

 

[dhookom]

I'm not sure why you would reference a value in a separate form. I would pull the tax1rate from a table, query, or the current form.

Where is CalcInvoice() and how is it called?

Duane
Hook'D on Access
MS Access MVP

 

[Ktx7ca]

Hi I'm calling it form the afterupdate event in the itemnum text box on the main form

I will be adding other calcuations and code as I go to the calcinvoice function

The data I need is already in the open subform so it seemed more effiect to get it from there then have access look it up again.

if there is a proper or better way I'm happy to learn

Thank for you help

 

[dhookom]

Since the data seems to be coming from multiple sources, I would probably pull it from tables.

Can you tell us the name of "the main form"?

Have you tried using any debugging in your code?

Duane
Hook'D on Access
MS Access MVP

 

[Ktx7ca]

The data is all coming from the table "purchaseList"

The name of the main form is "SF-Main"

When I run debug the correct value shows untill I fill the subformwindow up once they scroll past the bottom of the screen the debug value turns to 0

 

[dhookom]

I would try this code.

Public Function CalcInvoice()

  If Me.[Tax1Check] = True Then
     Me.[Tax1Calc] = Me.[sbfwindow].[Form]![PLTax1Total] * (Forms![Setup]![Tax1Rate] / 100)
  Else
   Me.[Tax1Calc] = 0
  End If

End Function

Duane
Hook'D on Access
MS Access MVP