Calculate a Total when Filtering a SubForm

[Justin.ITPro]

I am trying to calculate the total [QtyProd] of a filtered subform and have the total update on the main form. I can get the total to calculate for the whole table but when I filter the table it doesn't update. I am probably missing something simple... let me know if you need any additional information.

I am filtering the subform like this and it is based off a query:

Private Sub btnSKUQuery_Click()
    Me.subForm.Form.Filter = "SKU = [txtSKU]"
    Me.subForm.Form.FilterOn = True
    Me.subForm.Form.Requery
End Sub

The code inside my QtyTotal box is

=Sum([QtyProd])

 

[bob fitz]

I think your code should be:

Private Sub btnSKUQuery_Click()
    Me.subForm.Form.Filter = "SKU = '" & Me.txtSKU & "'"
    Me.subForm.Form.FilterOn = True
    Me.subForm.Form.Requery
End Sub

 

[Justin.ITPro]

Actually both work just as well... not sure which is best practice though. I also need to know how to make the filtered information total in the subform and update a field on the main form with the filtered total.

 

[bob fitz]

Once the filter has been applied, the total in the sub form will be recalculated.
The control on the main form can be updated with the following line of code:
Parent.YourControlName.Requery

 

[Justin.ITPro]

I have realized that my control was updating from a static query rather than the sub-form or filtered results. It won't let me attach my database but msg me and I can email it to you or share via Google Drive. Can you look at frmStorageSub and let me know how I can get the filtered results in subForm based on the Unbound txt at the top to total in the ctrQty control at the bottom.

Thanks for any help... I am sleep deprived and very lost.:banghead: