VBA code to change the value of a field

[jesswoo]

Hi all,

I have a table called tblPackingMaterials and a table called tblMaterialQuantities

TblPackingMaterial has a number field called StartingQTY

tblMaterialQuantities has:
IDMaterial (a one to one relationship with MaterialID in tblPackingMaterial)
AdjustmentQTY
QTYinShop

From these 2 tables I have a select query with:
IDMaterial.tblMaterialQuantities
StartingQTY.tblPackingMaterial
AdjustmentQTY.tblMaterialQuantities
QTYinShop.tblMaterialQuantities (this field is calulated in the query using the expression QTYinShop: [StartingQTY]-[AdjustmentQTY]

The expression works perfectly only problem is I cannot seem to figure out how to update the StartingQTY with the QTYinShop after an adjustment is made so whenever I make an adjustment it is from the origonal StartingQTY.

I created a form from this and created an on click event in the StartingQTY field to update StartingQTY with QTYinShop when I click in the StartingQTY box. This works perfectly as well and I was able to deplete inventory levels.

I tried doing after update events in both (not at the same time) AdjustmentQTY and QTYinShop to update StartingQTY with QTYinshop but they do not work like the on click event in StartingQTY.

Is there a better code I can write to update StartingQTY with the QTYinShop after I make adjustment without having to click in the StartingQTY box? I do not want StartingQTY to be visible in the adjusting form because I dont want any numbers to get put in there.

Please help!! All answers are greatly appreciated!

 

[jdraw]

From these 2 tables I have a select query with:
IDMaterial.tblMaterialQuantities
StartingQTY.tblPackingMaterial
AdjustmentQTY.tblMaterialQuantities
QTYinShop.tblMaterialQuantities (this field is calulated in the query using the expression QTYinShop: [StartingQTY]-[AdjustmentQTY]

are these actual statements from your work? The table and field name order is backwards. TableA.field1, tableA.field2 etc

People will advise against storing a calculated value in a Table.

You would normally use a query to determine AmountLeft =StartingAmount - AmountUsed

 

[jesswoo]

I know storing a calculated value is not advised but I need to know what we have in stock at any given time.

I am sure I typed more than just those statements backwards I haven't had any real training in Access just a lot of google and youtube help to figure things out.

For the last part of your answer, that IS what the query does. QTYinShop=Amount left, StartingQTY=startingamount, and AdjustmentQTY=amount used. That works fine and calculates what I want, but I have not been able to depreciate the startingqty so that when I make an adjustment it is not to the original qty entered when material is recieved but to the qty after all other adjustments.

 

[MarkK]

Time is a critical dimension of your data. How much do you have on hand when?

Commonly a system concerned with moving stuff in and out keeps a dated record of those movements. Mostly these are called orders and purchase orders, which contain detail records of a quantity and a reference to a product or object. In the header of the order is the source of the stuff, and the destination of the stuff.

In respect to managing stock then, the amount you have on hand is always a function of summing how much you received, and subtracting how much you shipped, and when.

 

[jesswoo]

I need to know whats on hand whenever I decide to check it. In the morning, in the afternoon whenever. Thing is we are a pretty small company and we get jobs in at various times. There are orders that get put in and the parts for orders do have specific materials that go with it but sometimes in the shop they will use a different bag if the one for the part is not there or they just don't look. (there is seriously nothing I can do about this, I have tried and tried and nothing gives). That is the reason I am doing adjustments seperate.

We get POs in at all hours of the day I could have 5 when I walk in in the morning and throughout the day several more will come in... My boss also quotes jobs whenever he gets them at varius times of the day and looks to see if we need to order bags for the job.

This is why I need it on hand at any time. If this is not the right way to do it then please tell me the right way because I have no idea.

I HAVE a working query that sums the ins and outs but it does it all seperately and off of the base amount in the startingQTY. I do not know how to get the starting qty to change. Do I just need to go into the table and change it manually every time I deduct something? If thats the only way then ok but say so but I already said I have a working query that does what you both are telling me.

 

[jdraw]

What lagbolt and I are saying is you do the calculation when you need it by means of a query. You do not (normally) store a calculated value in a table.

Please review this for more info. http://allenbrowne.com/appinventory.html