Opdatering af data i tabel

I de felter hvor du registrerer køb / salg, kan du indsætte flg. i felternes BeforeUpdate:

Me.Refresh

I assume you also have a tabel containing your products and stock levels?

In the forms where you make "købs- og salgsordrer"  you need to write some code which also updates the table which contains stock levels.

Jeg har også en tabel med hver reservedel på lager, som indeholder de forskellige info for hver del på lager, deriblandt lagerbeholdningen.

Jeg har fundet dette andetsteds, men har lidt problemer med, at få det skilt ad til hvad jeg skal bruge og hvordan:

http://allenbrowne.com/AppInventory.html

In the forms where you make "købs- og salgsordrer"  you need to write some code which also updates the table which contains stock levels.

Terry,

As I am quet new to Access, I have no idea what I need to write. I am trying to look at both the link above and also the code written for the Northwind example database. Neither is super helpful as I am not sure how much code I need.

Ronnie.

You could use  something like this and you need to alter name of table/field as this is justan example.
ValueTouWantToApplyToStockLevel also needs to be altered to the anem of the field on the form where you have entered the "købs- og salgsordrer" quantity. And dont forget to alter sign (+/-) accordingly.


DoCmd.RunSQL "UPDATE YouStockTable SET StockLevel = StockLevel + " &  ValueTouWantToApplyToStockLevel

Oops, and you obvioulsy need to include which "reservedel" you are altering stock level for.


DoCmd.RunSQL "UPDATE YouStockTable SET StockLevel = StockLevel + " &  ValueTouWantToApplyToStockLevel & " WHERE PartNumber = '" & SomePartNumber & "'"

assumeing PartNumber is a text value

Terry,

Where should I insert this code? On the button accepting the sales and purchase orders, respectively?

Ronnie

I would suggest in the After insert event for both køb and salg forms.
IE after a record is entered into the appropriate table you also update your stock table

I have now written the code as an AfterInsert event. It works fine - except that the line cannot read the ID from the sparepart table. It pops up and asks for the value and regardsless what I write in this field (just to test if it works) it will update the stock for all spareparts in my list. :S

My code for purchases is:
DoCmd.RunSQL "UPDATE tblLageroversigtKundelager SET [Antal på lager] = [Antal på lager] + [Antal] WHERE [Reservedels_ID] = [Produkt]"

Where:
tblLageroversigtKundelager is my spareparts list.
Antal på lager is the actual stock.
Antal is the amount to add.
Reservedels_ID is the Primary key for the spareparts table.
Produkt is a looked up field for products in the purchase table.

It will pop up and ask me for Reservedels-ID.

Any idea of what I got wrong?

Ronnie

Strangely the problem seems to have disappeared after I cut and pasted the the line.

I would like though that the amount is not withdrawn or added until I have sent and recieved the orders respectively. Could I obtain this by moving the code from AfterInsert to OnDirty?

"Strangely the problem seems to have disappeared after I cut and pasted the the line"
I dont see how the problem has gone if you havent made any alterations to the SQL.


DoCmd.RunSQL "UPDATE tblLageroversigtKundelager SET [Antal på lager] = [Antal på lager] + [Antal] WHERE [Reservedels_ID] = [Produkt]"

The reason you are being asked for Reservedels-ID is because you need to replace Reservedels-ID in th eSQL with the actual vale of Reservedels-ID. At the moment it is just text.



Something like this.

DoCmd.RunSQL "UPDATE tblLageroversigtKundelager SET [Antal på lager] = [Antal på lager] + " & [Antal] & " WHERE [Reservedels_ID] = '" & [Produkt] & "'"

Actually an idea would be to put the SQL in a string variable so that you can see what it contains before executing th eSQL


Dim sSQL  as string

sSQL = "UPDATE tblLageroversigtKundelager SET [Antal på lager] = [Antal på lager] + " & [Antal] & " WHERE [Reservedels_ID] = '" & [Produkt] & "'"

Then use
DoCmd.RunSQL sSQL

Terry,

I've made it work. :) Thank you VERY much for your help.

Please post an answer so I can give you some points.

great :o)

thanks