Solved

Code behind "Find Record" button turns off NumLock

Posted on 2015-01-10
24
323 Views
Last Modified: 2016-02-14
I have an Address Book database that I built many years ago in Access (part of MS Office 97) running on a Windows XP computer.  For the last couple of years, I've been running Office 2003 on a Windows 7 computer.  After the changeover I noticed that my NumLock key (which I always keep ON) was frequently being turned off.  Eventually I realized that the culprit is a particular button in my Address Book database.

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).  Here is the button's code, which is triggered by the button's "On Click" event:

Private Sub btnFindRec_Click()
On Error GoTo Err_btnFindRec_Click

    Screen.PreviousControl.SetFocus
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
    'SendKeys "%EF"   'calls EDIT menu, then FIND
    SendKeys "%HA"   'goes to MATCH field, then changes it to ANY PART OF FIELD
    SendKeys "%N"    'goes to FIND WHAT field
    DoCmd.RunCommand acCmdFind

Exit_btnFindRec_Click:
    Exit Sub

Err_btnFindRec_Click:
    MsgBox Err.Description
    Resume Exit_btnFindRec_Click
   
End Sub

The problem is that the moment I hit that button, "NumLock" is turned off.  This did not happen when I was running Access97 on WinXP.  Can someone tell me what is causing it under Access2003 & Win7?  And how to fix it?

Thanks,
Tim Deaton
0
Comment
Question by:TimDeaton
  • 7
  • 6
  • 5
  • +3
24 Comments
 
LVL 40
ID: 40543005
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40543150
You may have run into this reported bug by Microsoft.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40543318
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40543323
@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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40544422
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40544791
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

Open in new window

0
 

Author Comment

by:TimDeaton
ID: 40544962
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
0
 

Author Comment

by:TimDeaton
ID: 40570472
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40571528
Seems like a lot of work to go through just to "Search"
Why not build a simple filtering system...?
0
 
LVL 40
ID: 40571579
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40571669
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.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40571778
Lots of comments, but nothing to really point you in the right direction.
What you had before was a manipulation of the built-in Find dialog.
But SendKeys are an evil these days.
Where I went astray was with
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).
That to me read as if you were looking for text within a memo field on one record.

In modernizing, you have discovered FindFirst and FindRecord.
Which are both quite ok -- until you get past a thousand records or so.
Then they perform like absolute dogs.
Incidentally (and perhaps not co-incidentally), so does the Find dialog.
If I CTRL-F, I have to remember to choose a direction, either Up or Down and not All.
Otherwise, that's a few minutes of my life I won't get back :(

DoCmd.FindRecord strSearch, acAnywhere, , , True, acAll, False
That's an interesting beast -- not an efficient one-- but interesting.
It'll look in every control and then in every record bound to a form for a particular string.
But you have it married together with an InStr() loop to try to deal with multiple hits in a memo field.

Why not build a simple filtering system...?
Or better yet, a popup modal form, although this is very complex.
It's likely to "scrub some rust off", too, but it's a fair bit of work.
You'd have a textbox for the search string, and a command button to begin looking.
The search would start by cloning the recordset of the form.
It'd then filter each textfield successively with the desired search key and throw the Primary Key and field name into a table of temporary data with no duplicates
The form would then have Find Next and Find Previous buttons that would move the form to the desired record, set the focus on the desired field and use the code I first posted to highlight that text.

This is the guts of the kind of code that takes the place of DoCmd.FindFirst and its analogs when they get dog-slow
Dim db As Database
Dim ThePK As Long
Dim rst As DAO.Recordset
Dim strCriteria As String

strCriteria = "[SomeField] like '*" & InputBox("Enter the thing you're looking for") & "*'"
If strCriteria = "[SomeField] like '**'" Then Exit Sub

'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

Open in new window


You'd be doing that not on Me but on the form that called the modal popup.
The thing that's left is building the temporary table data through the use of filters.

@TimDeaton Does this sound way above your comfort level?
And @Experts -- how would you guys go after creating an emulation of the CTRL-F dialog that needs no SendKeys?
Does what I've outline sound good?

Nick67
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 40
ID: 40571803
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.
0
 

Author Comment

by:TimDeaton
ID: 40572089
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
0
 
LVL 40
ID: 40572254
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40573052
@Jacques
Have you never notice that DoCmd.FindFirst performance degrades as the number of records grows?

Nick67
0
 
LVL 40
ID: 40573088
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40573275
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
0
 
LVL 40
ID: 40573924
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40573942
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.
0
 
LVL 40
ID: 40573973
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40574165
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.
0
 

Author Comment

by:TimDeaton
ID: 40584044
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
0
 

Author Comment

by:TimDeaton
ID: 40643780
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now