Inventory problems

[Dandy55]

Here is my situation:

I have a database used for workorder tracking for a small business. I have a form called workorders linked to a table called workorders, a subform called workordersubform linked to a table called workorderdetails, and a form called inventory linked to a table called inventory. The workorder form has all of the details for the workorder like client, make, model, etc. The workordersubform has all the parts, quantity, price information for each workorder
What I would like to have happen, but am having great trouble with, is when the status field of the workorder is set to closed, all of the items in the workordersubform are subtracted from the inventory table. Until the workorder is closed, no items are subtracted from inventory. With 3 different tables and 3 forms, I am having trouble making everything work. I am using Access 2000. Has anyone done something similar to this or have any suggestions?

 

[sheronne]

It will take a fair amount of code. Create sometype of trigger that goes off to close the workorder. When this happens have it then subtract the items for the said workorder from inventory. The trigger could be as simple as a command button that closes a work order.

I have done this before, but I didn't do it in Access. If you want more detail let me know. My trigger was a little more complex. We the order was paid in full it subtracted items from inventory. If the order was not paid in full it marked the items ("Reserved&quot in inventory.

Thanks

R

sheronne@hotmail.com

 

[Dandy55]

I have a trigger set up in the afterupdate of the status field on the workorder form. If the value = "closed", then I would like all of the items in the workordersubform subtracted from inventory. I am not very good with writing code, so I am not sure how to do this properly, being that I have to refer to other tables and forms in the code. Any help you could offer would be appreciated.

 

[haj1503]

Hi!,

I not saying here i can to help you properly but if my solution here good enough for you to solve you problem.

My suggestion is like this:

You try make table "tblStatus"
StatusID = AutoNumber
Status = Text.
enter this table with what like to key in.

You try add you table in "frmWorkorderdetail" with field "Status" with "Number" and then your try make query at your form "frmworkorderdetails" where field status you try change from text to combobox and you try make query link to table "tblstatus". if you need make query that field status with text so in "criteria" you try write like this "*Updated*"
so when select from combo box "updated" so next time when you try open the form i believe only list of part not update will appear. I hope you can understand with my suggestion but is better if you can send your own sample so easy to me either modified you sample or you try to develop yourself.

Haj1503
hajlin@hotmail.com