Results 1 to 5 of 5
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    Update multiple fields in a table with single click

    Dear Gents,


    Um trying to update multiple fields with a single click , But unfortunately i don't know how to loop through records set, I have a piece of code that updates single row only can anyone help

    HTML Code:
     sql_text = "UPDATE Products " & _
        "SET qty = " & qtyRemain & _
        " WHERE PRD_Code= '" & code & "'"
        DoCmd.RunSQL (sql_text)

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont loop thru,
    you execute a query to update them all at once.

    docmd.openquery "quUpdateQry"

  3. #3
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Hi, i know this is an old question but i have the same issue.

    I tried the following which works for updates the selected row only.

    Code:
    Sub Example()
    
    
        Dim rs As DAO.Recordset
        Set rs = Me!EntrySalesDetails.Form.RecordsetClone
            rs.MoveLast
            rs.MoveFirst
    
    
        Dim idx As Integer
        For idx = 1 To rs.RecordCount
            CurrentProject.Connection.Execute "UPDATE TBL_PRODUCTS SET ProdStock = ProdStock +" & Me!EntrySalesDetails.Form!SaleDetailQty & "   WHERE ProdID_PK=" & Me!EntrySalesDetails.Form!ProdID_FK.Column(0) & ""
            rs.MoveNext
        Next idx
    
    
        Set rs = Nothing
    
    
        Exit Sub
    
    
    Leave:
        Set rs = Nothing
    
    
    End Sub
    How to make it handle all rows in the subform EntrySalesDetails ?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    idx is just a counter. If there are 50 records you will update the same record 50 times because the update values never change (as far as I can see). Why use an ADO connection with a DAO recordset? Shouldn't that be CurrentDb.Execute?

    In truth you probably should SET a variable to the CurrentDb (outside the loop) rather than loop over CurrentDb. A variable means the db object reference is only created once.

    Ideally you should start your own thread, not just because this is known as thread hijacking but the way some people search for threads your question might not be seen. I know, it's about 4 years old already and probably not much chance that the OP will come back, but still...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    ok thank you.
    i will open a new thread.

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

Similar Threads

  1. Replies: 6
    Last Post: 02-17-2017, 08:57 AM
  2. Replies: 4
    Last Post: 07-27-2016, 10:44 AM
  3. Replies: 6
    Last Post: 08-30-2012, 06:23 PM
  4. Replies: 10
    Last Post: 08-09-2012, 01:07 PM
  5. Replies: 1
    Last Post: 08-30-2011, 07:35 AM

Posting Permissions

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