Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    10

    Unanswered: Help with code to search for text string please.

    I have an access database in which one form is based on tblCustomer and shows Customer Name and Address details. To find any required record the user can input the required name into a text box, click a command button and using the code shown below, find the required record.

    DoCmd.FindRecord txtFindName, , , , , , False

    Other code allows for multiple finds and fwd and back buttons allow for these to be viewed. This all works ok.

    However, this method requires the complete Name to be inputted. I want to change this so the user can input, say just the first few letters of the name, click search and the code will select all records with a name starting with those letters. I have tried adding acStart to the code below as a parameter but with no success. I know I can do this with a parameter query but I want to use vba as a development to achieve this. I would be grateful to know how to go about making this change to the search process e.g. can I simply change the DoCmd line with another parameter? With thanks for any help you can give me.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    FindRecord will only locate one record. If you want to display a list of possible matches, you'll need to use something else to display the records. A subform or a listbox perhaps?

    Also, familiarize yourself with wildcard characters for access. Eg "*teststring*" will find any string that contains "teststring" followed by or preceeded by anything.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2004
    Posts
    10

    Help with code to search for text string - thanks

    Thanks for your help Teddy. I have dealt with the fact that FindRecord will only find one record by using it in a Do Loop Until. I count the total number of finds and then have some code with back and forward buttons to go back to review all the finds. This works for me since I will only have 3 or 4 found records to review. What I havn't been able to do is to get wildcard characters to work in the textbox I use (txtFindName) for the search argument in conjuction with the FindRecord command. However, I also want to make the search work after inputting just a few characters i.e. if I input smi and click search I will identify all names beginning smi like smith and smithers. I will go and have another look at wildcards again now and have another go. Again thanks for your input.

  4. #4
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    13
    Hey Criterium,

    Here's an example of using wildcards in a string. Thought it may help if you could see one way to use them. Using your example, this would find 'smi' as any where in the string.

    stNoBusiness = "[Address] Like '*" & Me.txtFilter.Value & "*'"

    HTH,
    Shane

  5. #5
    Join Date
    Oct 2004
    Posts
    10

    Help with code to search for text string - thanks

    Hi Shane

    Thank you for your input which I have tried to use in this prob. After trying for ages to fix this prob. I discovered that my search code was working ok even when I used wildcards, but the error coding seems to be the prob. The section of the code is shown below. When ever I use a wildcard in txtFindName this section of the code never recognises it and it throws up a Failed to find Msg. when it should not.

    If I REM out this part of the code everything else works with or without the use of wildcards and I can find any record.

    I would be very grateful for any help to show me how I can fix the CustomerSurname <> txtFindName line in here.

    'no matching records
    If CustomerSurname <> txtFindName Then
    MsgBox "Failed to find any matching records.", _
    0, "Search Failed"
    txtFindName.SetFocus
    Exit Sub
    End If

    with thanks

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try using "LIKE" and "NOT LIKE" instead of direct equivilency operators.

    "test" LIKE "tes*t" : true
    "test" = "tes*t" : false
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2004
    Posts
    10

    Thanks Teddy

    Hi Teddy

    Thank you very much. Why didn't I think of that, like 3 days ago. I used:

    If Not CustomerSurname Like txtFindName Then

    and evrything works just as it shoud, wildcards as well. However I've learned some useful information in the process. Your help is very much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •