total a continuous form

[Jollywg]

I have a continuous form with 5 columns (Qty, PartNum, Desc, ItemCost, TotalCos). At the bottom of the form i have a field labeled Invoice Total. Right now the Invoice Total is totaling everything in my tblInvoice. I need it to only total what rows that begin with the same InvoiceNumber. Here is what i have so far.

Private Sub UpdateInvoice()
 
  Dim rsItemMaster As ADODB.Recordset
  Dim rsInvoice As ADODB.Recordset
 
  Set rsItemMaster = New ADODB.Recordset
  Set rsInvoice = New ADODB.Recordset
 
  On Error GoTo ExitFunction:
 
  rsItemMaster.Open "tblItemMaster", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
  rsItemMaster.MoveFirst
  rsItemMaster.Find "Item='" & Me.cboPartNum & "'"
 
  Me.txtDescription.Value = rsItemMaster.Fields("Description").Value
  Me.txtItemCost.Value = rsItemMaster.Fields("Cost").Value
 
  Me.txtTotalCost = Me.txtQty * Me.txtItemCost
 
  rsItemMaster.Close
  Set rsItemMaster = Nothing
 
  rsInvoice.Open "tblInvoice", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
 
  'i think this is where the problem is  
  rsInvoice.Filter = "[InvoiceNum] = " & Me.txtInvoiceNum
 
  rsInvoice.MoveFirst
  Me.txtInvoiceTotal = 0
 
  Do While (rsInvoice.EOF = False)
    Me.txtInvoiceTotal = Me.txtInvoiceTotal + rsInvoice.Fields("TotalCost").Value
    rsInvoice.MoveNext
    Me.Refresh
  Loop
 
  If rsInvoice.EOF = True Then GoTo ExitFunction
 
  rsInvoice.Close
  Set rsInvoice = Nothing
 
  Me.Refresh
  Me.txtInvoiceNum = invoiceNumber
ExitFunction:
  'do nothing
 
End Sub

Right now it runs without errors, but not properly
Thanks in advance!

[Stewart Ross]

Oh dear Jollywg. VBA can be used to make things very unclear indeed, and this really is!

Why use this approach when you can simply place an unbound textbox control in your form's header or footer and set the control source of that textbox to

=Sum([TotalCost])

which will take account of any active filtering too?

In terms of your code you are defining a filter but not applying it. There are many other problems: use of me.refresh in the loop (completely unnecessary), not closing your recordset - I could go on. I'd review what you are trying to achieve and start again - doing away with code as far as possible by using much simpler functions such as Sum as mentioned.

-Stewart.

[Jollywg]

Oh dear Jollywg. VBA can be used to make things very unclear indeed, and this really is!

Why use this approach when you can simply place an unbound textbox control in your form's header or footer and set the control source of that textbox to

=Sum([TotalCost])

which will take account of any active filtering too?

In terms of your code you are defining a filter but not applying it. There are many other problems: use of me.refresh in the loop (completely unnecessary), not closing your recordset - I could go on. I'd review what you are trying to achieve and start again - doing away with code as far as possible by using much simpler functions such as Sum as mentioned.

-Stewart.

if you only knew the half of it! haha. Although in my defense I'm writing code take chunks out putting chunks in and i'm fixing to go back and review it all and clean it up. This is an upgrade to a "database" that we currently have.

Thanks for all your help
matt

[Jollywg]

Ok Stewart one more for ya,
whenever i create a new customer and click create invoice it comes up with the first record in the table and changes it's invoice number to the new customer's invoice. Clearly the form should be blank...how do i go about fixing this?

Thanks