Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

MS Access 2003 custom find problem

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

  • 4
  • 3
1 Solution
Dale FyeCommented:
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

Dale FyeCommented:
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.
pythondesignAuthor Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Dale FyeCommented:
Looks like that does it.
pythondesignAuthor Commented:
you are the man.
thank you
pythondesignAuthor Commented:
perfect solution.
proves this site is indispensible
Dale FyeCommented:
Glad I could help

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now