Can this be done?

[Me]

I have a query joining 3 tables say a,b & c
every record in a maps to one or more in b and one or more in c

I have written a query to fetch me all the records in three tables and
the result looks like (I am showing just the key values here)

Values in A Values in B Values in C

1 1 + A 1 + Ca
1 1 + B 1 + Ca
1 1 + A 1 + Cb
1 1 + B 1 + Cb
2 2 + X 2 + Cp
2 2 + x 2 + Cq
2 2 + x 2 + Cr

.....

I would like to design a form that shows each record
in A and corresponding records in B and corresponding records in C

How can I achieve this? Should I use this query or there is another way to
do so. Please note that users will also be doing search on various fields
using
the form.

Thanks for your help in advance,
-Me

 

[Klatuu]

This is a very typical form/subform construct.
You will need a form where the rowsource is table a. On that form, you will
need two subform controls.
One should link to a form with table b as the rowsource and the other with
table cc as its rowsource.

 

[John Vinson]

I would like to design a form that shows each record
in A and corresponding records in B and corresponding records in C

How can I achieve this? Should I use this query or there is another way to
do so.

Since there is (apparently) no relationship between tables B and C,
your best bet would be to use a Form based on A, with two subforms -
one based on B and the other on C.

John W. Vinson[MVP]

 

[Me]

Hi John,

I already have a form setup with table a as main form and b and c as subforms.
But the problem is users cannot do query on forms b and c. Maybe I should
look into it again. I think since table b is linked to another table for
values that may be the reason for it not to retrieve data.

Appreciate your help!

Thank you,
-Me

 

[Me]

Hi Klatuu,

I already have a form setup with table a as main form and b and c as subforms.
But the problem is users cannot do query on forms b and c. Maybe I should
look into it again. I think since table b is linked to another table for
values that may be the reason for it not to retrieve data.

Appreciate your help!

Thank you,
-Me

 

[Me]

Hi Klatuu,

This is what I found, if I have it the way you described, then when I query
something in subform b, then it does filter the results, but it shows blank
records where the data doesn't match subform b search criteria.

Similarly, it leaves blank when the search crietria in form c doesn't match.

Is there any way by which I can restrict it to skip those records when the
search criteria doesn't match?

I mean it shows all the records at all times, just leaves the form (main or
subform)
blank for records for which the search criteria doesn't match.

I would like it to skip those records.

Thanks for your help!

-Me

 

[Me]

Hi John,

This is what I found, if I have it the way you described, then when I query
something in subform b, then it does filter the results, but it shows blank
records where the data doesn't match subform b search criteria.

Similarly, it leaves blank when the search crietria in form c doesn't match.

Is there any way by which I can restrict it to skip those records when the
search criteria doesn't match?

I mean it shows all the records at all times, just leaves the form (main or
subform)
blank for records for which the search criteria doesn't match.

I would like it to skip those records and display only those for which the
search is valid.

Thanks for your help!

-Me

 

[John Vinson]

Hi John,

I already have a form setup with table a as main form and b and c as subforms.
But the problem is users cannot do query on forms b and c. Maybe I should
look into it again. I think since table b is linked to another table for
values that may be the reason for it not to retrieve data.

Well, the first thing to realize is that the Subforms *do not contain
any data*. They're just windows.

If you search on a Subform, you're searching within a subset of the
table's records - those records which match the criteria of the
subform's recordsource (if any), and which match the Master/Child Link
Field.

Are you expecting to have (say) five records visible on the subform,
and to be able to search through all 4128 records in TableB? Well, you
can, but it will take some code and some pretty careful design work!

Please explain what you're trying to accomplish. I'm sure it can be
done, but maybe not the way you're trying to do it!

John W. Vinson[MVP]