Results 1 to 11 of 11

Thread: Wait for it...

  1. #1
    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?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,419
    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 on the Tiger 800 or the Norton

  3. #3
    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?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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.

  5. #5
    Join Date
    Dec 2012
    Posts
    16
    Tried it, all it does is crash the database.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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.

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

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

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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.
    ...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.

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

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •