How do I run an update query on just one record from a form?

[Guest_imported]

Here is my problem.
I am making an inventory control database which is rather complicated in functionality. At one point I have a form and the form has a line for each Order to be received. Each line also has a command button(|||||) that says receive:

Date Order Qty
01/01/2002 X-phone 50 |||||
02/02/2002 Y-phone 25 |||||
ect...

I want to push the button and then have that product adjusted in two tables. Inventory table (onOrder goes down, onHand goes up).

How can the query know to only do the one product (the one on which I pressed the button)? Somehow I need to send it something so it can filter that line and I need to send it the amount to change it by. Does anyone know how?

 

[Isadore]

Soon --

You can restrict the update query by using the Form's current value of Order_ID, q.v.,

In the query's criteria field for Order_ID put:

Forms![MyForm]![Order_ID]

...and in the "Update to" field put:

Forms![MyForm]![Update]

...seems that's all you'd need to do. And then open the query on a button's "OnClick" event..

DoCmd.OpenQuery "qyMyUpdateQuery"

...or you could accomplish this in VB. Using the above solution the Query "knows" to only update this "one" record because the underlying criteria restricts it to the current record's Order_ID and the Update field knows to Update the selected field to the Form's value. Also when you build the update query drag down only those fields that you need (should be two here...)

 

[TheAxe]

Hey its me I got a permanent handle.
I work on every kind of IT project imaginable but I haven't touched access in literally years so its slow going.

Thanks for your help it works.
The funny part is I figured out after that I didn't even need to do it the way I wanted to. I was making the situation more complicated than it needed to be.
Still thanks for showing me that cause Im sure I will use it at some other point in this project.

Take care