I take it you are modifying the RecordSource of a form to load a limited
number of records (perhaps just one), e.g.:
Me.RecordSource = "SELECT * FROM Table1 WHERE ID = 99;"
If you do that while you are already editing/entering another record, Access
first saves that record, and then loads the matching record(s), or gives you
the new record if none match. If you were entering a record that cannot be
saved (e.g. a required field is missing, a validation rule is not met, a
unique index is violated), the attempt to save the record fails, and so the
attempt to set the RecordSource property does not succeed (i.e. you are
still with the not-yet-valid entry.)
For clarity, I would encourage you to explicitly save before setting the
RecordSource, or doing anything else that requires an implicit save, e.g.
closing the form, applying a filter, changing the sort order, requerying,
.... Use:
If Me.Dirty Then Me.Dirty = False
or if you prefer (and the form has focus):
RunCommand acCmdSaveRecord
You may find it easier to set the form's Filter rather than changing the
RecordSource. The Filter is effectively a WHERE clause, so anything you can
do in a WHERE clause can be done in a filter (including subqueries.)
If you do change the entire SQL statement, make sure that you don't drop any
fields that were present previously, introduce fields from a different table
that have the same name, or allow fields to change data type (particularly
with calculated fields.) Some versions of Access can crash if these things
happen.
