Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Thanked 0 Times in 0 Posts
    Hi I have a form called Customers that can be accessed from another form called Events. It might show an Event with the customers who are attached to that Event underneath. The user can then double click on the Customer to go directly to the Customers form. This all works great. The problem is that once in the Customers form sometimes the user forgets that the form is filtered and it shows the one filtered record and not all 61 existing customers. I have a filter or search box at the top of the Customers form that the user can click in and filter out a customer that way too. Ok, here is the problem, the user then clicks the search drop down and tries to filter again. I have tried to program in the "on click" event in the search drop down. I have tried, FilterOn = False or ShowAllRecords. This will not work to turn the Toggle Filter button off and show all the records. I hope this makes sense.

    Thank you,


  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts
    A couple of questions: What version of Access are you working with? Is the Customer form modal so the user has to close the form in order to get back to the Events form? If that is the case, then you should be able to clear the filter on the Events form and turn the filter off on the OnClose event for the Customer form. Otherwise, you should be able to the OnChange event on the Filter control - I presume that is a combo box. Is that true?

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts
    The scenario you describe is very familiar to me. You have a (single record) form with a "find record" combo box at the top. But sometimes you open the form with a filter in place so the 'find record' combo does not work anymore. I seem to encounter the same issue in every database, and I have a range of solutions I play around with.
    • Hide or disable the combobox if there is only 1 record in the form's recordset.
    Private Sub Form_Load()
     If Me.RecordsetClone.RecordCount < 2 Then
     Me.ComboFindChild.Visible = False
     Me.ComboFindChild.Visible = True
     End If
    End sub
    • Don't Filter the form at all. Instead just perform a Find. So when you move from the Events form to the Customer form, change the code to something like this.
    	Dim stDocName As String
    	Dim strSearchCriteria As String
    	Dim rs As Object
    	Dim frm As Form
    	stDocName = "frmCustomerDetails"
    	strSearchCriteria = "[CustomerID]=" & Me.CustomerID 
    	DoCmd.OpenForm stDocName
    	Set frm = Forms(stDocName)
    	Set rs = frm.RecordsetClone
    	rs.FindFirst strSearchCriteria
    	If Not rs.EOF Then frm.Bookmark = rs.Bookmark
    	Set frm = Nothing
    	Set rs = Nothing

Posting Permissions

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