MS Access 2003 custom find problem

Posted on 2011-10-08
Last Modified: 2012-05-12
Hi folks,
please help a non-programmer who fakes it anyway, and I will reward you generously ;)

I have a client who I've been maintaining an Access database for years to keep track of their inventory of military parts. They find that the built-in search capabilities (the pop-up search and replace) to take up too much screen space, so I looked and found this code to create an embedded search field in the main form interface they use. It works well for them, but they want more, as usual, and being a non-programmer, I'm stumped. Here is the code I have in there now (which I found online) and it is assigned to the search button:


Private Sub cmdSearch_Click()
    Dim strStudentRef As String
    Dim strSearch As String
'Check txtSearch for Null value or Nill Entry first.

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
    Exit Sub
End If
'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID
    DoCmd.GoToControl ("Mfg Part Number")
    DoCmd.FindRecord Me!txtSearch
    strSearch = txtSearch.Text

End Sub

Here's what they want:

the unbound text field (txtSearch) searches a column in the table INVENTORY called "Mfg Part Number"

 - They want to enter a partial number (as opposed to a full, exact number, as it does now) and come up with results, and keep hitting the <enter> key until they find their part.

 - They also want to do away with the search button (cmdSearch) altogether and just enter the number into the field and hit the <enter> key to start the search process.

The second "want" is not as important, since all they need to do it hit <enter> twice, but if you can solve it, all the better.

PS. - the "strStudentID" stuff has nothing to do with what I need. Those are the remnants of the original code I cribbed from. That can be stripped out.

so if you can supply me with the code to overwrite what I have above, and give me simple  instructions (e.g., should i assign it to the search field or button, as it is now)  you have my full gratitude and points.

Let me know any questions and I'll try to answer the best I can

Thanks, all, cheers

Question by:pythondesign
    LVL 47

    Accepted Solution

    I would use the textbox's keydown event. (make sure that you turn on Form Key Preview).

    Private Sub txt_Search_KeyDown(KeyCode As Integer, Shift As Integer)
        Static LastSearch As String
        Static intCount As Integer
        Dim rs As DAO.Recordset
        Dim strCriteria As String
        If KeyCode = vbKeyReturn Then
            KeyCode = 0
            Set rs = Me.RecordsetClone
            strCriteria = "[SomeText] Like " & Chr$(34) & Me.txt_Search.Text & "*" & Chr$(34)
            If Me.txt_Search.Text <> LastSearch Then
                LastSearch = Me.txt_Search.Text
                intCount = 0
                rs.FindFirst strCriteria
                rs.FindNext strCriteria
            End If
            If rs.NoMatch Then
                MsgBox "no " & IIf(intCount > 0, "more ", "") & "matches"
                intCount = intCount + 1
                Me.Bookmark = rs.Bookmark
            End If
            Set rs = Nothing
        End If
    End Sub

    Open in new window

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    To explain.

    I set two static variables as I want these variables to retain their value between subroutine calls.  You could use form level variables, but I prefer to keep them local to the procedure if I can.

    1.  First, check to see if the key that was pressed was the enter key.  If not, ignore

    2.  Next set the rs to the forms recordsetclone.  This is the preferred method of working with recordsets behind a form, as it prevents you from moving off the current record arbitrarily.

    3.  Define the search criteria.  In this case, I'm searching for text in the mfg part number field where the beginning of the field matches the value entered in txt_Search.  You can change that if you want, or add a combo next to txt_Search to select how you want to search (Exact, Begins with, Contains) and adjust the search criteria accordingly.

    4.  If this is the first time "Enter" has been pressed for this particular search string, set the two variables and use the FindFirst method to look for the string.  If not, use the FindNext method.

    5.  Use the recordset.NoMatch property to determine whether the search string was found. If not, display a message.  If so, move to the appropriate record by setting the forms bookmark to that of the recordset.

    6.  Finally, close the recordset.

    Author Comment

    I think it's a winner here, I had to do some minor adjustments, please check them out:

    - only things I had to do was change the field name from my "txtSearch" to your "txt_search"

    - I turned on form key preview, per your instructions

    - changed your "[SomeText]" to "[Mfg Part Number]"

    the thing now appears to work like a charm.
    Let me know if I did the right changes, I will  give you full credit.

    Thank you VERY much
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Looks like that does it.

    Author Comment

    you are the man.
    thank you

    Author Closing Comment

    perfect solution.
    proves this site is indispensible
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Glad I could help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now