If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Wait for it...

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 16
Wait for it...

From what I can tell Access Database that I've inherited / been working on has serious problems with its searching forms; its actively searching every keystroke, meaning that for each number in 984358 the code searches through the 100,000+ records once, scanning for each instance of 9, 98, 984... etc.

Quite frankly, its slow. I've taken a look at the VBA code in the search form; the code centered on the search box looks like this:

Code:
Sub txtSearch_Change()
Me.txtSearch = Me.txtSearch.Text
Me.Cmb_Search = vSearchString
Me.[frmsearch].Requery
End Sub
I'm fairly new to VBA, so is there any way to stop this procedure from doing 600,000+ comparisons. Some ideas I've had was to implement a delaying function to wait until all the numebrs have been typed in, or passing the string to be searched to a command button that initiates the search.

Any ideas?
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,059
so if uou have a problem then remove that code
or find a way to limit it
I guess the original designer intedended this to gradually rebuild the combo/list box as the use entered data

what you could do is say chop the time in half
you could check the length of the current string and if say even then requery, if odd then ignore. but you are not limited to that.. you coudl say search after 5 characters, and then odd numbers from there on in

right now the simplist is probably just ignore the requery and delete that code
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 16
Odd numbers are also required to search for obscured tags etc.

If I turn this into a function, and return a string to be sent to a button that initiates the requery, would that work?

So the txtSearch would handle getting the keyboard input, then sends it to a variable (searchText). A button click will then take searchText, and from there send it to the query that finds all the relevant ID numbers.

Does that sound like it would work? If so how could I implement that?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 965
Move the code to the text box's OnExit event?
__________________
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 16
Tried it, all it does is crash the database.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 965
Really? O.o
Have you tried stepping through the code to see which part causes the crash?
__________________
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 16
All I did was change the _Change() to _OnExit(), and the database refuses to open the record search form. I might have forgotten to roll back some of the changes I have made previously, so I'm checking that now.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 16
Alright, apologies for the double post but I've figured out the problem, solution works but all I'm stuck on is getting cursor back on the txtSearch element so that I can continue typing after losing focus on txtSearch, with an effect similar to clicking on the textbox again? I know about SetFocus, but that doesn't quite do what I want it to do.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 965
Now I'm confused.

According to what you've asked, you require your form to initiate a search as soon as the complete search term has been entered into txtSearch. Accordingly, you've set the code to fire when you exit txtSearch.
Quote:
...I'm stuck on is getting cursor back on the txtSearch element so that I can continue typing after losing focus on txtSearch, with an effect similar to clicking on the textbox again?
This implies that you want to be able to trigger the search at any point while typing, and yet still continue to type more into the search term. This does not make sense.
__________________
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 16
Sorry, I meant that as being able to type in a number (say, 876543) and hit enter without the VBA sending the computer going through each record in the tables once every time I plug in a digit. The loss of focus will start the search and this makes it 6x more efficient. The 'problem' from here is that I have to click back onto the textbox to type in a number to search again.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 965
In that case, investigate the Keypress event in the Help file, and amend the code to fire when the Enter key is pressed.
__________________
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On