Sum a column every time a record is created

[Guest]

I want to sum the Quantity field each time I create a record. I am defaulting
a 1 in the Quantity field and I have created a sum in the footer section. The
sum works whenever I manually enter a value but it does not work if I use the
default value of 1. In other words, if I simply press enter and accept the
default 1 for quantity, the sum does not update when I commit the entire
record. If I manually enter a value of 1 for Quantity it does update. How can
I force the sum to update each time I create a record even if I do not
manually enter a value?

 

[Arvin Meyer [MVP]]

Try forcing the subform to recalculate in the AfterUpdate event.

Sub Form_AfterUpdate()
Me.Recalc
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[onedaywhen]

I want to sum the Quantity field each time I create a record. I am defaulting
a 1 in the Quantity field

The DEFAULT must be a scalar (i.e. a single value) and either a literal
value (e.g. not a calculation) or a niladic function (e.g. DATE()).

Consider this table

CREATE TABLE Test (
amount INTEGER DEFAULT 0 NOT NULL,
CHECK (amount >= 0),
total_amount_when_added INTEGER DEFAULT 0 NOT NULL,
CHECK (total_amount_when_added >= 0)
);

To determine the value for the total_amount_when_added column on
insert, you could create a SQL procedure (parameterized query):

CREATE PROCEDURE TestProc (
new_amount INTEGER = 1
) AS
INSERT INTO Test (amount, total_amount_when_added)
SELECT new_amount AS amount,
IIF(SUM(amount) IS NULL, 0, SUM(amount))
+ IIF(new_amount IS NULL, 1, new_amount)
AS total_amount_when_added
FROM Test;

You would then remove INSERT permissions from the base table and grant
them to the procedure. This way, you would ensure that all inserts to
the table go via your procedure i.e. the value in the
total_amount_when_added column is under the control of the database
designer, not the front end code.

Jamie.

--