Add quantity of one on field2

[Majic]

Hello everyone,
I need your help

I have a subform that has a field for "Item Number" and a field for
"Quantity". I scan the item number to validate that number, however,
I have to add the quantity in. I would like to scan the item number
and displays 1 for quantity in the second field and then give me the
capability to scan again "item number" in the first field and add
another 1 in quantity field.
In other word, every time I scan the item it displays 1 for first
time, 2 for second time, 3 for third scan and so forth.

Any ideas

Thank you

 

[Jeff Boyce]

We aren't there. We can't see what you're doing.

What do you mean by "scan"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Collins]

I scan the item number to validate that number, however,
I have to add the quantity in. I would like to scan the item number
and displays 1 for quantity in the second field and then give me the
capability to scan again "item number" in the first field and add
another 1 in quantity field.
In other word, every time I scan the item it displays 1 for first
time, 2 for second time, 3 for third scan and so forth.

Any ideas

Ideally, you'd want control of flow logic e.g. if the row doesn't
exist then add a row with a tally of one otherwise increment the
existing tally by one.

Sadly, Jet only allows one SQL-language statement (INSERT, UPDATE,
DELETE) per SQL PROCEDURE (Access Query object defined by either a SQL-
language statement or a parameterized SELECT query), so the best I can
do (other than saying, "More capable SQL DBMSs are freely available"
<g>) is *two* SQL PROCEDUREs coded to avoid errors where rows do not
yet exist, both to be called consecutively and in order e.g. (ANSI-92
Query Mode syntax):

CREATE TABLE Items
(
item_number INTEGER NOT NULL UNIQUE,
tally INTEGER DEFAULT 0 NOT NULL,
CHECK (tally >= 0)
)
;
CREATE TABLE Sequence
(
seq INTEGER NOT NULL UNIQUE
)
;
INSERT INTO Sequence VALUES (1)
;
CREATE Procedure AddItem
(
arg_item_number INTEGER
)
AS
INSERT INTO Items (item_number, tally)
SELECT DISTINCT arg_item_number, 0 AS tally
FROM Sequence
WHERE NOT EXISTS (
SELECT *
FROM Items AS I1
WHERE I1.item_number = arg_item_number)
;
CREATE Procedure IncrementItemTally
(
arg_item_number INTEGER
)
AS
UPDATE Items
SET tally = tally + 1
WHERE item_number = arg_item_number
;

Usage:

EXECUTE AddItem 55;
EXECUTE IncrementItem 55

Note Sequence is a permanent auxiliary table known to contain at least
one row; any such table could be used here (but not the Item table
itself because you would be unable to add the first row <g>!)

Jamie.

--