Update a field

[Guest]

Hello,

I have three linked tables: Supplier, Product, Supplier Order

I have a supplier order form, in there I select a Supplier ID (from Supplier
Table) and a Product ID (from Product Table). Also there is a manually filled
in quantity field. When I populate that field I want to click onto a command
button that adds the quantity onto the Stock Qunatity field (from Product
Table) whislt saving the record in the Supplier Order table as normal. I
pretty sure it's an update query that must be linked through a macro, but
after many attempts I need the answer please.

Thanks,

 

[Guest]

Why do you want to save this data again in the stock table, you can always
create a select query that sum quantities per Item in the Order table, and
then subtruct it from the Stock Table, that should display the Stock In hand
(If that what you want to get)

If you need help with the queries, please post back with more details about
the table and fields names

 

[Guest]

It's about stock control.
The product information table holds how much stock there is of each item.
When an order is placed with a customer or supplier, I would like the Stock
Quantity field to update accordingly so the user knows how much stock is left
without manually subtracting or adding the data.

 

[Guest]

There is no need to move this quantity to the stock table, in the form create
a text box that display the stock in hand with something like

=Nz(DlookUp("[Stock]","[StockTable]","[ItemNum]=" & [ItemNumInthe form]),0)
- Nz(DSum("[Quantity]","[OrderTable]","[ItemNum]=" & [ItemNumInthe form]) ,0)

Should be in the control source of the text box, also make sure that the
fields names are correct

 

[Guest]

One more thing, if the item fields is text type add single quotes

=Nz(DlookUp("[Stock]","[StockTable]","[ItemNum]='" & [ItemNumInthe form] &
"'"),0)
- Nz(DSum("[Quantity]","[OrderTable]","[ItemNum]=" & [ItemNumInthe form] &
"'") ,0)


--
Good Luck
BS"D

There is no need to move this quantity to the stock table, in the form create
a text box that display the stock in hand with something like

=Nz(DlookUp("[Stock]","[StockTable]","[ItemNum]=" & [ItemNumInthe form]),0)
- Nz(DSum("[Quantity]","[OrderTable]","[ItemNum]=" & [ItemNumInthe form]) ,0)

Should be in the control source of the text box, also make sure that the
fields names are correct

--
Good Luck
BS"D

It's about stock control.
The product information table holds how much stock there is of each item.
When an order is placed with a customer or supplier, I would like the Stock
Quantity field to update accordingly so the user knows how much stock is left
without manually subtracting or adding the data.

 

[Guest]

Thanks, I shall give it a go
--
Paul J

One more thing, if the item fields is text type add single quotes

=Nz(DlookUp("[Stock]","[StockTable]","[ItemNum]='" & [ItemNumInthe form] &
"'"),0)
- Nz(DSum("[Quantity]","[OrderTable]","[ItemNum]=" & [ItemNumInthe form] &
"'") ,0)


--
Good Luck
BS"D

There is no need to move this quantity to the stock table, in the form create
a text box that display the stock in hand with something like

=Nz(DlookUp("[Stock]","[StockTable]","[ItemNum]=" & [ItemNumInthe form]),0)
- Nz(DSum("[Quantity]","[OrderTable]","[ItemNum]=" & [ItemNumInthe form]) ,0)

Should be in the control source of the text box, also make sure that the
fields names are correct

--
Good Luck
BS"D

It's about stock control.
The product information table holds how much stock there is of each item.
When an order is placed with a customer or supplier, I would like the Stock
Quantity field to update accordingly so the user knows how much stock is left
without manually subtracting or adding the data.
--
Paul J


:

Why do you want to save this data again in the stock table, you can always
create a select query that sum quantities per Item in the Order table, and
then subtruct it from the Stock Table, that should display the Stock In hand
(If that what you want to get)

If you need help with the queries, please post back with more details about
the table and fields names

--
Good Luck
BS"D


:

Hello,

I have three linked tables: Supplier, Product, Supplier Order

I have a supplier order form, in there I select a Supplier ID (from Supplier
Table) and a Product ID (from Product Table). Also there is a manually filled
in quantity field. When I populate that field I want to click onto a command
button that adds the quantity onto the Stock Qunatity field (from Product
Table) whislt saving the record in the Supplier Order table as normal. I
pretty sure it's an update query that must be linked through a macro, but
after many attempts I need the answer please.

Thanks,