VBA and Access Form Filter

Question

I have this form in access, the purpose of it is to work as a front end of a table which can be edited through this form. Initially when it loads I display in the form data from a recordset with the following query:

SELECT * FROM DATA

I want to be able to filter the data on the recordset once the form is open. I tried the following VBA code to accomplish this:

Private Sub Filter_Click()
    If (IsNull(Me.Find_Field) Or Me.Find_Field = "") Then
        rs.Close
        Set rs = db.OpenRecordset("Select * from DATA ORDER BY ID)
        rs.MoveFirst
        LoadData (True)
        Exit Sub
    End If

    Set rs = db.OpenRecordset("Select * from DATA WHERE ID = " & Me.Find_Field)


    rs.MoveFirst
    LoadData (True) ' Function that loads the data into the form
Exit Sub

As you all can see, I reload the recordset with a new filtered query. Up to this point it works, the problems begin when I try to modify a record.

Originally, when the form loads the recordset data, I am able to edit the data and the edited data would show in the table (which is what I want). But after I apply my filter, my code gives me the Run-Time error '3027': Cannot Update. Databse or object is read-only.

I am pretty much using the same code over and over to reload data from the table and it never gave me a problem until I 'overwrote' the source of the recordset. Any idea how can I resolve this issue? Thanks

HansUp · Accepted Answer · 2011-08-11 00:49:16Z

1

I would prefer to use a standard Access bound form because it's simpler than what you appear to be doing.

I can change the form's RecordSource from the click event of my cmdApplyFilter button.

Private Sub cmdApplyFilter_Click()
    Dim strSql As String
    If Len(Me.txtFind_Field & vbNullString) > 0 Then
        strSql = "SELECT * FROM tblFoo WHERE id = " & _
            Me.txtFind_Field & " ORDER BY id;"
        Me.RecordSource = strSql
    End If
End Sub

If you're concerned someone might save the form with the filtered RecordSource, you can make the form always open with the unfiltered version.

Private Sub Form_Open(Cancel As Integer)
    Dim strSql As String
    strSql = "SELECT * FROM tblFoo ORDER BY id;"
    Me.RecordSource = strSql
End Sub

answered Aug 11, 2011 at 0:49

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA and Access Form Filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related