Delete item in orders but update in stock table

[FurryGorilla]

Hi all,

Hopefully you can help. I have a form for a customer order which includes a subform (in datasheet view) which displays the items and their respective quantities that are on the current order. The user is able to delete items from the order by highlighting the record in the subform and deleting it as normal.

If the item on that order was the last in stock then the status of the stock item would be 'out of stock', however if they were deleting the item from the order then there would be items available to sell, i.e. the status would change to 'in stock'.

The only thing is, I do not know where to place the query which would change the status of this. I have tried it by putting it in the Form_Delete() section but this results in a Jet Database error message as I am updating the record currently open.

Is there a way to pass the StockID from the subform to a query once the stock item has been deleted from the order? Any help would be much appreciated.

Thanks in advance
Chris ____________________________
Have you seen my munkee?

 

[FoxProProgrammer]

Hi Chris,

You might try putting your code in the On Current event of the subform. Based on your description, it sounds like that would work. If it doesn't, post back.

Best, dz
dzaccess@yahoo.com

 

[FurryGorilla]

Hi dz

Thanks for the reply although I'm still having trouble. Maybe I've not understood what you've said but this is what I've done.

In the Sub Form_Delete I've turned off the document warnings and then prompted the user to check they are happy with deleting the record. If they are happy a query is run to check the status of the item that is being removed. If it is 'Out Of Stock' this asks the user if they would like to change the status of this to 'In Stock'. Now I found I couldn't run the query here because of the Jet database error so instead store the StockID from the form (txtStockID) in a global variable called intStockID (originally set to 0). Now once this is deleted the variable is the only thing that identifies the record that has been deleted. If the intStockID is greater then 0 then it means that this item of stock should have it's status changed. The only thing is that I have no idea where to put this. If there are no other records in the sunform then the On Current event is not ppresent so the query can not be run from there. Any ideas?

The code i have for changing the status is as follows but I'm not sure whether this is correct either:

If intStockID > 0 Then
 DoCmd.RunSQL "UPDATE tblStock SET tblStock.Status = 1 " & _
       "WHERE tblStock.StockID = " & intStockID
End If[code]

Apologies for the formatting but hopefully you follow.

Ta
Chris
 ____________________________
Have you seen my munkee? [monkey]

 

[FurryGorilla]

No probs, done it now. Just popped the code in the After Delete Confirm section of the subform and everyhings fine.

Thanks for the help dz. ____________________________
Have you seen my munkee?