VBA, Running sums on a FORM??

Running sums on a FORM??

Author	Message
JamesS #1 / 6	Running sums on a FORM?? Got the running sum on the report..its great for doing a checkbook - balances. Now, i want to show the same data on a form. How can this be done? thanks James
Mon, 26 Jan 2004 08:36:24 GMT

Allen Brown #2 / 6	Running sums on a FORM?? On a report, the data is fixed, so RunningSum is easy. Your form could be filtered or reordered at any time. Should Access show only the running sum for the filtered records? Should the balance be reworked if the records are reordered? The checkbook balance is usually a matter of using a calculated control that has a DSum() expression. Assuming: - primary key named "ID", and - Amount field that's positive for credits and negative for debits, the ControlSource of your text box will be something like this: = DSum("Amount", "YourTable", "ID <= " & [ID]) Quote: > Got the running sum on the report..its great for doing a checkbook - > balances. > Now, i want to show the same data on a form. How can this be done? -- Perth, Western Australia Tips for MS Access users at: http://odyssey.apana.org.au/~abrowne
Mon, 26 Jan 2004 10:37:37 GMT

Simon Li #3 / 6	Running sums on a FORM?? Hi James, You may refer to the following articles documented in Microsoft Knowledge Base: ACC2000: How to Use DSum to Create a Running Sum on a Form [acc2000kb] ID: Q210495 http://support.microsoft.com/support/kb/articles/q210/4/95.asp ACC2000: Sample Function to Create a Running Sum on a Form [accvba2000] ID: Q210338 http://support.microsoft.com/support/kb/articles/q210/3/38.asp Hope that helps! Simon Liao
Mon, 26 Jan 2004 11:20:23 GMT

JamesS #4 / 6	Running sums on a FORM?? thanks to you both. Quote: >>Should Access show only the running sum for the filtered records? Should the balance be reworked if the records are reordered? yes to both of these. if i use ID <= " & [ID]) and the order is by date, that will throw off the running sum. same if sorted by payee. maybe i should just not allow that? James Quote: > Hi James, > You may refer to the following articles documented in Microsoft Knowledge > Base: > ACC2000: How to Use DSum to Create a Running Sum on a Form [acc2000kb] > ID: Q210495 > http://support.microsoft.com/support/kb/articles/q210/4/95.asp > ACC2000: Sample Function to Create a Running Sum on a Form [accvba2000] > ID: Q210338 > http://support.microsoft.com/support/kb/articles/q210/3/38.asp > Hope that helps! > Simon Liao
Mon, 26 Jan 2004 23:24:51 GMT

Keith Sor #5 / 6	Running sums on a FORM?? All the posts make one undiscussed assumption, i.e. that you have an ID that is sequential from the top of your list to the current record when using the DSum function. What about the situation where you have a set of transactions (each of which DO have a unique key) but there is no guarantee that the ID's are in date sequence? I am doing a checkbook program, but cannot guarantee that the ID's will necessarily sort lowest to largest by date. This could happen if a person entered the transactions out of date sequence. E.g. AutoNo Date Amount 1 1/1/01 +46.45 2 1/4/01 -35.23 3 1/3/01 +245.33 etc. Keith Quote: > On a report, the data is fixed, so RunningSum is easy. > Your form could be filtered or reordered at any time. Should Access > show only the running sum for the filtered records? Should the > balance be reworked if the records are reordered? > The checkbook balance is usually a matter of using a calculated > control that has a DSum() expression. Assuming: > - primary key named "ID", and > - Amount field that's positive for credits and negative for debits, > the ControlSource of your text box will be something like this: > = DSum("Amount", "YourTable", "ID <= " & [ID]) > > Got the running sum on the report..its great for doing a checkbook - > > balances. > > Now, i want to show the same data on a form. How can this be done?
Thu, 12 Feb 2004 08:29:58 GMT

Bob Barrow #6 / 6	Running sums on a FORM?? You would need to sort by date in the DSum. Howver, if you're not storing the time as well as the date (which seems likely) this method is more prone to error since many records will have the same date entry.You could sort by date AND ID, like this: = DSum("Amount", "YourTable", "Date* <= #" & txtDate** & "# AND ID <= " & [ID]) but this may also give unexpected results if you care about the order within a certain date. However, it's really the only option, so you should pobably go for it. I hope this isn't really the name of your field. "Date" is a reserved word and should be avoided when considering field (or other object) names. Look up reserved wordsin online help. *Your form control names should be different from the fields in the bound recordset. I usually use a prefix designating the type of control I am naming: "txt" for textbox, "cmb" for combo, etc. HTH, Bob Barrows Quote: > All the posts make one undiscussed assumption, i.e. that you have an > ID that is sequential from the top of your list to the current record > when using the DSum function. > What about the situation where you have a set of transactions (each of > which DO have a unique key) but there is no guarantee that the ID's > are in date sequence? > I am doing a checkbook program, but cannot guarantee that the ID's > will necessarily sort lowest to largest by date. This could happen if > a person entered the transactions out of date sequence. > E.g. > AutoNo Date Amount > 1 1/1/01 +46.45 > 2 1/4/01 -35.23 > 3 1/3/01 +245.33 > etc. > Keith - Hide quoted text - - Show quoted text - Quote: > > On a report, the data is fixed, so RunningSum is easy. > > Your form could be filtered or reordered at any time. Should Access > > show only the running sum for the filtered records? Should the > > balance be reworked if the records are reordered? > > The checkbook balance is usually a matter of using a calculated > > control that has a DSum() expression. Assuming: > > - primary key named "ID", and > > - Amount field that's positive for credits and negative for debits, > > the ControlSource of your text box will be something like this: > > = DSum("Amount", "YourTable", "ID <= " & [ID]) > > > Got the running sum on the report..its great for doing a checkbook - > > > balances. > > > Now, i want to show the same data on a form. How can this be done?
Thu, 12 Feb 2004 21:45:00 GMT

Page 1 of 1

[ 6 post ]