By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,558 Members | 1,533 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,558 IT Pros & Developers. It's quick & easy.

total a continuous form

100+
P: 158
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub UpdateInvoice()
  2.  
  3.   Dim rsItemMaster As ADODB.Recordset
  4.   Dim rsInvoice As ADODB.Recordset
  5.  
  6.   Set rsItemMaster = New ADODB.Recordset
  7.   Set rsInvoice = New ADODB.Recordset
  8.  
  9.   On Error GoTo ExitFunction:
  10.  
  11.   rsItemMaster.Open "tblItemMaster", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
  12.   rsItemMaster.MoveFirst
  13.   rsItemMaster.Find "Item='" & Me.cboPartNum & "'"
  14.  
  15.   Me.txtDescription.Value = rsItemMaster.Fields("Description").Value
  16.   Me.txtItemCost.Value = rsItemMaster.Fields("Cost").Value
  17.  
  18.   Me.txtTotalCost = Me.txtQty * Me.txtItemCost
  19.  
  20.   rsItemMaster.Close
  21.   Set rsItemMaster = Nothing
  22.  
  23.   rsInvoice.Open "tblInvoice", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
  24.  
  25.   'i think this is where the problem is  
  26.   rsInvoice.Filter = "[InvoiceNum] = " & Me.txtInvoiceNum
  27.  
  28.   rsInvoice.MoveFirst
  29.   Me.txtInvoiceTotal = 0
  30.  
  31.   Do While (rsInvoice.EOF = False)
  32.     Me.txtInvoiceTotal = Me.txtInvoiceTotal + rsInvoice.Fields("TotalCost").Value
  33.     rsInvoice.MoveNext
  34.     Me.Refresh
  35.   Loop
  36.  
  37.   If rsInvoice.EOF = True Then GoTo ExitFunction
  38.  
  39.   rsInvoice.Close
  40.   Set rsInvoice = Nothing
  41.  
  42.   Me.Refresh
  43.   Me.txtInvoiceNum = invoiceNumber
  44. ExitFunction:
  45.   'do nothing
  46.  
  47. End Sub

Right now it runs without errors, but not properly
Thanks in advance!
Sep 9 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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.
Sep 9 '08 #2

100+
P: 158
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
Sep 9 '08 #3

100+
P: 158
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
Sep 9 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.