Results 1 to 10 of 10
  1. #1
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56

    loop recordset and update fields

    I am trying to loop a recordset updating all fields that contain dashes. I am getting errors referring to object property not allowed.




    Code:
    Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
    
    If Not (rs.EOF And rs.BOF) Then
      rs.MoveFirst
      Do Until rs.EOF = True
        If InStr(rs.Field, "-") Then
        rs.Edit
        rs.Field = Replace(rs.Field.value, "-", "")
        rs.Update
        End If
    Loop
    End If

  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 don't loop thru records doing updates....
    this is what update queries are for.
    no need for code.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I agree with using a query. Your error is because you can't use rs.Field like that. It would either be rs!FieldName or if you want all fields, you'd need to loop the fields within the recordset loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56
    Ok, but in this case the file I am loading into the table will be random, I need to loop the record-set and "check" if any of the fields contain dashes, if they do, I can then run a query to update as you mentioned. How can I perform the check?

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Why not just run the query without doing the check? If you run an update query that doesn't actually update anything (because there are no dashes, for example), there is no harm done.

  6. #6
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56
    The problem is that since a different file will be loaded into my table each time I run the program, the field names will change. Each time a file loads, I need to identify which fields on that particular file contain dashes and then remove them. I want to do this just after the file loads (which happens via button click).

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Really only a few small changes from what you had:

    Code:
    dim fld as Field
    Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
    
    while Not rs.EOF 
      rs.edit
        for each fld in rs.fields 
          fld.value = Replace(fld.value, "-", "")
        next
       rs.Update
       rs.movenext
    wend
    End If
    No need to worry about how many field there are or what they are called. If there are no dashes, no changes are made.

  8. #8
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56
    Quote Originally Posted by John_G View Post
    Really only a few small changes from what you had:

    Code:
    dim fld as Field
    Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
    
    while Not rs.EOF 
      rs.edit
        for each fld in rs.fields 
          fld.value = Replace(fld.value, "-", "")
        next
       rs.Update
       rs.movenext
    wend
    End If
    No need to worry about how many field there are or what they are called. If there are no dashes, no changes are made.
    Code in red returns Invalid use of Null Error.

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    That means the field it is looking at contains a Null.

    This should work:

    If not isnull(fld) then fld.value = Replace(fld.value, "-", "")

  10. #10
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56
    Quote Originally Posted by John_G View Post
    That means the field it is looking at contains a Null.

    This should work:

    If not isnull(fld) then fld.value = Replace(fld.value, "-", "")

    Works great. Thanks!!

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

Similar Threads

  1. Replies: 5
    Last Post: 07-09-2015, 11:39 AM
  2. Loop Not Progressing Through Recordset
    By nmlinac in forum Programming
    Replies: 1
    Last Post: 02-15-2013, 10:54 AM
  3. Replies: 1
    Last Post: 06-15-2012, 10:47 AM
  4. Double loop recordset
    By silverspr in forum Programming
    Replies: 7
    Last Post: 03-07-2012, 01:43 PM
  5. Loop through recordset
    By eliedebs in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 12:26 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