First "Find" is quick, all others are slow

[chuckcoleman]

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. 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

 

[RJGoodhead]

Is the indexing set up the same on each form? So the field being searched is indexed on both?

 

[chuckcoleman]

I just 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

 

[tehNellie]

How many records are we talking about in the tables?

Using Find (and replace) is horribly inefficient compared to using a SELECT query. Using Find Access starts at the first row and checks each column value for what you are searching for before moving to the next row. The more Columns and Rows the table has the slower the Find operation will become. In a small table Find can be quicker and easier than using a SELECT query, but once you start to get any volume of data in your table Find can take ages to run.

 

[chuckcoleman]

tehNellie, thank you for your reply. See the one I just left ahead of yours.

Chuck

 

[tehNellie]

Glad you got it sorted out, but it is something to bear in mind. I have a table here with close to a million rows of data. A SELECT query will return the information I wanted in under a second, Find, well, won't (unless I'm lucky enough that whatever I happen to be looking for ends up at the top of the table when it's opened).