Results 1 to 8 of 8
  1. #1
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22

    Unhappy Do Loop Problem

    Hi,

    New to using VBA. When I run this macro, it only processes the first record. I don't know what I am doing here! UGH!

    What is wrong with this picture? (Comments are my attempt to keep things straight)
    Sub RemoveChar()
    Dim rstTbl As DAO.Recordset
    Dim strNewMfg As String
    Dim strNewVendor As String
    Dim strNewPart As String
    Dim myMStr As String
    Dim myVStr As String
    Dim myPStr As String
    Dim char As Variant
    Set rstTbl = CurrentDb.OpenRecordset("Inventory", dbOpenDynaset)
    myMStr = rstTbl![MfgPartNumber]
    myVStr = rstTbl![VendorPartNumber]
    strNewMfg = myMStr
    strNewVendor = myVStr
    strNewPart = myPStr
    '----------------------------------With---------------------------
    With rstTbl
    .MoveFirst
    '-----------------------------If 2------------------------
    If Not rstTbl.BOF And Not rstTbl.EOF Then
    '--------------------DO Until----------------
    Do Until rstTbl.EOF = True
    '-----------For Each----------
    For Each char In Split(SpecialCharacters, ",")
    strNewMfg = Replace(strNewMfg, char, "")
    strNewVendor = Replace(strNewVendor, char, "")
    '------If 1-----
    If strNewMfg = strNewVendor Then
    rstTbl.Edit
    rstTbl![PartNumber] = strNewMfg
    rstTbl.Update
    Else
    rstTbl.Edit
    rstTbl![PartNumber] = strNewMfg & " - " & strNewVendor
    rstTbl.Update
    End If
    '---End If 1-----
    Next
    '-------------Next------------



    '--------------------Exit DO Until----------------
    Exit Do
    Loop
    '--------------------Loop----------------
    End If
    '-------------------------End If 2------------------------
    End With
    '------------------------------End With-------------------------
    End Sub

    Thank you!
    Rosa


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you've got

    Exit Do

    right before it would loop to the next record, but you also need rstTbl.MoveNext to make it do so.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Remove the Exit Do

  4. #4
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Quote Originally Posted by pbaldy View Post
    Well, you've got

    Exit Do

    right before it would loop to the next record, but you also need rstTbl.MoveNext to make it do so.
    Thank you Paul and Dave!

    I made the changes. The part number for all records is the same as the first part number.

    '---End If 1-----


    '-------------Next For Each----------
    Next


    rstTbl.MoveNext
    '--------------------Loop----------------
    Loop


    '-------------------------End If 2------------------------
    End If


    '------------------------------End With-------------------------


    End With


    End Sub

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    These values are set before the loop, so they are never changing during the loop:

    myMStr = rstTbl![MfgPartNumber]
    myVStr = rstTbl![VendorPartNumber]

    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Quote Originally Posted by pbaldy View Post
    these values are set before the loop, so they are never changing during the loop:

    mymstr = rsttbl![mfgpartnumber]
    myvstr = rsttbl![vendorpartnumber]

    *****It works!! Thank you so much for being so smart!!*****
    Last edited by RosaCat; 04-12-2019 at 10:56 AM. Reason: misspelling

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help and welcome to the site by the way!

    I'm not sure if it's being smart or just made similar mistakes so many times it final began to seep in.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Quote Originally Posted by pbaldy View Post
    Happy to help and welcome to the site by the way!

    I'm not sure if it's being smart or just made similar mistakes so many times it final began to seep in.
    Thank you for the welcome!

    You are smart! Accept it!

    How else do we become smarter then by making mistakes? Enjoy your weekend!

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

Similar Threads

  1. VBA Loop Problem
    By yianni in forum Access
    Replies: 6
    Last Post: 03-12-2018, 02:02 AM
  2. Replies: 5
    Last Post: 12-21-2013, 06:26 PM
  3. Do While Loop Problem
    By jhrBanker in forum Import/Export Data
    Replies: 8
    Last Post: 02-16-2012, 09:51 PM
  4. Loop Problem
    By JDPrestige in forum Forms
    Replies: 3
    Last Post: 11-14-2010, 06:48 PM
  5. Replies: 9
    Last Post: 04-28-2010, 11:20 AM

Tags for this Thread

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