Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: Loop through query field to update table values

    Hi,

    The following is the code I have. It is meant to take values from a linked excel table (which change daily) and add any new item numbers to a table AssignedToTbl. One of the fields in this table, Item Status, changes on a daily basis. So I am trying to figure out a method to get this field to get updated based on a separate query I have created (UpdateItemStatus).


    Code:
    Dim dbs As Database
    Dim rst As Recordset
    Dim rst_2 As Recordset
    Dim qdf As QueryDef
    Dim qdf_2 As QueryDef
    Dim strSQL As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String
    Dim itemnumber As String
    
    Set dbs = CurrentDb()
    Set rst_2 = dbs.OpenRecordset("AutoReleaseExceptionsTbl", dbOpenDynaset)
    
    'Delete previous query
    On Error Resume Next
    dbs.QueryDefs.Delete "UpdateItemStatus"
    dbs.QueryDefs.Delete "AutoReleaseExceptions"
    On Error GoTo 0
    
    'Create query
    strSQL = "Select DISTINCT [Item number], [Item Status], [Planner code]  " & _
        "FROM AutoReleaseExceptionsTbl WHERE [Planner code] < '15';"
        
    strSQL3 = "SELECT DISTINCT [Project Number], [Item number], [Planner code], " & _
        "[Planner suffix], [Item Status], [Item Description] " & _
        "FROM AutoReleaseExceptionsTbl;"
        
    Set qdf = dbs.CreateQueryDef("UpdateItemStatus", strSQL)
    Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    
    Set qdf_2 = dbs.CreateQueryDef("AutoReleaseExceptions", strSQL3)
    Set rst_2 = qdf_2.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    
    rst_2.Close
    qdf.Close
    qdf_2.Close
    
    strSQL2 = "Insert Into AssignedToTbl SELECT AutoReleaseExceptions.[Item number], AutoReleaseExceptions.[Project number], " & _
        "AutoReleaseExceptions.[Planner code], AutoReleaseExceptions.[Item Status] " & _
        "FROM AutoReleaseExceptions WHERE [Item number] not in (SELECT [Item Number] from AssignedToTbl) " & _
        "AND AutoReleaseExceptions.[Planner code] < '15';"
        
    dbs.Execute strSQL2
    
    Set rst = dbs.OpenRecordset("UpdateItemStatus")
    
    rst.MoveFirst
    Do Until rst.EOF
    
        UPDATE AssignedToTbl SET [Item status] =
    
    
    dbs.Close
    The bold part is where I'm struggling... I want the Item Status to be updated in AssignedToTbl based on the item status in the first query created (UpdateItemStatus) with the condition that AssignedToTbl.[Item number] = UpdateItemStatus.[Item number]. I'm not sure if there's a way to do this with SQL or if there is another way... Could anyone please help? I have searched around the internet but haven't had much luck with this issue.
    Thanks in advance!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In Access' query designer, make a new query as follws:
    Code:
    From AssignedToTbl INNER JOIN UpdateItemStatus ON AssignedToTbl.[Item number] = UpdateItemStatus.[Item number]
    Now make the UPDATE appropriately. Save it as a query. In your VBA, when you finish the status calculations and Executing strSQL2, simply do a DoCmd.OpenQuery on the UPDATE query.

    Sam

  3. #3
    Join Date
    Nov 2011
    Posts
    57
    I tried the following and got an error message "Operation must use an updateable query"

    Code:
    UPDATE AssignedToTbl INNER JOIN UpdateItemStatus ON AssignedToTbl.[Item number] = UpdateItemStatus.[Item number] SET AssignedToTbl.[Item Status] = UpdateItemStatus.[Item status] WHERE AssignedToTbl.[Item number] = UpdateItemStatus.[Item number];
    I am not really sure what this means... Also, is this an acceptable criteria? Most update queries I've seen specifiy a value for the criteria. However, I want this query to match up the item numbers and change the status based on that.
    Last edited by nic311; 03-06-12 at 08:53.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that message usually means the SQL engine has no way of identifying what rows need updating, usually that means the SQL parser doesn't have a value or column in a whjere clause. often thats fixed by explicitly including the appropriate column(s) by identifying the tablename eg tablename.columnname

    however in this case Im pretty certain the JOIN is in the wrong place and that is throwing the SQL parser
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    57
    I ended up going with the following code:

    Code:
    Set rst = dbs.OpenRecordset("UpdateItemStatus")
    
    rst.MoveFirst
    Do Until rst.EOF
    
    item = rst.Fields("Item number").Value
    status = rst.Fields("Item status").Value
    
    strSQL = "UPDATE AssignedToTbl SET [Item Status] = '" & status & "' WHERE [Item number] = '" & item & "';"
    
    dbs.Execute strSQL
    
    rst.MoveNext
    
    Loop
    
    dbs.Close
    rst.Close
    It seems to work well and was easier for me as I'm not super familiar with update queries. Thanks for the help!

Posting Permissions

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