Results 1 to 4 of 4
  1. #1
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110

    Find or filter for a string

    When a user wants to start a new order, a form opens that displays our customer table in alphabetical order and the user is prompted to type in any part of the customer’s name. For example, if the customer’s name is “The Smith Company” the user can type “Smith” and the program will find the first occurrence of “Smith”. If the user has located the right customer, he 2-clicks the field to start an order. The problem is that the first occurrence of “Smith” might not be “The Smith Company”. If the user thinks that “The Smith Company” is not in the table, he clicks the “Add New Customer” button and we wind up with duplicate records in the table. Is there a way to filter the form so it only displays the customers with “Smith” in their name? Or is there a better way to search the table? Currently, my code looks like this:

    Private Sub Form_Open(Cancel As Integer)

    TryAgain:
    Dim varCust As String
    varCust = ""



    varCust = InputBox("Company Name:")

    If varCust = "" Then
    MsgBox ("You must type something.")
    GoTo TryAgain
    End If

    DoCmd.FindRecord varCust, acAnywhere, , acSearchAll, , acCurrent

    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    have the continuous form show all records,
    instead of asking, put a text box on the form (txtFind),

    set the afterupdate event:
    Code:
    sub txtFind_Afterupdate()
    If IsNull(txtFind) Then
       Me.FilterOn = False
    Else
       Me.Filter = "[ItemName] ='" & me.txtFind & "'"
       Me.FilterOn = True
    End If
    end sub

  3. #3
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    Thanks for the response RANMAN256. I followed your instructions and the filter works perfectly, but only on the first try. For example, if I filter for "Smith", I get all the "Smiths". Then if I filter for "Jones", I still get all the Smiths even though I turned the filter off and set the textbox to null in between. The only way I can apply a different filter is to close the form and re-open it.My textbox is on the main form and I'm filtering the data in a subform that is in datasheet view. The textbox is named "SearchFor". Here's my code:

    Private Sub SearchFor_AfterUpdate()

    If IsNull(Me!SearchFor) = True Then
    Forms!frmCustomerSelectMain!frmCustomerSelectSub.F orm.FilterOn = False
    Else
    Forms!frmCustomerSelectMain!frmCustomerSelectSub.F orm.Filter = "[Company] like '*' & Forms!frmCustomerSelectMain!SearchFor & '*'"
    Forms!frmCustomerSelectMain!frmCustomerSelectSub.F orm.FilterOn = True
    End If

    End Sub

  4. #4
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    I discovered the problem. I didn't have the quote marks in the right places. Thanks again for your help.

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

Similar Threads

  1. RegExp to Find String at END of a String
    By Micron in forum Programming
    Replies: 3
    Last Post: 01-17-2018, 09:43 PM
  2. Need Help to find SQL connections string
    By datadc in forum Access
    Replies: 4
    Last Post: 01-16-2017, 08:37 PM
  3. find last object by spliting string
    By Mbakker71 in forum Access
    Replies: 2
    Last Post: 01-29-2014, 12:10 PM
  4. find all text string in Criteria at once
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 05-15-2012, 12:12 PM
  5. Find first Capital letter in string
    By Dutch1956 in forum Programming
    Replies: 5
    Last Post: 09-07-2011, 03:14 PM

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