Maybe I should approach, showing this from another VBA perspective? Could I change the open form options and use a Filter and then turn that filter on or off?
At the risk of muddying the waters at an advantage stage of the discussion, I need to comment on the fundamental design approach itself.
What you are doing is binding a table (or query based only on that table) to the form and loading the entire recordset from that table whenever the form opens. After it's open, you intend to apply filters to dump all but one record from the current state of that recordset. Then, remove the filter and reload all of the records again, only to apply a different filter.
Lots and lots and lots of records coming and going in that approach.
In fact, Access is pretty good at it and as long as your tables have few records, it's not a problem per se. You don't see the I/O cost of reading and dumping records over and over.
That said, there's a better way, especially as the number of records involved grows over time. And if you ever move to a remotely hosted server based database such as SQL Server, you will be hit with a huge performance penalty.
It's more efficient and less costly to load ONE RECORD AT A TIME unless you REALLY, REALLY have to be able to scroll dozens, or hundreds, or thousands of records in the form. Not often is that practical, especially when recordsets grow over a couple dozen records at a time. People don't need or want that ability.
The method I recommend (and I think most others would, but we'll see....) is to combine a combo box or list box on the form, in the form's header section along with a query as the form's recordsource with a WHERE clause to limit the records to one.
The combo box has the entire set of records as its rowsource, using two fields usually. Those are the Primary Key for the table and a user-readable field that can identify which record to select.
SELECT PrimaryKey, LastName & ", " & First As SelectableName
FROM tblPerson
ORDER BY LastName & ", " & First
Now, the form's recordsource is something like:
SELECT *
FROM tblPerson
WHERE tblPerson.PrimaryKey = Forms!frmPerson.cboSelectaPerson
That way one and only one record ever gets loaded into the form at a time. It still has the combo box with many records which can be a factor. If so, there are other ways to address that, though.
In order to allow the user to take a coffee break while the form loads a couple thousand people records, you can do it this way.
SELECT PrimaryKey, LastName & ", " & First As SelectableName
FROM tblPerson
UNION
SELECT 0, " <Show Me Everything>" As SelectableName
FROM TblPerson
ORDER BY SelectableName
And the form's recordsource can be:
SELECT *
FROM tblPerson
WHERE tblPerson.PrimaryKey = Iif(Forms!frmPerson.cboSelectaPerson = 0, <>0, Forms!frmPerson.cboSelectaPerson)
The assumption behind this particular syntax is that "PrimaryKey" is an Autonumber and that it will, therefore, never have a value of 0.
