asked on

Filter data on an access form

I have an Access form that has a combo box (cboField) and a text box (txtField ) the combo box uses a "Value List" to populate from the table fields and the text box the user enters the search criteria with and uses LIKE to allow for partial searches. Hitting enter runs the the search, deleting data values in the text box resets the records to unfiltered. The search works fine except that when the records are filtered I can't edit the filtered records. Below is the code running on both the text box and combo box.

Private Sub cboField_Enter()
Dim oRS As DAO.Recordset, i As Integer
If Me.Form.FilterOn = True Then DoCmd.ShowAllRecords
Set oRS = Me.RecordsetClone
cboField.RowSourceType = "Value List"
cboField.RowSource = ""
For i = 0 To oRS.Fields.Count - 1 'OR 1 To Count
 If oRS.Fields(i).Type = dbText Then cboField.AddItem oRS.Fields(i).Name
 Next i
End Sub

Open in new window

Private Sub txtFilter_Exit(Cancel As Integer)
 Dim sFilter As String, oRS As DAO.Recordset
 If IsNull(cboField) Then
 DoCmd.ShowAllRecords
 MsgBox "No Search Item Selected"
 Exit Sub
End If
If IsNull(txtFilter) Then DoCmd.ShowAllRecords
sFilter = cboField & " LIKE '" & txtFilter & "*'"
DoCmd.ApplyFilter , sFilter
Set oRS = Me.RecordsetClone
 If oRS.RecordCount = 0 Then
 MsgBox "No Matches Found"
 DoCmd.ShowAllRecords
End If

Open in new window

Dale Fye

Normally, simply applying a filter or clearing the filter will not prevent a record from being updated.

1. Why would you not use a query as the RowSource for the combo box?

2. Why are you using Docmd.ShowAllRecords and and DoCmd.ApplyFilter rather than using:

me.filter = strFilter
me.FilterOn = (Len(strFilter) > 0)

HTH
Dale

Fred Webb

ASKER

Dale,
Thats my problem it should not stop me from editing, I will implement your suggestions.

Dale Fye

I cannot see anything in that code which would prevent you from being able to edit the records before or after you filter or unfilter the recordset. So if that is changing (you can edit it when unfiltered but not when filtered), there is a high probability that there is some other code associated with the form (Form_Current, Form_ApplyFilter, ...) which is either changing the AllowEdits property of the form or changing the Locked property of the controls on the form.

Dale

Fred Webb

ASKER

Dale,
I changed the filter code as you suggested, the issue appears to be when a value is entered into the txtField control the focus stays on that control until it is cleared than the form is able to be edited. I got this code from an old database that I had from a training class years ago. below is how i implemented your suggestion. Basically all I want to do is have a combo box with the the column to search on and a text box to enter the search criteria in the header of the form.

Private Sub txtFilter_Exit(Cancel As Integer)
 Dim sFilter As String, oRS As DAO.Recordset
 If IsNull(cboField) Then
 DoCmd.ShowAllRecords
 MsgBox "No Search Item Selected"
 Exit Sub
End If
If IsNull(txtFilter) Then DoCmd.ShowAllRecords
sFilter = cboField & " LIKE '" & txtFilter & "*'"
Me.Filter = sFilter
Me.FilterOn = (Len(sFilter) > 0)
Set oRS = Me.RecordsetClone
 If oRS.RecordCount = 0 Then
 MsgBox "No Matches Found"
 'DoCmd.ShowAllRecords
End If

End Sub

Open in new window

Dale Fye

OK, here is what I would do:

1. Create a new procedure in your forms code module:

Private Sub FilterMe

    If (me.txtFilter & "" = "") OR (me.cboField & "" = "") then 
        me.filter = ""
    Else
        me.Filter = "[" & me.cboField & "] LIKE '" & me.txtFilter & "*'"
    end if
    me.filteron = (Len(me.Filter) > 0)

End Sub

Open in new window

2. Add a command button (cmdFilter) and set the Click event to:

Private Sub cmdFilter_Click
    Call FilterMe
End Sub

Open in new window

3. If you don't want the Filter button, then you could use the Exit or AfterUpdate event of the textbox and the combo box to call the filter:

Private Sub txtFilter_Exit(Cancel as Integer)
    Call FilterMe
End Sub
Private Sub cboField_Exit(Cancel as Integer)
    Call FilterMe
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Fred Webb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

ASKER

Any Suggestions on my Filter clearing issue

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

ASKER

Dale,
I tried that and it still remembers the previous filter

Fred Webb

ASKER

It is what worked