help!... urgent and i'm new on this area

[Kurosagi85]

i need help and its urgent...

i have created a table with fields: ItemNo, ItemName, IncomingStocks
and also DateReceived.


The IncomingStocks and DateReceived are volatile. it is expected to
change several times during the life of a record


I have tried the simple append query
but it keep on copying the whole records instead of the 'ONE' record
that i change..


What code do i need to use to have it append only the record the user
has
touched? I have created a append query, but this will append all the
records
from the main table to history table.


INSERT INTO ItemHistory ( ItemNo, ItemName, DateReceived,
IncomingStocks )
SELECT Item.ItemNo, Item.ItemName, Item.DateReceived,
Item.IncomingStocks
FROM Item;


but i need to store the exact same records..same field...for the
archive. for instance


DateReceived : 1-sep-2006
ItemNo : 1
ItemName : Sweets
IncomingStock : 50 boxes


and then the next day i bought another 10 boxes of sweets and i have to

fill in the form for 10


DateReceived : 2-sep-2006
ItemNo : 1
ItemName : Sweets
IncomingStock : 10


so in my archive i want it to be like this:


DateReceived ItemNo ItemName IncomingStock


1-sep-2006 1 Sweets 50


2-sep-2006 1 Sweets 10


and so on... however...when i'm using my append it become like this if
i have another item:


DateReceived ItemNo ItemName IncomingStock


1-sep-2006 1 Sweets 50


2-sep-2006 1 Sweets 10


1-sep-2006 1 Sweets 50


2-sep-2006 1 Sweets 10


3-sep-2006 2 Choco 15


Can sumone help me..i'm new in this area

and i dont want it to be using subform since it will become longer ...

 

[Guest]

It is not an append query you want. You need an update query.
An append query creates a new record in a table. An update query changes
the values of fields in an existing record.

 

[Guest]

You do need an append query if, as you say, you're writing to a history
table. An update query, as Klatuu is suggesting, will not help.

The problem is that your append query is selecting all previous records with
no criteria (where clause). I assume this is being run from a form and that
is where you should get the values you want to insert. The form should have
the IncomingStocks tables as its record source. When you click a button or
perform some action on the form, it will launch the append query. This query
will look something like:

INSERT INTO ItemHistory ( ItemNo, ItemName, DateReceived, IncomingStocks )
SELECT Forms!MyForm!txtItemNo, Forms!MyForm!txtItemName,
Forms!MyForm!txtDateReceived, Forms!MyForm!txtIncomingStocks)

You need to insert a single record with values from your form rather than
with values from the table. The form brings in the values you want from the
IncomingStocks table.

Barry

 

[Guest]

You are correct, Barry, I don't know what I was thinking.