Results 1 to 10 of 10
  1. #1
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95

    Problem with Trying to Repeat Records in VBA

    I'm working on a form that allows a user to enter to text fields and then a number that allows the record to be repeated n times. The problem is that the loop never ends.



    Could this be a mismatch of data types? I think the problem is in the do until line but I can't track it down. Any help is appreciated, here is the code I am using:

    Option Compare Database
    Private Sub addrec_bttn_Click()
    Dim dbInventory As DAO.Database
    Dim rstTest As DAO.Recordset

    Set dbInventory = CurrentDb
    Set rstTest = dbInventory.OpenRecordset("Inventory_tbl")

    intCopies = Forms!invent_frm!Copies_txt
    intstart = 1

    Do Until intstart = intCopies

    rstTest.AddNew
    rstTest("TestCode").Value = Forms!invent_frm!Testcode
    rstTest("TestTitle").Value = Forms!invent_frm!TestTitle
    rstTest("TestID").Value = intstart
    rstTest.Update
    intstart = intstart + 1

    Loop

    End Sub

  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
    Offhand the code looks okay, so my first thought would be some sort of mismatch. I note the two variables aren't declared here; are they elsewhere? If not, the first thing would be to declare both variables as Integers, which might solve the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    as Paul said, the variables are probably the issue.

    If the loop never ends, intstart is never reaching intCopies, so that tells you right away that intCopies is either larger than 50,000 (takes a few seconds to write this many records), 0 or NULL. Any of these cases will give you and endless loop if you're only waiting a few seconds. put a break line at the beginning of the loop and hover the mouse over "intCopies" a few lines before the loop. You'll see what value you gave it.

    Use Ctrl+Break to stop code too. NEVER Ctrl+Alt+Delete your program out, or you're inviting corruption (just a tip!).

  4. #4
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    Thanks guys, variable declaration will be the death of me. One more question though. I've noticed that as I add more TestCodes, the loop seems to over write one of the previous test codes already entered with one of the testcodes I am currently entering.

    When I added the second testcode, a record with blank testID appeared. Wehn I added my third testcode, it replaced one of the testcodes from my second entry and kept the testID of 2. Any ideas?

    I need to add some error checks and the ability to add testIDs on to current testcodes, so maybe I will find it then.

  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
    I don't see offhand why it would overwrite a record, unless the form is bound to the table. I'd also point out that as written, I suspect you're going to get one less record than you want. On the last record, the two variables will be equal so it will drop out of the loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    I was getting one less record and I found that it was overwriting the first record in the table because the form was opening to that record. I fixed both those issues, but I still get an extra record with a blank TestID (i.e. if I repeat the record 5 times, I get 5 records, plus 1 record with a blank TestID. Is there a way to get around that?

  7. #7
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    What you can do (and this applies to future coding too) is experiment it with it yourself. For example, if you're not sure when code exists a loop, write some dummy code at the beginning of the loop and step through it using F8. Another thing you could do is write something like:

    Code:
    do until a = b
    
       if a = b
          'do nothing
             exit do
       end if
    
    'MORE CODE
    
    loop
    put a break point on:
    Code:
    exit do
    if your code breaks there, then you know that it is executing at least a part of the loop even when the condition is reached. strategies like this are good for learning how code works too.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The blank is probably coming from the form being bound to the table. I would probably have it unbound.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    I tried Adam's idea and I still get an extra blank record (TestTitle and TestCode are populated, just not TestId).

    Paul, if I make the form unbound, how do I move my data from the form to the table? Is there way to keep the form bound to the table but avoid the blank TestID codes? I suppose I could run a delete query on open of the form where TestID is null.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My assumption was that the code was adding the required records to the table. The form does not have to be bound for that to occur. Generally speaking, you add via a bound form or code, not both.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 09-30-2009, 09:40 AM
  2. Problem with "join" duplicating records
    By Zukster in forum Queries
    Replies: 0
    Last Post: 08-25-2009, 09:00 AM
  3. Replies: 0
    Last Post: 10-23-2008, 12:08 AM
  4. Problem Selecting Records for a Report
    By Joe in forum Programming
    Replies: 0
    Last Post: 09-27-2008, 02:27 PM
  5. Problem Deleting Records with ADO
    By bdicasa in forum Programming
    Replies: 0
    Last Post: 08-21-2008, 09:27 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