refresh or requery form from subform change

[AccessIdiot]

This is driving me nuts.

I have an unbound textbox (txt_SumSize) in my subform that holds the total of a field (Debitage_Size_Quantity)seen in datasheet view (control source =Sum([Debitage_Size_Quantity]).

I have an unbound textbox on my form (txt_SizeTotal) that gets the value from the subform footer when the form loads:

Private Sub Form_Load()
    Me.txt_SizeTotal = Me!sbfrm_DEBITAGE_Size.Form![txt_SumSize]
End Sub
 

This works great. But I need the total to refresh or requery when more data is added to the subform.

Nothing I have tried works. Either nothing happens or I get an error. I have tried putting code on the form, on the subform, on the subform footer textbox after update, on change and also on the main form total textbox after update and on change and nothing works.

I am not committed to using the on load function but it was the only code that worked in trying to get the total from the footer to show up on the main form.

Any help appreciated!

[AccessIdiot]

Help? This is the only thing hanging me up (for the moment) and nothing I've searched for works!

[Stewart Ross]

Hi. I think that using multiple indirect references to unbound controls on forms is a very complicated approach, as you have to keep requerying all the controls to keep them up to date. You don't tell us what the calculation is, or whether it is a sum of values that span more than one row in the subform (that is, a group total).

I would suggest that you either include a calculated field in the underlying query on which your subform is based (the best and simplest solution if the total is a row total) or continue to use an unbound textbox on the main form, but instead of indirectly referring to another textbox use just the one unbound control with a DSUM or whatever domain aggregate function is suitable for your purposes to calculate the total directly. With the unbound textbox you would need to trigger a requery from the after update and after insert events of the subform (at the very least). You might have to requery from the On Current event too - I can't be sure as you haven't told us how the total relates to the records in your main form/subform setup. If you don't use On Current then you would still have to set the initial value of the unbound textbox from the main form's On Load event as at present.

-Stewart

[AccessIdiot]

I'm sorry, I should have been more detailed. Also, I am totally open to completely redoing how this is done.

I have a form and a subform. The subform is a datasheet view with just two fields: size (a combobox, called Debitage_Size) and quantity (Debitage_Size_Quantity). Then there is the unbound text in the footer that sums the quantity (txt_SumSize). I don't have much experience with unbound fields doing a sum in the footer of a form so I assume it is updating as records are added?

Then there is the unbound field on the form (txt_SizeTotal) that is supposed to grab the total from txt_SumSize and display it.

The code I supplied in my first post is all I have and again, I am not bound to it. It's just the only thing I have tried that actually worked until I hit the wall I'm at now.

Any other suggestions are welcome!

[Stewart Ross]

OK. As there are only two fields on your subform, is it safe to say it is not linked to the main form in a parent/child (1..many) relationship? If this is the case then the unbound footer textbox is simply performing a running sum of the quantity for all records in the subform recordset.

Forms in datasheet view do not have visible headers and footers, so any controls you place on these areas will work as normal but cannot be seen by the users.

In a test subform I placed an unbound textbox in the footer called txt_SumSize (as in your example) and set its control source to:

=Sum([Debitage_Size_Quantity])

This is indeed updated automatically on each record add or change as you mentioned.

I placed a second unbound textbox on the main form. Instead of trying to update this via code, I simply set its control source property to refer to the field on the subform as shown below. I don't know the name of your main form, so in the example I have used [Debitage Test] for the main form and Debitage for the subform. You would have to replace these with the real names of your main form and subform.

=Forms![Debitage Test].Debitage.Form!txt_SumSize

This tracks the running total in the footer of the subform with no code whatsoever - no event procedures at all. See attached screenshot for an example of the way it operates.

Of course, if a group total is needed the simple Sum in the footer has to change to a DSum or similar as previously discussed.

-Stewart

[AccessIdiot]

That works beautifully, thank you. I had tried something like that before but I must have had the syntax wrong because I got the dreaded Error in the total box on the main form.

A question though - there are only two fields shown on the subform but the underlying table contains a few more, namely an autonumber PK and a FK that is the PK of the table that underlies the main form. And yes, the subform IS linked to the main form in a one to many. So does that affect or change things? Yes the unbound footer textbox should just be a running sum of the quantity in the subform.

Thanks again for your help!

[Stewart Ross]

Hi again AI. I am glad this approach is working out for you.

The parent/child relationship between the fields of the mainform and subform should take care of the grouping - in effect a WHERE clause is being applied to the subform's recordset automatically to limit its scope to only those records matching the linked parent fields. All being well you should not have to do any more to the unbound textbox in your main form, which should show the correct running total when you select a different grouping in your main form...

Cheers

Stewart

[AccessIdiot]

Yep, works great, thanks!