This is for informational purposes...I had a problem and I thought it
might help others in a similar situation. I hope someone, someday,
finds this idea useful. I've garnered so much knowledge from this
forum over the years, it's nice to present a solution that I couldn't
find here.
I have a form that my users wanted to filter, using the right-click
Filter By or Filter Excluding, etc.
I have a check box which my reports look up, called Select. In other
words, if the record is selected, they want it on the report.
Once they filtered the report, they wanted to be able to, in effect,
click all of them "Select" without having to scroll through all the
records.
Here was the problem, which was sort of addressed elsewhere in this
forum: If they filtered on a combo box (which references another
table), the "query" that could be created and saved references a
"lookup" table, which would essentially fail if I tried to use an
update query to mark all of the filtered records with Select. It was
suggested to change the recordsource of the form to include all the
lookup tables, which would be a good solution, however, one of my
fields changes controlsources based on what is selected in another
field, so I could not change the recordsource to include all tables.
To illustrate so you'll understand, we deal in office equipment, B&W
Copiers, Color Copiers, etc. from various manufacturers. If the user
selects B&W, the model numbers they get to choose from come from the
B&W table. If they choose Color, the model numbers come from another
table. These tables are obtained from an outside source, and are
updated monthly, so I had to go the route of changing the
controlsource after the user chooses. It works brilliantly.
So, how did I "Select" the filtered records, based on whatever filter
the user selected (which can be any, of course)? It was so simple, I
don't know why I didn't think of it sooner. (I've been working on
this most of the day, trying out various solutions).
Here is the code, which I put under a command button:
Private Sub cmdSelectFiltered_Click()
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
rs.Edit
rs!Select = True
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
