Code behind "Find Record" button turns off NumLock

What happens if you run these commands manually?

Is it possible that you have one of these keyboards that enable you to program shortcut keys. If you have one of these, it is possible that one of the shortcuts is program to work with the NumLock.

Finally, if you programmed that because you prefer that type of search, note that you can set it as your default through Tools...Options.

You may have run into this reported bug by Microsoft.

This is a know issue on some systems,...see here:
http://support.microsoft.com/kb/179987/en-us

The standard solution is to insert a "DoEvents" between multiple sendkeys commands.

Also note that Sendkeys is becoming increasing unpredictable and difficult to control in the more recent versions of Windows/VBA/Access.
MS has,  ...on several occasions, ...hinted that Sendkeys may not be supported at all at some point, ...so you may wish to consider another way of doing this...
Sendkeys should always/only be used as a *Very Last Resort*

If you need this level of control, ...consider either filtering the form,...or creating your own utility to "Find" records matching your criteria.

At the higher end you can create your own utility to "simulate" the find dialog box...
Here is a very basic sample you can reference as a starting point.
Access-EEQ--26449067-27019231-SimpleSear

@Martin Liss
Sorry for the dupe reference to the MS link, ...
By you link caption, ...I thought you posted a link to "report" this as a bug to MS....

@TimDeaton,

The other info I posted should still be relevant

;-)

JeffCoachman

The code you are currently using harkens back to the A95 (acMenuVer70) interface which was 20!!!! years ago.  It was poor then but worse now. This will bring up the find dialog:

docmd.RunCommand acCmdFind

You may still need to use the send keys to change the properties.  I can't find any default property that alters how the form opens.

You ABSOLUTELY cannot use SendKeys with MS Access on Windows 7.
The KB article linked to, is from earlier and different problems.
But when we moved from XP to Win 7, this problem cropped up and cannot be overcome.
You need to get rid of your Sendkeys statements.
I tried to code a routine that would detect the state of the NumLock and engage it if it was not on.
It failed miserably, because the problem occurs asynchronously enough that you can NOT fix it immediately after the SendKeys statements occur.

The button, titled "Find Record", runs code that allows me to find a sub-string anywhere within the active field (not just at the beginning of the field).
There are MUCH better ways to do what you are doing.  SelLength(), SelText() and SelStart exist for this very purpose.  Have a gander at them in the help

Sub Find_Click()
    Dim strSearch As String, intWhere As Integer
    Dim ctlTextToSearch As Control
    ' Get search string from user.
    With Me!Textbox1
        strSearch = InputBox("Enter text to find:")
        ' Find string in text.
        intWhere = InStr(.Value, strSearch)
        If intWhere Then
            ' If found.
            .SetFocus
            .SelStart = intWhere - 1
            .SelLength = Len(strSearch)
        Else
            ' Notify user.
            MsgBox "String not found."
        End If
    End With
End Sub

Select allOpen in new window

Thank you for the input - especially PatHartman and Nick67.  I hope I can work on this tonight, but I might not be able to until Saturday.

-- Tim

It took me longer than I thought to get back to this, but thank you for your patience.

I started with the sample code Nick67 posted.  I haven't done this in about 7 years, so it took awhile to "scrub some rust off".  The best I could come up with using that approach is this (immediately below), but I still wasn't doing something right because it would only search the current record.  However, the story continues below this code.

===== First attempt at new code.  Would only search one record. =====

Private Sub btnFindRec_Click()
    Dim strSearch As String
    Dim intWhere As Integer
    Dim ctlPrevious As Control

On Error GoTo Err_btnFindRec_Click

    ' Get search string from user.
    Set ctlPrevious = Screen.PreviousControl
    With ctlPrevious
        strSearch = InputBox("Enter text to find:")
        ' Find string in text.
        intWhere = InStr(1, .Value, strSearch, vbDatabaseCompare)
        If intWhere Then
            ' If found.
            .SetFocus
            .SelStart = intWhere - 1
            .SelLength = Len(strSearch)
        Else
            ' Notify user.
            MsgBox "String not found."
        End If
    End With
GoTo Exit_btnFindRec_Click

Exit_btnFindRec_Click:
    Exit Sub

Err_btnFindRec_Click:
    MsgBox Err.Description
    Resume Exit_btnFindRec_Click
   
End Sub


While searching for a solution, I stubled across the "FindRecord" and "FindNext" methods.  (I wish I had found them when I first created this button using Access97.)  Once I got the bugs worked out, those helped a great deal.  They appear to not only solve my problem of the unintended NumLock Key's status changes, but they let me search an entire record (not just one field) as well.

Before I added the second MsgBox for "DoCmd.FindNext" the found text was highlighted on the current record -- but it didn't give me the option to search for the "next record" the way my original code did.  With the second ("FindNext") MsgBox, the focus left the main form and I couldn't easily see the FOUND text on the current record.  So I hit on the solution of using the "RGB" function to temporarily change the "BackColor" property of the textbox that is the active control.

I also had to modify the error-trapping, because InStr() would fail if no record was found.  

The only thing I haven't solved yet is that I often have multiple "finds" on one record.  I won't get into the reasons for the sometimes redundant info.  Access2003's Help screen on "DoCmd.FindRecord" says that final "false" parameter makes the search start with record AFTER the current one, which would avoid the multiple hits.  Unfortunately, it really starts with the next occurence of the search string, even if it's in the SAME FIELD (a memo field for notes) in the CURRENT record.  If someone has a solution for that, I'd like to know about it.

Thanks again for all your help.  My current code is below.

-- Tim
----------------------------------------------------------------


Private Sub btnFindRec_Click()
    Dim strSearch As String
    Dim intWhere As Integer, intYesNo As Integer
    Dim ctlCurrentControl As Control
    Dim lngYellow As Long, lngWhite As Long

On Error GoTo Err_btnFindRec_Click

' New_FindRecord_code:
' Searches all fields in all records
' Because of "Find Next" message box, found text in current record is not highlighted
' So additional code temporarily turns the active field yellow

    lngYellow = RGB(255, 255, 0)
    lngWhite = RGB(255, 255, 255)

    ' Get search string from user.
    strSearch = InputBox("Enter text to find:")
    DoCmd.FindRecord strSearch, acAnywhere, , , True, acAll, False
SearchLoop:
    ' determine whether search string was found
    Set ctlCurrentControl = Screen.ActiveControl
    With ctlCurrentControl
        On Error Resume Next
        intWhere = InStr(1, .Value, strSearch, vbDatabaseCompare)
        On Error GoTo Err_btnFindRec_Click
    End With
    If intWhere Then
        With ctlCurrentControl
            .BackColor = lngYellow
            ' If found, do you want to search for another record?  6=Yes
            intYesNo = MsgBox("Keep searching for: " & strSearch, vbYesNo)
            .BackColor = lngWhite
        End With
        If intYesNo = 6 Then
            DoCmd.FindNext
            GoTo SearchLoop
        End If
    Else
        ' Notify user that search was not successful.
        MsgBox "String not found: " & strSearch
    End If
GoTo Exit_btnFindRec_Click

Exit_btnFindRec_Click:
    Exit Sub

Err_btnFindRec_Click:
    MsgBox Err.Description
    Resume Exit_btnFindRec_Click
   
End Sub

Seems like a lot of work to go through just to "Search"
Why not build a simple filtering system...?

As a user, I would be pissed of if an application was to do that in my environment, because it overrides the default the preference that I have personally set in Access options for the remainder of the application run.

From the user point of view, who is used to control his standard Find dialog, he will probably not notice that its settings have been changed. He will think that the search is inconsistent.

I feel that any application that changes the options without asking the user if we want to do so is a bad application.

Just FYI, using queries is a better way to "filter" records.  The method you are using is only viable if you never upsize to SQL Server or other RDBMS.

One question to ask.

Is this really necessary, since the user can decide by himself through the standard Find dialog.

The user should always come first. He is the one who will use the application after all. Who are we, as programmers, to decide what is best for the user, specially in the context of a search in a database?

Code should be a service for the user, not for the programmer.

Thank you all for your comments.

The first issue I should address is that I myself am the primary (generally the only) user.  I created this Address Book database for my own home use probably a decade ago.  (My Access development work was mainly 2002-2007.)  Besides, CTRL-F, the FIND icon, and the Access menu are all still there.  This button is an addition to all that, not a replacement.

Originally, I just used Access's built-in system to find records, but eventually built my own button because I wanted to always match the search string to "any part" of the field and I thought that button would make it easier for my wife to use the database.  (Wishful thinking.  She's never been interested.)

As to database size: After a decade of Access and a decade of something else before that, I've currently got 733 records.  I don't know if it'll ever hit 1000, but that barrier is good to know about.

A modal popup form sounds better than what I did.  That will require some more learning (and rust-scraping) and a few more days, but I'd rather use a popup like CTRL-F uses, but which uses my preferred defaults.  

Thanks again.  I'll keep working.

-- Tim

always match the search string to "any part" of the field can be set as the default in Access options. You do not need to program to get it as the default.

What is it about the 1000 records barrier? You can never set a limit like that on any database. There are limits, such as 2Gb file sizes and 255 tables. But not on the number of records.

I have had tables with 60000 records, each with 30-40 fields, in a database that topped the maximum number of tables, and except for a slight delay when displaying a form that loads them all along with links with 10 other tables, there is not problem with that. But in my work I have seen tables with only 500 records or so that were crawling like an earthworm.

What determines the limit is more the design of the tables and the type of work you do with them. Working with text instead of numbers usually brings down a database faster. Having too many memo or OLE object fields can also do it. Useless indexes takes up resources, but if you do not have indexes where they are necessary, then you slow down things. Bad coding is also a big factor. These are the type of stuff that determines the limits, not the number of records.

@Jacques
Have you never notice that DoCmd.FindFirst performance degrades as the number of records grows?

Nick67

Yes it does, the same way a query will take longer to execute as the number of records grows. This specially true if you launch the search with "any part" or on memo fields. In such a case, the system cannot use the indexes and has to loop through all the records one by one until it finds the one you are looking for.

This is why, in my last post, I insisted on the fact that a proper design and use of indexes is important as far as performance in concerned.

the same way a query will take longer to execute as the number of records grows
Not at all!
A CTRL-F with the ALL for direction will degrade in performance more than arithmetically with the number of records.
Even if you are doing a find on an autonumber primary key.
Try the experiment
Build a table with an autonumber PK and an long field
Bind a form to it.
Add code to insert records to the table.
Measure the time it takes to find a record via a criteria on the primary key on the form with
DoCmd.FindRecord strSearch, acAnywhere, , , True, acAll, False
versus
Dim db As Database
Dim ThePK As Long
Dim rst As DAO.Recordset
Dim strCriteria As String

strCriteria = InputBox("Enter the thing you're looking for")
If strCriteria = "" Then
    Exit Sub
Else
    strCriteria = "[SomeField] =  " & CLng(strCriteria)
End if

'finding no match takes forever so
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT ThePrimary Key from TheTable where " & strCriteria & ";", dbOpenDynaset, dbSeeChanges)
If rst.RecordCount = 0 Then
    MsgBox "No such thing found.", vbInformation
Else
    ThePK = rst!ThePrimaryKey
    rst.Close
    strCriteria = "ThePrimaryKey = " & ThePK
    Set rst = Me.RecordsetClone
    rst.MoveLast 'I start looking from the end
    rst.FindLast strCriteria
    Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing

The second code block can be orders of magnitude faster than the first as the number of records increases past 50K.
And that's on numeric index fields.
DoCmd.FindRecord strSearch, acAnywhere, , , True, acAll, False
isn't something you should plan to use in production on a form where a significant number of records will eventually exist.

That's my experience.
Nick67

I did not say that a query would be "as slow", I simply said that queries also slow down when the number of records grows. It is a basic fact of databases.

And your comparison is not valid. You specified acAnywhere in your call to DoCmd, which means that the index is not used and the match must be done against subsets, two situations that do not occur in your code that goes straight for the whole value.

And why to do convert strCriteria to a Long in the following : strCriteria = "[SomeField] =  " & CLng(strCriteria)

The system will need to convert the result back to a String in order to perform the concatenation.

Sigh.
My only point is that it very common for a newcomer to see
DoCmd.FindRecord strSearch, acAnywhere, , , True, acAll, False
as a godsend.

Until it's not.
You specified acAnywhere in your call to DoCmd, which means that the index is not used and the match must be done against subsets, two situations that do not occur in your code that goes straight for the whole value.
I have no idea about the underpinnings of the DoCmd call, nor did I really care after it began to perform like a dog.
I replaced it.  And that's the point.  Somewhere along the line
DoCmd.FindRecord strSearch, acAnywhere, , , True, acAll, False
is going to be thrown in the trash bin for something more efficient.

In the background, DoCmd.FindRecord probably builds a SQL Statement according to the instructions given by the user, calling the same functionality as the Find dialog.

When you call it with acAnywhere, it probably builds a query with a LIKE *criteria* instruction in it, which tremendously slows down things.

Nope.
expression.FindRecord(FindWhat, Match, MatchCase, Search, SearchAsFormatted, OnlyCurrentField, FindFirst)
What kills performance is in bold

Search   Optional AcSearchDirection.
AcSearchDirection can be one of these AcSearchDirection constants.
acDown
acSearchAll default
acUp
If you leave this argument blank, the default constant (acSearchAll) is assumed.

It can perform quite well if you choose acUp or acDown
it is acSearchAll, which is the default, that performs like a dog.
Why?  I don't know!
And that same thing bleeds over into CTRL-F
Leave it at the default 'ALL' and you can go for coffee if there are a lot of records.
Choose Up and guess wrong, and then choose Down and get a result and you are still spending only a quarter of the time of acSearchAll
You'd think they'd be equivalent.
They aren't.

I had hoped to work on this over the weekend, but I've been in bed sick instead.  Will get back to it as soon as possible.
--Tim

I'm sorry.  Before I got better, my computer died.  It's now back running, but I'm not where I can work on this again yet.  Meanwhile, I've probably let this languish too long.  So I'm going to accept Nick67's "2015-01-26 at 20:21:18  ID: 40571778" comment as the solution.  When I do get to work on this again, that's where I'll be starting.

Thank you for all your help.