Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update units on order (Access 2000)

    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;

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update units on order (Access 2000)

    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 <font color=448800>ItemsSelected</font color=448800> collection because a listbox can have more than one row selected. The <font color=448800>ItemsSelected</font color=448800> 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:

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">'</span hi>" & Me.OrdersList.ItemData(varRow) & "<span style="background-color: #FFFF00; color: #000000; font-weight: bold">'</span hi>;"

    Hope this helps.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update units on order (Access 2000)

    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

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update units on order (Access 2000)

    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
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update units on order (Access 2000)

    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

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update units on order (Access 2000)

    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.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update units on order (Access 2000)

    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
    Attached Files Attached Files

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update units on order (Access 2000)

    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
    Attached Files Attached Files
    Regards
    John



  9. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update units on order (Access 2000)

    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

  10. #10
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update units on order (Access 2000)

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •