Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77

    Exclamation CYLCE THROUGH RECORD in the background

    I'm trying to use the following code to cycle through my records. However, it displays the cycle of each record. With 1000+ records it takes a long time. Is there a way to not show each record being cycled through????



    Code:
        With Me.Recordset
            Do While Not .EOF
                If !cmBOX Then
                    Call .Edit
                    !cmBOX = False
                    Call .Update
                End If
                Call .MoveNext
            Loop
        End With

  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
    Execute an update query with the same criteria that the form has.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    What do you mean it shows each record. If it is code in the background it should not show anything. Do you mean it is just taking a long time to complete the cycle? How often do you runt he code? Also try Paul's suggestion.

  4. #4
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by Bulzie View Post
    What do you mean it shows each record. If it is code in the background it should not show anything. Do you mean it is just taking a long time to complete the cycle? How often do you runt he code? Also try Paul's suggestion.
    Hello Bulzie,

    When I open the form, which is tied to a query, the form starts going through each record and unchecking each box. However, it displays the by running from top to bottom of the list. It takes a long time for it to cycle. I'm not sure what Paul means.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So are you wanting that field to default to False for all records when the form opens? Is that a combo box or check box?

  6. #6
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by Bulzie View Post
    So are you wanting that field to default to False for all records when the form opens? Is that a combo box or check box?
    It's a check box. and Yes, to default to False.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So on the field on the form, put False as the default on the properties screen? Or you can put False as the default value for that field on the table?

  8. #8
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by Bulzie View Post
    So on the field on the form, put False as the default on the properties screen? Or you can put False as the default value for that field on the table?
    I tried something similar. However, the issue at hand is that I that code is useful and I was hoping someone could help me modify it to run without displaying the cycle through records on the Display section.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In simple plain English what are you trying to accomplish?

    Is the issue to change every value of cmBox to false??

    If so, then see this for Update query.

  10. #10
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by orange View Post
    In simple plain English what are you trying to accomplish?

    Is the issue to change every value of cmBox to false??

    If so, then see this for Update query.
    Hi orange, I'll try to walk through what I'm trying to accomplish.

    Form is bound to Query. the code displayed above does the following. Every time the form opens. That check box becomes unchecked from all the records and updates table. When the form opens, it will display all the records stored. However, this takes a while because the codes goes through each record and makes sure each record is unchecked.

    What I need is this to do what it is currently doing with a minor change. Only cycle through the records in the background.

  11. #11
    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 point is, "cycling through the records" is the least efficient method. Create an update query with the same criteria as the query the form is bound to (if any), and execute it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Somewhere in your query, you refer to a table that contains the field cmBox,,
    my guess is that if you run an SQL update statement to "reset" the cmBox field in that table, it will do the reset.
    Then open your query and all the cmBox fields will be properly set when you open your form.
    In effect, you are changing the way you do the "reet of the cmBox field". Th SQL Update should be very quick and "in the background".

    What is the SQL of your query?
    Last edited by orange; 12-11-2017 at 12:59 PM. Reason: spelling

  13. #13
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by pbaldy View Post
    The point is, "cycling through the records" is the least efficient method. Create an update query with the same criteria as the query the form is bound to (if any), and execute it.
    pblady, would I execute on the onload or onopen this update query?

  14. #14
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by orange View Post
    Somewhere in your query, you refer to a table that contains the field cmBox,,
    my guess is that if you run an SQL update statement to "reset" the cmBox field in that table, it will do the reset.
    Then open your query and all the cmBox fields will be properly set when you open your form.

    What is the SQL of your query?

    the SQL utilizes the Where cmBOX = "NO" at the end of the table select equation.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by BRASILO View Post
    pblady, would I execute on the onload or onopen this update query?
    Wherever you want the action to happen. Sounds like either of those events, though I'd try the open event first, since you'd probably have to requery in the load event.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-12-2017, 08:24 AM
  2. Replies: 8
    Last Post: 11-28-2015, 12:00 PM
  3. Replies: 5
    Last Post: 06-28-2013, 06:11 PM
  4. Replies: 2
    Last Post: 03-05-2013, 04:11 PM
  5. Clicking on Form Background Adds New Record?
    By swimmermx in forum Forms
    Replies: 3
    Last Post: 08-03-2010, 12:14 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