Results 1 to 9 of 9
  1. #1
    carsto is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    5

    Loop through rows and update field based on other field data

    I am an advanced Excel/VBA user with basic SQL skills and ZERO Access (2010).
    But we have a database in Access with numerous tables full of bad data that no one here can change automatically so I've accepted the challenge.

    I want to LOOP THRU the tables and in some of them change one field based on the beginning characters in another field.

    The code below came from a couple sources online but it doesn’t work and I know not why. I've left the original sample code lines in so you'll see those and my changes.

    It errors on the EXECUTE line.


    Code:
    Dim rs As DAO.Recordset
      
    '*****
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    Dim Grd As String
        Grd = "XYZ"
    
    For Each tdf In db.TableDefs
        ' ignore system and temporary tables
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            'Member Tables only
            If tdf.Name Like "*Member" Then
    '*****
    'original sample code:  Set rs = db.OpenRecordset("TA Deliverable")
                Set rs = db.OpenRecordset(tdf.Name)
    'original sample code:  CurrentDb.Execute "Update [TA Deliverable] SET [Deliverable Notes] = ' " & New_Deliv_Notes & "' WHERE ID = " & Deliverable_ID
                CurrentDb.Execute "Update [tdf.Name] SET [SGrd] = ' " & Grd & "' WHERE Description like 'ABC*'"
                rs.Edit
    'original sample code:   rs("Deliverable Notes") = New_Deliv_Notes
                rs("SGrd") = Grd
                rs.Update
                rs.Close
                
                '*****
            End If
        End If
    Next
    '*****
    End Sub
    TIA

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    [tdf.name] is not a table name
    "Update " & [tdf.Name] & " SET [SGrd] = ' " & Grd & "' WHERE Description like 'ABC*'"

  3. #3
    carsto is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    5
    but tdf.Name works here:
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then

    So how can I assign a variable to tdf.Name for the loop? The following fails:
    MyTable = tdf.Name
    CurrentDb.Execute "Update [MyTable] SET [SGrd] = ' " & Grd & "' WHERE Description like 'ABC*'"

  4. #4
    carsto is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    5
    Dim MyTable As Variant
    Set MyTable = tdf
    CurrentDb.Execute "Update [" & MyTable & "] SET [SGrd] = ' " & Grd & "' WHERE Description like 'ABC*'"

    So this fails on Type Mismatch, so I'm getting somewhere.
    Need to convert the tdf variable to the table in the loop.....

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Please provide details on why the code I provided does not work. It is called string concatenation if you wish to look it up.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Square brackets are good to add - I assume the rule of never using spaces or special characters in table names!

    Type mismatch - number vs text. Either SGrd is a number or Description is.

  7. #7
    carsto is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    5
    They are both text

  8. #8
    carsto is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    5
    I changed the bracket location in your
    "Update " & [tdf.Name] & " SET [SGrd] = ' " & Grd & "' WHERE Description like 'ABC*'"
    to
    "Update [" & tdf.Name & "] SET [SGrd] = ' " & Grd & "' WHERE Description like 'ABC*'"

    and it worked

    THANKS A MILLION! million records changed that is!

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Great. Try not to use spaces in your table names, you have to always remember those brackets!! Not good practice.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-20-2016, 08:55 AM
  2. Replies: 1
    Last Post: 11-14-2016, 10:36 AM
  3. Replies: 0
    Last Post: 06-17-2016, 08:56 AM
  4. Replies: 1
    Last Post: 05-31-2015, 09:20 AM
  5. Replies: 3
    Last Post: 01-09-2015, 05:48 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
  •  
Other Forums: Microsoft Office Forums