Access SubForm recordsource revert to original

Question

I have an Access form with two subforms, both in continuous mode. Since I cannot have a subform inside a continunous form, I had to do it that way (Datasheet is not an option either)

Either way, when I click on my first subform, I change the other subform record source using some rather simple code :

Public Sub MAJFiltre(intIdMembership As Integer)
   IdMembershipFiltre = intIdMembership

   Me.RecordSource = "SELECT * FROM T_PeriodeMembershipPartipant WHERE IdPeriodeMembreship=" & IdMembershipFiltre
   Me.Requery

End Sub

This function is called from the first subform. I did this for another form and it was working fine. For this one, if I use a breakpoint, I can see the recordsource is changed, but nothing happen in the UI. However, if I put a breakpoint in a BeforeInsert event, I can clearly see the recordssource reverting to the original one (with no WHERE clause)

I also notice something unusual : If I save the form code while debugging, all of a sudden, it works. However, as soon as I close the form, it revert back to its "buggy" version.

Does anyway have an idea of what is going on and how I can correct /prevent it ?

Thanks

John Bingham John Bingham · Accepted Answer · 2016-05-15T23:55:22

It sounds similar to a problem we suffer periodically, which relates to subforms linked to a parent form with link master/child ids: If you've (ie Access has done it for you) unintentionally saved a filter value as a property in one of the subforms, (filter by or in a record source), when your code reassigns the record source, this saved value prevents the new record source from being assigned correctly, and ms access then does what it thinks is best - in this case, reverting to the valid (prior) record source.

Check your subforms for unwanted saved values of data-tab properties.

Access SubForm recordsource revert to original

2 Answers