Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Are your tables Access or Linked SQL tables? If SQL you could do a pass-through query to update that field to False and it should take no time to do that. Pass through queries run fast as it is done on the SQL side. If Access, try what Paul is suggesting using the update query.

    Another question on the data, are you importing loads of new records each day? What if someone checks a record as True and then the next day would it get moved back to False? What is the process where you need that field to be False every time the form opens?

  2. #17
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by Bulzie View Post
    Are your tables Access or Linked SQL tables? If SQL you could do a pass-through query to update that field to False and it should take no time to do that. Pass through queries run fast as it is done on the SQL side. If Access, try what Paul is suggesting using the update query.

    Another question on the data, are you importing loads of new records each day? What if someone checks a record as True and then the next day would it get moved back to False? What is the process where you need that field to be False every time the form opens?
    I'm using access tables. Although I may explore SQL as well. Since its faster..? A sample db would be great!

    I'll work on everyone's suggestions and get back to you guys tomorrow. Thanks for pointing me on the right direction.

  3. #18
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by Bulzie View Post
    Are your tables Access or Linked SQL tables? If SQL you could do a pass-through query to update that field to False and it should take no time to do that. Pass through queries run fast as it is done on the SQL side. If Access, try what Paul is suggesting using the update query.

    Another question on the data, are you importing loads of new records each day? What if someone checks a record as True and then the next day would it get moved back to False? What is the process where you need that field to be False every time the form opens?
    I attempted all the examples given. I got the update query working. However, I found myself in a position where the form utilized the function in a unique way / updating on demand while using the form. With the pre-Update qry. This feature wasn't possible. So once again, I'm back to square one.

  4. #19
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe still not clear on what you want to do as this should not be that hard. Can you explain in detail what you are trying to do. Also are records added to the database by the users or are you importing records each day, etc. Give us an example of how the process should work from start to finish.

  5. #20
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by Bulzie View Post
    Maybe still not clear on what you want to do as this should not be that hard. Can you explain in detail what you are trying to do. Also are records added to the database by the users or are you importing records each day, etc. Give us an example of how the process should work from start to finish.
    Hi Bulzie,

    The form where that code snip is located does the following:

    * When the form opens it pulls all the data from a table and displays it. There is roughly 6 columns of data that is being displayed in the "Detail" section.
    * inside the detail section, where each record loads per line, there is a checkbox. That code unchecks each box and sets all the data in the check and the table to 0 for that checkbox.
    *when this form opens it cycles through each record, it's like watching a long list scroll down by its self. This can take a while since there is over a 1000 records.
    * Also, this checkbox is used for selecting records and sending it through a query to populate a report.
    * I was hoping to have the open function cycle through the records in silence. Meaning, not display the actual process that is currently in place.

  6. #21
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    From that description running the update query before opening the form makes the most sense. What exactly does this mean, and why does running the update before not work? Even if you had to do it again after the form was open and in use, I'd use the update query. It's the most efficient way of updating records.

    Quote Originally Posted by BRASILO View Post
    I got the update query working....I found myself in a position where the form utilized the function in a unique way / updating on demand while using the form. With the pre-Update qry. This feature wasn't possible.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #22
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Don't know enough about your table structure to be specific, but I would simply run an update query to modify the desired field BEFORE opening the form.
    An update query of this sort should run in under a second or two for 1000 records.
    Something like:
    UPDATE yourTableName SET cmBox = FALSE
    Last edited by davegri; 12-15-2017 at 12:02 PM. Reason: more

  8. #23
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by pbaldy View Post
    From that description running the update query before opening the form makes the most sense. What exactly does this mean, and why does running the update before not work? Even if you had to do it again after the form was open and in use, I'd use the update query. It's the most efficient way of updating records.
    Okay, maybe I need to restructure the process I'm doing. I'll take some time to rework the flow of info in my DB. Thanks.

  9. #24
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by BRASILO View Post
    Okay, maybe I need to restructure the process I'm doing. I'll take some time to rework the flow of info in my DB. Thanks.
    Solution that worked for me. I put this in the onload(). It worked well!


    Code:
        If Me.Dirty Then Me.Dirty = False
        CurrentDb.Execute "UPDATE mainTBL SET mainTBL.cmbPRINT = No;"
        Me.Requery

  10. #25
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's basically what I suggested in post 2. I'd probably use the open event, which is before data is loaded, which should eliminate the need for the requery. You wouldn't need the first line in either event, since no update could have been made yet.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #26
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by pbaldy View Post
    That's basically what I suggested in post 2. I'd probably use the open event, which is before data is loaded, which should eliminate the need for the requery. You wouldn't need the first line in either event, since no update could have been made yet.
    Yes, I agree with your suggestion. However, it wasn't the desired approach I was after. Either way, thanks!

  12. #27
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a comment about the code in Post #1. Why is the "Call" command used?

    The code should be
    Code:
        With Me.Recordset
            Do While Not .EOF
                If !cmBOX Then
                    .Edit
                    !cmBOX = False
                    .Update
                End If
                .MoveNext
            Loop
        End With
    The "Call" command is not necessary and shouldn't be used.

    Also, I wouldn't use the form record set. Of course it will be slow because you are having to update the screen every time the current record changes.

    I would think creating a record set in memory (code) would be faster.
    Code:
     Dim rs As DAO.Recordset
    
     Set rs = Me.Recordset
    
        With Me.Recordset
            Do While Not .EOF
                If !cmBOX Then
                    .Edit
                    !cmBOX = False
                    .Update
                End If
                .MoveNext
            Loop
        End With
    
     rs.close
     Set rs = Nothing

    But the fastest method is using a query!!

  13. #28
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by ssanfu View Post
    Just a comment about the code in Post #1. Why is the "Call" command used?

    The code should be
    Code:
        With Me.Recordset
            Do While Not .EOF
                If !cmBOX Then
                    .Edit
                    !cmBOX = False
                    .Update
                End If
                .MoveNext
            Loop
        End With
    The "Call" command is not necessary and shouldn't be used.

    Also, I wouldn't use the form record set. Of course it will be slow because you are having to update the screen every time the current record changes.

    I would think creating a record set in memory (code) would be faster.
    Code:
     Dim rs As DAO.Recordset
    
     Set rs = Me.Recordset
    
        With Me.Recordset
            Do While Not .EOF
                If !cmBOX Then
                    .Edit
                    !cmBOX = False
                    .Update
                End If
                .MoveNext
            Loop
        End With
    
     rs.close
     Set rs = Nothing

    But the fastest method is using a query!!

    Thanks for the reply, I tried using CALL first as that was the only approach I had tried with that particular segment of my code. I didn't think of removing it and trying your way. I'll definitely give your edit a try. Many Thanks!

Page 2 of 2 FirstFirst 12
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