MS Access FIlter on subform changing values of other fields

Question

I have a datasheet subform with a combobox name Loan_ID_cbo. Whenever I update the filter via the combobox, the subform is updating the Loan ID (primary key) with the selected Laon ID from the subform, thereby changing the data on the table.

I would like to only filter this data and not allow the filter to edit the data on the table. How can I prevent this from happening?

Here is my VBA code for the After_Update event:

Private Sub Loan_ID_cbo_AfterUpdate()

Application.Echo False

Me.Filter = "MyKey = '" & Loan_ID_cbo & "'"
Me.FilterOn = True

If Loan_ID_cbo = "" Then
    Me.Filter = ""
    Me.FilterOn = False
End If

Application.Echo True

End Sub

Thomas G Thomas G · Accepted Answer · 2016-09-23T11:33:06

If I understand correctly, your combobox is in the subform datasheet. Thus the combobox appears on each row.

It does happen probably because your combobox is bound to the field Mykey. So changing the combobox do change the value of Mykey.

You should not make a filtering combobox out of the myKey field in the subform, you should make an unbound combobox in the parent form :

In the subform, delete this combobox, and add a new textbox and bind it to Mykey.

On your mainform, create a new combobox and use this one to filter your subform. So the code would be something like :

Private Sub Loan_ID_cbo_AfterUpdate()

    Application.Echo False

    Me.subformname.Form.Filter = "MyKey = '" & Loan_ID_cbo & "'"
    Me.subformname.Form.FilterOn = True

    If Loan_ID_cbo = "" Then
        Me.subformname.Form.Filter = ""
        Me.subformname.Form.FilterOn = False
    End If

    Application.Echo True

End Sub

MS Access FIlter on subform changing values of other fields

1 Answers