Results 1 to 11 of 11

Update multiple product id's

  1. #1
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56

    Update multiple product id's

    Hi ive got an order detail form which has

    product id, product description, customer, quantity and below the form a Total quantity

    When i enter the same product id, and when i press the button to update, the table of stockquantity deducts from totalquantity

    If i use different product ids, it dosnt work, but updates only the current product id, where the mouse current record is set by mouse.

    By the way i use dlookup to check if the stockquantity has enough items.


    any idea how to fix this?

  2. #2
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    Need to see the code behind the update to see what is happening.

  3. #3
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56
    Code:
    Private Sub Εντολή20_Click()
      Dim lookupcheck As String
       'Dim cnn1 As ADODB.Connection
     'Dim myrecordset As New ADODB.Recordset
     'Set cnn1 = CurrentProject.Connection
     'myrecordset.ActiveConnection = cnn1
              
     'myrecordset.Open " SELECT Sum([TransactionLine].Quantity) as SumofQuantity" & _
      '         " FROM [products] INNER JOIN TransactionLine ON " & _
       '        "[products].ProductId = TransactionLine.ProductId " & _
        '       " GROUP BY [products].ProductId", cnn1, adOpenStatic, adLockOptimistic
    
     lookupcheck = DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId")
     If TransactionTypeId = 1 Then
     
        If DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId") < Forms![TransactionLine].Sumqtr Then
         MsgBox "No stock, current stock is: " & lookupcheck & " ", vbCritical, "Warning"
        Else
          
                mysql = "UPDATE products INNER JOIN TransactionLine ON " & _
                "[products].ProductId = TransactionLine.ProductId " & _
                "SET products.apothema = products.apothema" & _
                " - forms![TransactionLine].sumqtr" & _
                " where products.Productid=forms![transactionline].Productid"
                DoCmd.SetWarnings False
                DoCmd.RunSQL mysql
                
                
                mysql2 = "UPDATE products INNER JOIN TransactionLine ON " & _
                "[products].ProductId = TransactionLine.ProductId " & _
                "SET products.FinalPrice = products.apothema * products.Price" & _
                " where products.Productid=forms![transactionline].Productid"
                DoCmd.SetWarnings False
                DoCmd.RunSQL mysql2
                MsgBox "Records Updated", , "Records"
                DoCmd.SetWarnings True
        End If
     End If
       
    End Sub

    probably you see some comment with ado.database.
    since im newbiew, i keep this for future reference.
    i know is something going with the group by, but i cannot do it in update

    Do you have any recommendations?

  4. #4
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    At the top of the page do you have

    Option Explicit

    If not add it and recompile.

    After that can you show where the code is causing problems. Which Line?

    Edit

    Can you also delete all unnecessary comments. Then repost the code.

  5. #5
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56
    Quote Originally Posted by Rainlover View Post
    At the top of the page do you have

    Option Explicit

    If not add it and recompile.

    After that can you show where the code is causing problems. Which Line?

    Edit

    Can you also delete all unnecessary comments. Then repost the code.
    ---------------------------------------------------------------------------------
    It works fine.. but how can i do it to update with multiple productid, not just with the same


    Private Sub Εντολή20_Click() Dim lookupcheck As String lookupcheck = DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId" ) If TransactionTypeId = 1 Then If DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId" ) < Forms![TransactionLine].Sumqtr Then MsgBox "No stock, current stock is: " & lookupcheck & " ", vbCritical, "Warning" Else mysql = "UPDATE products INNER JOIN TransactionLine ON " & _ "[products].ProductId = TransactionLine.ProductId " & _ "SET products.apothema = products.apothema" & _ " - forms![TransactionLine].sumqtr" & _ " where products.Productid=forms![transactionline].Productid" DoCmd.SetWarnings False DoCmd.RunSQL mysql mysql2 = "UPDATE products INNER JOIN TransactionLine ON " & _ "[products].ProductId = TransactionLine.ProductId " & _ "SET products.FinalPrice = products.apothema * products.Price" & _ " where products.Productid=forms![transactionline].Productid" DoCmd.SetWarnings False DoCmd.RunSQL mysql2 DoCmd.SetWarnings True End If End If End Sub
    Attached Thumbnails Attached Thumbnails warehouse.jpg  

  6. #6
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56
    Private Sub Εντολή20_Click() Dim lookupcheck As String 'Dim cnn1 As ADODB.Connection 'Dim myrecordset As New ADODB.Recordset 'Set cnn1 = CurrentProject.Connection 'myrecordset.ActiveConnection = cnn1 'myrecordset.Open " SELECT Sum([TransactionLine].Quantity) as SumofQuantity" & _ ' " FROM [products] INNER JOIN TransactionLine ON " & _ ' "[products].ProductId = TransactionLine.ProductId " & _ ' " GROUP BY [products].ProductId", cnn1, adOpenStatic, adLockOptimistic lookupcheck = DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId" ) If TransactionTypeId = 1 Then If DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId" ) < Forms![TransactionLine].Sumqtr Then MsgBox "No stock, current stock is: " & lookupcheck & " ", vbCritical, "Warning" Else mysql = "UPDATE products INNER JOIN TransactionLine ON " & _ "[products].ProductId = TransactionLine.ProductId " & _ "SET products.apothema = products.apothema" & _ " - forms![TransactionLine].sumqtr" & _ " where products.Productid=forms![transactionline].Productid" DoCmd.SetWarnings False DoCmd.RunSQL mysql mysql2 = "UPDATE products INNER JOIN TransactionLine ON " & _ "[products].ProductId = TransactionLine.ProductId " & _ "SET products.FinalPrice = products.apothema * products.Price" & _ " where products.Productid=forms![transactionline].Productid" DoCmd.SetWarnings False DoCmd.RunSQL mysql2 MsgBox "Records Updated", , "Records" DoCmd.SetWarnings True End If End If End Sub

  7. #7
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    It works fine.. but how can i do it to update with multiple productid, not just with the same

    How can it work fine. There must be an error somewhere.

    e.g. You have mysql and mysql2 as Variables but you have not declared them.

    If you have the Option Explicit statement then the compile will fail on each of these.

    It is difficult to help solve the problem when I do not know where the code is failing.

    You could add a lot of MsgBox statements to see if the various lines of code are been executed.

    Have another try and when you post your code don't forget to wrap them in Code Tags.

  8. #8
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    Are you using a Form SubForm situation?

    Try doing the update on the Before Update Event of each line.

  9. #9
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    I should have said this earlier.

    What you are doing is storing calculated values which is not recommended for your situation.

    The following is a link to a good article and some code on a better way to do this.

    http://allenbrowne.com/AppInventory.html

    If you like I can post a sample data base.

  10. #10
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56
    well i dont understand much of ado database...
    usually can you give me an example how can i do an update using group by

  11. #11
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    Did you read the link I posted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-24-2012, 12:44 AM
  2. Total by product
    By Alexpi in forum Queries
    Replies: 1
    Last Post: 05-24-2011, 02:19 PM
  3. Update product parts
    By honey2wood in forum Forms
    Replies: 17
    Last Post: 04-15-2011, 01:20 AM
  4. Update Multiple fields according multiple criterias
    By denis.m in forum Programming
    Replies: 1
    Last Post: 02-21-2011, 09:03 AM
  5. Replies: 0
    Last Post: 12-14-2009, 07:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums