By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,223 Members | 1,371 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,223 IT Pros & Developers. It's quick & easy.

Filter by Form with Combo Boxes (A2003)

P: n/a
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

Feb 2 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.