Form footer total not working for all agents from Combo box filter

[murphy123]

Hello,

I am trying to fix a problem on a database that I didn't create. I need help with some code on a combo box filter. I have form "frmEstimatedPayment". On this form a have an Unbound combo box that filters the "Agents" to give me a total of estimated payments by each quarter. I have 5 text boxes running a sum on the same form for(Quarters: 1,2,3,4 and YTD) for each agent.

When the form is loaded/opened it shows all agents and the textboxes show an #Error message. However, if I select one single agent each textbox gives me the correct payment amount per quarter.

Here is some of the current code:

In the Unbound combo box:
Private Sub ComboAgent_AfterUpdate()
Me.Filter = "(([Bonus Payout by Month].[Name]='" & Me.ComboAgent & "'))"
Me.FilterOn = True
Me.Recalc
End Sub

Also in the unbound combo box click envent:

Private Sub ComboAgent_Click()
Me.FilterOn = True
End Sub

Here is the code in the 5 Textboxes:

=Sum([Bonus Payout by Month]![Q107])

=Sum([Bonus Payout by Month]![Q207])

=Sum([Bonus Payout by Month]![Q307])

=Sum([Bonus Payout by Month]![Q407])

=Sum([Bonus Payout by Month]![EE Bonus])

How can I make the textboxes totals sum all agents when there is no specific agent name selected?

Do I need to place some code on the "frmEstimatedPayment" form? Can someone help me?


Thanks.

 

[GhostWolf]

Maybe something like Me.FilterOn = False in the Form_Load event?

 

[murphy123]

I tried that and I still get the same error message in the textboxes if there is no agent name selected from the combo box. Any other suggestions?

Thanks.

 

[murphy123]

Sorry, I forgot to give you the code I tried:

Private Sub Form_Load()
Me.FilterOn = False
End Sub

I also tried...

Private Sub Form_Load()
Me.FilterOn = False
Me.ComboAgent= " "
End Sub

 

[GhostWolf]

Surely there's a way to make it work the way you'd like, but I don't know it. One of the resident gurus will probably pop in and tell us.

I've tried it, and gotten the same results as you. Doggoneit!! Even tried some of the other properties, like .DefaultValue, and didn't have any luck.

The quickest way for me to do it was to put the following code in my form's Load event:

Private Sub Form_Load()
Dim dbRec As Database, rsRec As Recordset
  Set dbRec = CurrentDb()
  Set rsRec = dbRec.OpenRecordset("select sum(Qty) as totQty from wrkGeneral")
  txtQuantity = Format(rsRec!totQty,"#,0")
  rsRec.Close
  set dbRec = Nothing
End Sub

If you wanted to do it that way, you could "select sum(Q107) as Q1, sum(Q207) as Q2" etc, then populate your textboxes from the recordset.