Showing a field related in a subform

[tigreeuropeo]

I have a form to capture product orders, which has a subform in it. Each line has: product number, quantity, price per unit, and the total of the line.

It is like this:

Order Number: _________ Date: ________
Client : _________

Line Prod.Number Quantity Price Total
-----------------------------------------------
1 111111 2 20.50 41.00
2 222222 3 15.00 45.00
3 333333 1 3.00 3.00
...
...
I have to related tables (orders and lines) to store the orders. On the other hand, I have another table to store the products (number, description, quantity in stock).
The lines are contained in a subform, and I want to put the description, that's the first problem.

The second problem, is that I put one button to delete the selected line. This is located at the end of the subform, in the footer. The code associated to this command button was generated with the wizard, and it works ok. I placed the code to decrement the quantity in stock in the Click event.
But if I select more than one record in the subform (lines of the order), and press the button located at the menu bar (the one that comes with Access), it deletes all the selected records without executing my code. I want to know where and how can I catch this event for each line.
I need to write code for each deleted line in the subform, and I don't know how to do it.
Can you help me, please?.
Thanks in advance.

 

[UncleJack]

What I do in these situations is to have 3 tables:

Products
Orders
OrderItems

Products holds the description, price, availablity, etc.
Orders is a group of items for a particular customer at one time.
OrderItems has a list of the products on a particular order.

Products is keyed by ProductID
Orders by OrderID
OrderItems by ItemID

OrderItems holds the ProductID and OrderID
Orders hold the CustomerID, date, etc.

You can display OrderItems in a subform on an OrderForm. Base the subform on a query that contains the Products table so you can show the description.

As for deleting multiple records with a button click, I don't think you can do this without a lot or bother. Just be content to delete one line item at a time.

To delete a single record just write a delete query containing the ID number of the item you want to delete. On the criteria line of the delete query put the form address of that item on your subform. Use the following syntax:

Forms!MainFormName!SubformName.Form!SubformField

The ".Form" indicator is a literal in the expression and doesn't stand for anything. It just tells Access that you're referring to a field on the subform. You can use the SQL version of the query in an On Click event procedure of your delete button and put any other code that you want to run in the same place.

Uncle Jack