Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    28

    Unanswered: Search table through form inputs

    Ok i have a Customer table with all relevant customer information. I have a form with two text boxes: txtFirstName, txtLastName. I want to type in the first and last name of a customer and then click a Submit button. When you click the button i want it to search the Customer table for existing customers in the database. After the search it displays possible matches and then lets me select a customer in those matches. If the possible matches are not the customer i want then to add the customer. By clicking an Add Buttton. I know how to do that.

    Sorry if its confusing.
    Thanks,
    John

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    dim strSQL as string

    strSQL = "SELECT * FROM myTable WHERE ((1=1) " 'thanks rudy
    if len(nz(txtFirstName, "")) > 0 then strSQL = strSQL & " AND (firstNameField LIKE '*" & txtFirstName & "*')"
    if len(nz(txtLastName , "")) > 0 then strSQL = strSQL & " AND (lastNameField LIKE '*" & txtLastName & "*')"
    strSQL = strSQL & ") ORDER BY lastNameField, firstNamefield;"
    'don't forget those three spaces
    me.recordsource = strSQL

    izy
    Last edited by izyrider; 11-15-04 at 08:10. Reason: missing )
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Posts
    28
    thanks for the response. I tried running it and i get an error. "run time error 2580..... specified on this form or report does not exist. In the debbuger it higlights the Me.RecordSource = strSQL part of my code. Sorry i maybe should've been more specific in the first post. After you click submit button that form closes automatically and a new form pops up with all fields in the table Customers. All text boxes on this form will be filled in by the results of the search. If the search does not return the correct customer you click the "add" button which clears out all the text boxes. i then type the new customer information and input that in the table via a button (i can do that).
    The important thing is even if say i am search for a customer "dan moretti" and the closes match is "sally newman" even though no customer in the table is near 'dan moretti' it still shows the closest possibly match no matter how far off.

    My code looks like this:

    Private Sub cmdSubmit_Click()
    Dim strSQL As String

    strSQL = "SELECT * FROM Custmers WHERE ((1=1) " 'thanks rudy
    If Len(Nz(txtFirstName, "")) > 0 Then strSQL = strSQL & " AND (firstNameField LIKE '*" & txtFirstName & "*')"
    If Len(Nz(txtLastName, "")) > 0 Then strSQL = strSQL & " AND (lastNameField LIKE '*" & txtLastName & "*')"
    strSQL = strSQL & ") ORDER BY lastNameField, firstNamefield;"
    'don't forget those three spaces
    Me.RecordSource = strSQL
    End Sub


    Thanks so much, im a super-noob. Thanks again

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry - real life got in the way.

    ok - the SQL should work, but you want the SQL to feed some other form.
    Forms!thisForm.recordsource = strSQL
    should do it.

    meanwhile, bad news: a search for "Elvis" is not going to return "Sally" as the closest match!

    in my strSQL, any case-insensitive contiguous substring (e.g. "l", "Ll", AlL", "sAl", "y") will return "Sally", but you can't seriously expect "Sally" as being any sort of match for "Elvis".

    ??? soundex ...it adds complication and i don't have an algorithm, but it would be fun to make for access ...maybe someone already did it ???

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2003
    Posts
    268

    Even bigger issues

    This brings to the forefront some even bigger issues.

    McDonald vs MacDonald vs Mc Donald
    Matt vs Matthew vs Mathew

    there are so many different options especially when capturing customer data.

    Ideally, some type of customer identifier other than name would be ideal. however, this is much easier said than done.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    http://www.ftponline.com/Archives/pr...600/gs0600.asp
    is Stan Schultes work on soundex for VB.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2004
    Posts
    28
    thanks for all the help. i still cant get it to work.

    izyrider: where does " Forms!thisForm.recordsource = strSQL " go? Does it go on the form wher i enter the first and last name or do i put that on the new form the pops up with the search. what would that code look like. ive attached my project. Its one of many drafts, haha so most of the other functions dont work. I do have a log in for that you should use first as it records who logs in. just use "1" as the employee DI and "test1" as the password. Thanks for all the help.
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Posts
    28
    Well i finally got it working, at least for the most part. heres the code i used:

    Private Sub cmdSubmit_Click()


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Customer Enter" 'form to open

    stLinkCriteria = "[ContactLastName] LIKE " & "'*" & [txtLastName] & "*'" 'contactlastname is Column in Customer table that txtLastName is searching against

    DoCmd.OpenForm stDocName, , , stLinkCriteria


    With Me
    .OrderByOn = True
    .OrderBy = "ContactLastName"
    End With

    End Sub


    This works fine because its only searching the last name. Right now for example lets say i enter 'john smith' it opens the new form and goes to the first 'smith' based on CustomerID. How would i make it so it still lists all the 'smith's' but the first record shown is 'john smith' if thats not a match i can manually scroll through all the 'smiths' to see if they are an existing customer. I know it sounds wierd but i just need this to finish a project for class. So it doesnt have to be the most practical, it just needs to work. Also how would i do a record count of how many 'smith's' were found. Either by alerting user through a msg box. Thanks.

Posting Permissions

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