Results 1 to 8 of 8

First "Find" is quick, all others are slow

  1. #1
    ChuckColeman1812 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Springfield, IL
    Posts
    24

    First "Find" is quick, all others are slow

    I'm really scratching my head. I have two different databases. Both have a customer form in them. Both have a command button that has the exact same VBA code that runs a macro. The macro's in both databases are exactly the same, (I copied the code and macro's from one to the other). In the first database when you click on the "Find Customer" command button, it pulls first goes to the last name field of the customer and then pulls up the Find and Replace dialog box. When you type a last name in the database, everything works great. If there are several customers with the same last name, no problem, hit the Enter key or click on the Find Next button and the next customer is found instantly. In the second database, after typing in the customers last name it also quickly finds the customer with that last name. When you then hit Enter or click on Find Next, here is where the behavior changes. In the second database, the "Look In:" combo box in the Find and Replace dialog box which said Current Field immediately becomes grayed out and says Current Document, (grayed out). The database takes forever to find the next record that matches the Find criteria. In the lower left corner of the db it says, "Searching, Press CTRL-BREAK to stop. Why are the two behaving differently? How do I get the second to act like the first?

    Thanks for your help!

    Chuck

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,887
    Are the indexes the same in both db's?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    ChuckColeman1812 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Springfield, IL
    Posts
    24

    First "Find" is quick, all others are slow

    Quote Originally Posted by RuralGuy View Post
    Are the indexes the same in both db's?
    In the first database the Last Name is not indexed. In the second database, (the one with the issue), it originally was not but in my testing I did index it and it didn't change what was happening. The first database's table has about 200 records in it and the second was has
    about 1,900. Neither number is a big number. They both have BE databases containing the tables. The issue still happens if I click in the Last Name field, do a CTRL-F to bring up the Find and Replace dialog box. The fundamental question is why in the second database does
    the Current Field in the Look In: function become gray and change to Current Document. It doesn't in the first database and the searches are quick for the 2nd, 3rd, 4th, etc Finds. I think the problem is related to this.

    Chuck

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,887
    Have you done a Compact and Repair on the db's?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    ChuckColeman1812 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Springfield, IL
    Posts
    24
    Quote Originally Posted by RuralGuy View Post
    Are the indexes the same in both db's?
    Absolutely!

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,887
    I would try *importing* the 2nd db to a fresh new db and see if the issue follow: http://www.btabdevelopment.com/ts/impnew
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    ChuckColeman1812 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Springfield, IL
    Posts
    24
    I found what was causing the problem. On the form there is a sub-form. In the On Current event for the main form there was some code and part of the code changed the focus to the sub form. Therefore, the first find was no problem but each click for finds 2, 3, 4, etc caused the focus to shift to the sub-form which added a lot more records and caused the long delays. I removed the code and the finds now work like a champ. Thank you for responding.
    Chuck

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,887
    Excellent! Thanks for posting back with your success and solution. I marked the thread as Solved.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Replies: 0
    Last Post: 12-06-2011, 09:01 AM
  2. Replies: 1
    Last Post: 04-14-2011, 02:46 PM
  3. Replies: 3
    Last Post: 02-23-2010, 04:32 PM
  4. Error message and How do I find the "value Field" ?
    By createdwithfire in forum Forms
    Replies: 1
    Last Post: 11-05-2009, 10:26 AM
  5. "doCmd - can't find object"
    By drmcfall in forum Access
    Replies: 1
    Last Post: 08-18-2009, 08:41 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
  •  
Tech Forums: Microsoft Office Forums