Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I have a search function that works correctly as follows. The "search" text box on the main form (frmClientInfo) finds any occurrence of the string anywhere within a few fields (names, addresses, city etc) via a query, which presents a list of candidate records in a separate "search results" form in datasheet view. On clicking anywhere in a search result record, the main form the client normally works with is presented, showing the full details for just that one record. The client (some of the staff there) say this is confusing and do not want to have to click the "remove filter" button to show all records again in order to continue their work. Is there a way I can change the sample code below so that it still displays the correct record but does not filter the form and all records can still be seen?

    Private Sub TenancyAddress2_Click()
    On Error GoTo Err_cmdShowDetails_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmClientInfo"

    stLinkCriteria = "[ClientID]=" & Me![clientID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_cmdShowDetails_Click:
    Exit Sub

    Err_cmdShowDetails_Click:
    MsgBox Err.Description
    Resume Exit_cmdShowDetails_Click
    End Sub


    Thank you, regards Roger

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    My initial reaction is to change the button to say "New Search" instead of "Remove Filter" but that probably isn't what would really satisfy them. Searching for specific records is always a challenge. A couple of options come to mind, and the right answer is dependent on how many records you are dealing with. One would be to use a combo box as the selection process, but that only works if you want to search on one field, for example LastName or City. If you are searching using multiple fields, a common approach is to create a query based on the parameters they enter, and then display the results in a list box or a continuous form where they can scroll. We also use a treeview as an approach to that sort of thing, but that involves some complex code and ActiveX controls. If I visualize your situation correctly, I would use the OnCurrent event of the continuous form (you can't use a table or a query as they don't have events) and have the display form be a PopUp so it always floats above the continuous form, and change and reapply the filter to display the record they just clicked on. Hope that makes sense - if not, post back with questions and we will try to help.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply, however the current search method is working well, and from what I understand from your reply is very similar to what you outline. I will try to elaborate on what I am trying to achieve. A listing of records meeting the search criteria is displayed in a continuous form, this shows only 6 or so fields, enough for the user to sufficiently identify the record they are looking for. To this point it things work great. Clicking a record then takes you back to the main form, which has the full record details on multiple tabs (and with sub-forms). This main form itself is also good.

    The issue I want to address is that the code implemented on clicking a record in the search results form takes you to the main form, correctly shows the right record, but is in a filtered view with just that single record. I would like to modify the code so the form shows that correct record of course, but not filtering out the other records, since some of the users get confused by there only being the one record. It would appear the following line creates this problem, showing the form with just the single record. I want it to do "go to this record" instead of the current "show me only this record".

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Personally I don't believe it is a problem to click the "remove filter" button on the standard toolbar and educate users to do so, however this is not what (some of) the users want.

    Regards, Roger

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You might look at the code that is generated by the combo box wizard when you choose the option of using it to find a record. That uses a Recordset Clone method, and doesn't actually apply a filter, so you can use the navigation buttons on a form to move around in the records (if your form has navigation controls). You would have to adapt it to your situation where there are multiple criteria, but the concept would still be the same.
    Wendell

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is a solution I have used in a similar situation.

    You use this code to open the form. This filters the form to just the one record.
    Code:
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    An alternative is to open the form without a filter, but just find the record you are interested in

    Code:
    	Dim stDocName As String
    	Dim stSearchCriteria As String
    	Dim rs As Object
    	Dim frm As Form
    	stDocName = "frmClientInfo"
    	stSearchCriteria = "[ClientID]=" & Me![clientID]
     	DoCmd.OpenForm stDocName
    	Set frm = Forms(stDocName)
    	Set rs = frm.RecordsetClone
    	rs.FindFirst stSearchCriteria
    	If Not rs.EOF Then frm.Bookmark = rs.Bookmark
    	Set frm = Nothing
    	Set rs = Nothing
    Regards
    John



  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, thanks very much for the replies, I have got this working nicely from your code.

    Regards
    Roger

Posting Permissions

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