Filter Question

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

Hello,

If you have a form and a sub form linked together with an ID autonumber with
1 to many relationship. If you then search for data in the sub form and lots
of records are found (results may look like in datasheet view of the form)

(in Sub Form)

lngMainID | Name

2 Neil
2 Peter
5 Mike
4 Phil
4 David

How would you code the SQL statement (the WHERE part) to show records 2, 4 &
5 in the main form only?

"SELECT * FROM tblMain WHERE lngID = " Me.lngMainID ' Assumes code is in
SubForm

obviously isn't going to work.....
I know there's a way but can I for the life of me remember what it is.


TIA,

Neil.
 
Not sure if I'm understanding correctly, but is this what you seek:

"WHERE lngID = " & Me.lngMainID
 
Yes, I'm still watching this thread. I'll review your post and then reply
later today.
 
Let me see if I'm understanding your setup and desired actions.

You have a main form that is linked to a subform on that main form.

A user can select one or more "names" on the mainform, and therefore filter
the subform to show only the records with those names.

When the subform filter is set, the user currently can scroll through the
main form's records, which include records that have "names" different from
the ones chosen as the subform's filter. When the main form's record is one
of those that doesn't contain one of the "selected" "names", the subform
displays no records.

You wish for the main form to be filtered by the same filter as the subform.

Am I correct on this?

Now, I admit to a bit of confusion. Why not just filter the mainform for the
"names" in the first place, and then let the subform display the records
that are linked to the filtered records from the main form? I believe that
this is what you seek to accomplish. Your setup of filtering the subform and
then uses that to filter the main form is backwards from the usual setup:
filter the main form and then display the child records for the filtered
records on the subform.

So, before I get into more details, let me know if I'm off track so far.
 
Thanks for your help on this Ken,

You are indeed correct in what I am trying to do - I agree that it is the
backward way of doing of things. The reason why I need to do it is because
the peoples names are kept in the sub form and this way, users can search
through this form for them. The names arn't kept in the main form (in fact
it isn't even names - this was just the best way I could give examples of
what I was doing). The only way how I can filter the main form is by
applying a filter on all the main id numbers that have been returned in the
sub form. If this is not possible I will have to revise the way the database
has been set up.

Neil.
 
Rather than spend a lot of time trying to make this setup work, I recommend
that you switch around the subform and mainform and use that setup. It'll be
much easier to develop and maintain if you work "with" ACCESS and let it do
a lot of the hard work.

If this just isn't the way to go, then I'll need to think a bit about how to
do what you seek.......
 
Back
Top