Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2006
    Posts
    5

    Access search

    Hi there.



    could anyone give me a hand with this code:

    Ive created a search box in a form to search a list of contact names for an address book that I am creating.

    The form runs a macro (i think its called) when the search button is pressed (it uses a text box to type in the search beforehand.)

    The code i've used is:

    Code:
    Private Sub findButton_Click()
    On Error GoTo Err_findButton_Click
    
    
        DoCmd.GoToControl "name"
        DoCmd.FindRecord findEmployee
        findEmployee = Null
        
    
    Exit_findButton_Click:
        Exit Sub
    
    Err_findButton_Click:
        MsgBox Err.Description
        Resume Exit_findButton_Click
        
    End Sub
    however, this code will only search the whole of the field that is is assigned to, and I need it to search for any part of the field in question.

    for example, if a contact name only has one name (say Chris) it will pick it up, however, if a contact field has two names in (say Chris Brooke) searching for "chris" will not bring up the Chris Brooke field.

    What do i need to change in my code to let me search this field to search any part of this field?

    many thanks



    Chris.

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Chris,

    Have you tried:

    Code:
    DoCmd.FindRecord "*" & findEmployee & "*"

  3. #3
    Join Date
    Mar 2006
    Posts
    5
    hi,

    thanks for the code,

    i'll try it on monday as the database is on my workstation at work - forget ot email it home!

    Will let you know.

    Chris

  4. #4
    Join Date
    Mar 2006
    Posts
    5
    Hi,

    The code pasted didn't work, it does not run a search, nor does it bring up any errors..


    Any Ideas?

  5. #5
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Sorry, Chris. Try this instead:

    Code:
    DoCmd.FindRecord findEmployee, acAnywhere

  6. #6
    wasim_sono is offline Advanced Beginner
    Windows XP Access 2013 64bit
    Join Date
    May 2005
    Location
    Pakistan
    Posts
    73
    Hi chris

    I want also using this find function in my form. Can u tell me the whole procedure for this b'caz I dont know the abc of this or if u don't mind then email me ur procedure. For time being i'm using Find and replace wizard of ACCESS in table.

    Thanks a lot

    Wasim

  7. #7
    Join Date
    Mar 2006
    Posts
    5
    Quote Originally Posted by matthewspatrick
    Sorry, Chris. Try this instead:

    Code:
    DoCmd.FindRecord findEmployee, acAnywhere

    That worked! Thanks!

    matthewspatrick: the person who i was making the database for needs the search to be able to seach a whole table and not just the "name" field, how could this be accomplised using the code (what do i need to change in order for it to search the whole table the data is heled in (all fields))

    To: wasim_sono

    I used the codes/methods on this page, if you want to do what i did and creat a seach, just use the code in the quote instead of what they instruct you to put)

    Exercise 1
    We are going to write some Visual Basic for Applications (VBA). VBA is a programming language that takes several courses to learn; so, we will not get carried away learning VBA but will build a little to demonstrate how VBA code is behind the operation of all the controls you have placed on your forms.
    You can read Tutorial 11, pp. 554-581 to get an idea of VBA terminology but, again, don't get carried away as this subject is much different than the rest of Access and should be studied on its own.
    We will add some more controls to our Company Employee form so that a user can enter an employee's last name and have Access search for that person's record without having to click through all the records.
    Open the Company Employee form in design view. Open up some space below the tab control pages so you can work on the form's main page, but stay in the Detail section. Add three controls to your form:
    A Label box: type some instructions to the user like, "Type a last name and click the button."
    A Text box: go to the properties and in the Name property type findEmployee.
    A Command Button: as you go through the wizard, choose the action Find Next, have the button say something like "Find Employee," and write findButton for the control name in the last wizard screen.
    Make sure this button works with a hot key like all the others, preferable use the "F".
    To open the VBA editor either: (1)go to the properties for the Find Employee button, click in the On Click property, click the ellipsis button (3 dots) to the right; or (2)click the Code toolbar button while in the form design screen.
    Make sure you work only in the "Private Sub findButton_Click()" subroutine.
    We hope your routine has the commands "Screen.PreviousControl.SetFocus" and "DoCmd.FindNext." Edit these two lines to read instead: DoCmd.GoToControl "LastName" (or whatever you have named your last name field), and DoCmd.FindRecord findEmployee (this is the name of the text box you specified above).
    Let's add another line of code below the two above: findEmployee = Null. This will clear the text box after each search.
    You should now be able to view your form, enter a last name and have Access find the record of the person you chose. Note, however, it will find only the first instance of a last name. We need more complex code to also add a first name.

    From: http://www.rrcc-online.com/~lenm/14501db11.htm
    give us a message if you have a problem contructing this as i will be able to help - to a certain extent!

  8. #8
    wasim_sono is offline Advanced Beginner
    Windows XP Access 2013 64bit
    Join Date
    May 2005
    Location
    Pakistan
    Posts
    73
    chris

    Thanks a lot of u. Its working. But can u tell me that If there are more than two records of same criteria then how it will show all of that records?

    Regards.


    Wasim

  9. #9
    Join Date
    Mar 2006
    Posts
    5
    Quote Originally Posted by wasim_sono
    chris

    Thanks a lot of u. Its working. But can u tell me that If there are more than two records of same criteria then how it will show all of that records?

    Regards.


    Wasim
    What i had to do for this, was to make a find next button in design view of a form, you can do this by using the command button on the toolbar and by using FindRecord in the left hand pane, and Find Next in the right,

    hope this helps!

  10. #10
    pettit is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    2
    I've been trying to mimic a "finder" tool to populate information for a specific review number. When I test the tool it populates the form with information for a different record. I'm missing a step somewhere...please let me know what I am missing

    Macro
    1st line
    Macro Name: Finder
    Action: FindRecord
    Arguments: =[Forms]![denial_review_form]![Finder]

    2nd line
    Action: StopMacro

    Form
    In an unbound text box named "Finder", I included a default value.

    I'm not sure how to connect the macro to the unbound text so that I can type a review number to populate the rest of the form.

    Thank you in advance

  11. #11
    pettit is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    2

    Resolution

    The missing link is that I had to select the "Finder" macro for the "Got on Focus" property in the field that I wanted to reference (in this case, review number). It worked fine after that.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search for multiple records
    By Blake in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:17 AM
  2. Refresh form search text box
    By oxicottin in forum Forms
    Replies: 2
    Last Post: 11-19-2007, 02:28 PM
  3. Search All Field
    By robbiebrown34 in forum Access
    Replies: 0
    Last Post: 05-17-2007, 09:40 AM
  4. Search for record from a form
    By hcoakley in forum Forms
    Replies: 0
    Last Post: 11-26-2006, 11:23 AM
  5. Search and Clear button
    By polk383 in forum Programming
    Replies: 1
    Last Post: 08-30-2006, 08:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums