Results 1 to 3 of 3
2010-01-04, 15:00 #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.
2010-01-04, 15:30 #2
- Join Date
- Aug 2001
- Evergreen, CO, USA
- Thanked 60 Times in 60 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?Wendell
2010-01-04, 17:43 #3
- 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 Else 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 = NothingRegards