update units on order (Access 2000)

Topic

New Reply

I am trying to build a function that updates the units in stock in the table Products based on a specified
OrderID in the table Order Details.I have obtained from a friend a function very close to my needs which runs as follows:

Public Function UpdateStock()

Dim db As Database
Dim strSQL As String
Set db = CurrentDb

strSQL = “UPDATE Products RIGHT JOIN [Order Details] ON products.Productid = “” _
& “[Order Details].ProductID SET Products.UnitsInStock = ” _
& “[Quantity]+[UnitsInStock] ” _
& “WHERE ([Order Details].OrderID)= ” & MyOrder & “;”
DoCmd.RunSQL strSQL

However he doesnt know what MyOrder in the above code may mean. If it is the number of the order,can i change it with a StrWhere pointing to a specific order number to choose from the list?Also, can somebody help me in applying this to my needs?
I have a form called frmCustomerOrders,and in it there is a ListBox called OrdersList. In this list the orders are enumerated according to their orderid.
What i want is with one click to update the selected order.
Also,in the sql what is better to use, right join or inner join?

Just for information i want to inform what i have tried with no success, becasue i received Run Time error 3079
The specified field “orderid” could refer to more than one table listed in the FROM clause in your SQL statement

Public Function UpdateStock()
Dim MyForm As Form
Set MyForm = Forms![frmCustomerOrders]

Dim strWhere As String, strCondition As String
strCondition = “OrderID=” & MyForm.OrderID
strWhere = ” WHERE ” & strCondition

Dim strSQL As String

strSQL = “UPDATE orders INNER JOIN (products INNER JOIN ” & ” [order details] ON (products.Productid = [order details].ProductID) ” & “AND

(products.Productid = [order details].ProductID)) ” & “ON orders.orderid = [order details].OrderID” & _
” SET ” & “products.UnitsOnStock = products.UnitsOnStock +[order details].cartons” & strWhere

CurrentDb.Execute strSQL
End Function

My underlying query ofr the list is the following:

SELECT orders.orderid, orders.orderdate, customers.CompanyName, [order details].cartons
FROM (affiliates INNER JOIN (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid))

ON affiliates.afid = customers.afid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
ORDER BY orders.orderdate;

Reply | Quote

Replies

Let me see if I understand what you are trying to do:

You have a table (Products) of items (ProductID) that contains the onhand inventory (UnitsInStock) of those items. You also have a listbox (OrdersList) containing a list of orders (OrderID) backed by a table (Order Details) containing the items on that order. If you select an order from OrdersList you want to update Products for those items contained in the order’s [Order Details] by adding the Quantity in [Order Details] to the UnitsInStock in Products.

The SQL statement that your friend gave you is essentially correct. What you need to know is how to limit the query to only the selected order. The MyOrder variable should contain the value of the OrderID that was selected from the listbox.

When using a listbox, you can’t directly refer to the selected value. You have to search through the ListBox’s ItemsSelected collection because a listbox can have more than one row selected. The ItemsSelected collection contains a list of the row numbers that are highlighted in the ListBox.

here’s the coding that I think you need:

Public Function UpdateStock()

Dim strSQL As String
Dim varRow As Variant

For Each varRow In Me.OrdersList.ItemsSelected
strSQL = “UPDATE Products RIGHT JOIN [Order Details] ON products.Productid = ” _
& “[Order Details].ProductID SET Products.UnitsInStock = ” _
& “[Quantity]+[UnitsInStock] ” _
& “WHERE [Order Details].OrderID= ” & Me.OrdersList.ItemData(varRow) & “;”
DoCmd.RunSQL strSQL
Next varRow

The good thing about this is you can define your listbox as multi-select and let the user select more than one order at a time if you wish. By the way, the above coding assumes that OrderID is numeric. If it is alpha, then you have to put single quotes before and after the double-quotes:

‘” & Me.OrdersList.ItemData(varRow) & “‘;”

Hope this helps.

Reply | Quote

Dear Sir,

Thank you so much for your nice and clever answer. I have tried without success, The function i have build shows no syntax errors, but when i
click an order in the list box called OrdersList, nothing happens.Therefore i am afraid i have given you a wrong or uncomplete information
about my problem.I think the reason is that my list box is based on the table orders, and not on the table order details.Here i think i have mislead you.
Please allow me to sum up once again my problem.

I have a table (Products) of items (ProductID) that contains the onhand inventory (UnitsInStock) of those items. I also have a listbox (OrdersList) containing a list of orders (OrderID) backed by a table (Orders) that is connected with another table [Order Details] containing the items on that order. The table order details is connected with the table Products. If i select an order from OrdersList i want to update Products for those items contained in the order’s [Order Details] by adding the Quantity in [Order Details] to the UnitsInStock in Products.

The function i have build shows no syntax errors, but when i click an order in the list box called OrdersList, nothing happens, it doesnt update.
I suppose the reason for this is that i am referring in the code to the table [order details], while actually the list box is based on the table orders.
The table orders is related to the table [order details] in one to many relationship.Also the table products is related to the table [order details]
in one to many relationship.
The table orders and order details are connected in cascade connection,that is if i delete an order id everything in order details is deleted.
Actually an orderid in the list is contained only once in the table orders, but more than once in the table order details, depending on the products
in that order. What i want is by clicking just once on any order to update every product in this order by its quantity.

Here is the function i have built according to your advice:

Public function UpdateStock()

Dim strSQL As String
Dim varRow As Variant
For Each varRow In Me.OrdersList.ItemsSelected
strSQL = “UPDATE Products RIGHT JOIN [Order Details] ON products.Productid = ” _
& “[Order Details].ProductID SET Products.UnitsOnStock = ” _
& “[order details].[Quantity]+ Products.[UnitsOnStock] ” _
& “WHERE [Order Details].OrderID= ” & Me.OrdersList.ItemData(varRow) & “;”
DoCmd.RunSQL strSQL
Next varRow
Me.ListOrderID.Requery
Me.Requery
End Function

Could you help me make it work?

Very best regards

Reply | Quote

I have just tried your function in a small database of my own and it worked perfectly, when in removed these two lines:
Me.ListOrderID.Requery
Me.Requery
What are they for? One issue here is that you don’t want to update stock for any one order many times, so for each order you need a field to track whether it has been processed, and only show the unprocessed ones in the list.

I have added a field “updated” boolean to the orders table, then set the listbox to only show the orders that haven’t been updated. I then modified the function as shown below:
Public Function UpdateStock()

Dim strSQL As String
Dim strSQL2 As String
Dim varRow As Variant
For Each varRow In Me.orderslist.ItemsSelected
strSQL = “UPDATE Products RIGHT JOIN [Order Details] ON products.Productid = ” _
& “[Order Details].ProductID SET Products.UnitsOnStock = ” _
& “[order details].[Quantity]+ Products.[UnitsOnStock] ” _
& “WHERE [Order Details].OrderID= ” & Me.orderslist.ItemData(varRow) & “;”
DoCmd.RunSQL strSQL
strSQL2 = “UPDATE Orders set Orders.Updated = true WHERE [Orders].OrderID= ” & Me.orderslist.ItemData(varRow) & “;”
DoCmd.RunSQL strSQL2

Next varRow

Me.orderslist.Requery
‘ Me.Requery
End Function

I called the function by putting =Updatestock() in the onclick event for the button next to the list

Reply | Quote

Dear Sir,

Thank you so much for your answer.I cannot understand why i cannot do it, i have no errors, but no updating occurs.
WIll you agree to send to you an abridged sample of my database, wzpped and no more than 50 Kb ?
Perhaps you will find immeditelt where my error lies

Best regards

Reply | Quote

Do you call the function from the OnClick event for the listbox? Click on the listbox and select Properties. In the Event properties there should be something in the OnClick event to call the function. If not, that’s why your coding is not executing. Also, I don’t know if putting the function call in your OnClick event is the best thing to do. What happens if someone clicks on the same order twice? You’ll update inventory twice!

It would be better to let you select an entry from the listbox, then press a command button whose OnClick event points to your function. After processing your function, you should somehow remove that order from the listbox to prevent selecting it a second time.

Reply | Quote

thank you for your reply.Could you please look up at my example database? it is only 30 KB.Just to repeatr what i want:

Thank you very much for your agreement to send to you my example .It contains only one order, for only one product.
Order 12809 contains only one product called ATF 22. The order is for 1 carton.
The table Products has a field called branch1.It contains 10 cartons
.They must be updated to 11.
When i click the order 12809 in Listbox, i want to get 11 cartons in the warehouse,it means
that the field branch1 shoould be increased with 1 and become 11

Best regards

Reply | Quote

Your attachment did not have any tables, so I couldn’t test it properly. I notice that you attach the function to the onclick event of the list box. This doesn’t seem to work. My guess is that until you have clicked an item, nothing in the list is selected. I put the code in a command button, but I have also tried putting it in the doubleclick event and that worked Ok for me.
I attach a sample

Reply | Quote

It works! I am writing this email to express my gratidue for the kindness and patience you have shown and most of all
for the solution i have received.

Please accept my best regards

Reply | Quote

I’d be glad to look at your database, however, I only have Access97 available to me right now. Could you please convert your database to Access97 (don’t worry, it makes a new copy of the database and leaves your Access2000 database intact) and send me the Access97 database?

Reply | Quote