Results 1 to 7 of 7
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066

    Search Form

    I have an Access 2010 Database. The data is in SQL Server. I've been asked to add a search routine to the main data form.


    I've added a command button to open a form with 2 textboxes for the user to type in what they're searching for.



    Code:
        
    DoCmd.OpenForm "frm_namesearch", acNormal
        Me.Filter = strfind
        Me.FilterOn = True
    strfind is a public string variable in a public module


    There is also a command button for the user to apply the filter.
    This is the code in the search command button
    Code:
        If Not IsNull(Me.txtlast) And IsNull(Me.txtfirst) Then
            strfind = "Lastname Like ""*" & Me.txtlast & "*"""
        Else
            If IsNull(Me.txtlast) And Not IsNull(Me.txtfirst) Then
                strfind = "Firstname Like ""*" & Me.txtfirst & "*"""
            Else
                strfind = "lastname Like ""*" & Me.txtlast & "*"" and firstname like ""*" & Me.txtfirst & "*"""
            End If
        End If
    Now what I'd like to happen is once the user clicks on the command button this form closes and goes back to the original form and the strfind be applied to the form filter.

    I tried adding a me.close to the command button that builds the find string but it doesn't work just gives me an error. Any help is appreciated.

  2. #2
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    wouldn't you be served equally well simply by adding a combo box to select a record by Name?

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Me.close won't work

    How about this?
    DoCmd.Close acForm, Me.Name


    Sent from my iPhone using Tapatalk

  4. #4
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    No the last name and first name are in separate fields. I've solved it but not ideally instead of the form the user is prompted to enter the last name and then again for the first name. It works but it's not ideal.

  5. #5
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    andy49 - thanks that works unfortunately the routine doesn't work as I expected. I wanted the search form to open so the user could enter the last name and first name click on the command button and return to the form to set the filter property and turn it on. That doesn't happen the form opens but the routine that opens the form continues to run so the filter is set before the criteria is entered so it has no effect. I thought opening the form modally would stop the calling form from executing until the new form was closed that isn't the case.

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Why not save the "filter" as a string argument when you close the search form

    Then use the form_onopen event to set the filter

    Think that should work.




    Sent from my iPhone using Tapatalk

  7. #7
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    combo boxes are multi field, and you can sort by field - - so you could add City or Job Title or whatever to help differentiate between identically named people..... selecting a record by name with a combo box has to be by far the most common and easiest approach....

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

Similar Threads

  1. Replies: 3
    Last Post: 09-12-2016, 11:49 AM
  2. Replies: 10
    Last Post: 09-08-2016, 08:09 PM
  3. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  4. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  5. Replies: 1
    Last Post: 04-20-2012, 03:16 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