Update a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
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
 
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.
 
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
 
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


Ofer Cohen said:
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


luser error said:
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.
Click to expand...
Click to expand...
 
Thanks, I shall give it a go
--
Paul J


Ofer Cohen said:
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


Ofer Cohen said:
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


luser error said:
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,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top