Connection b/t tables for query and form incorrect?

[Karen]

I have three tables whose data flow into a query:

Table 1 - recipient info (recipients are autonumbered - primary key)
Table 2 - personal services agreements (each psa is listed once, but there
may be multiple p.s.a.s for each recipient - which is in a lookup field from
Table 1)
Table 3 - purchase orders (each psa may have multiple purchase orders; the
psa field is a lookup here)

A relationship is created b/t the tables for a query that brings in fields
from all three. I think the relationship among the tables works fine b/c as
long as there is a recipient in table 1 it shows up on the query (even if it
has no p.s.a.s or p.o.s). So if a recipient has two PSAs or two POs the
recipient is listed over and over again.

I created a linked form that brings in the recipient info on the main form.
The psa info is on a subform and then the and po info on another subform.
Here's my problem: the recipient information is listed twice on the main
form b/c it is flowing from the query. Is it possible to have the main form
show the recipient once and the subform show the psas. Then each psa will
show the affiliated pos?

Maybe I need to do something to the query? Maybe I need to change the
relationship of the tables?

 

[Ken Sheridan]

Karen:

From your description the relationships look fine. As far as the form is
concerned a way of reflecting them would be to have a parent form based on
the Recipients table, a subform based on the PSAs and a subform within the
PSA subform based on the PurchaseOrders table. This would mean the PSA
subform would have to be in single form view, though. If you want it in
continuous form view, however, you can do so by having a Recipients parent
form and two 'correlated' subforms within that, one for PSAs one for
PurchaseOrders.

To create correlated subforms you'd do the following:

1. Create the parent form, in single form view, based on Recipients.

2. Create a form based on PSAs, in continuous form view, and use it as the
source object for a subform in the parent form.

3. Create a form based on PurchaseOrders; this can be in either single or
continuous form view as preferred. Use this as the source object for another
subform in the parent Recipients form.

4. Add a text box, txtPSA_Hidden say, in the parent Recipients form and set
its Visible property to False (No in the properties sheet). Set its
ControlSource property to reference the primary key of the PSAs table, so if
this is the PSA field you'd put something like this:

=[sfcPSA].Form![PSA]

where sfcPSA is the name of the subform control, i.e. the control in the
parent from which houses the subform, not the name of its underlying form
object, unless both have the same name of course.

5. Set the LinkMasterFields and LinkChildFields properties of the PSA
subform control to the names of the relevant primary/foreign key columns from
the Recipients and PSA tables, e.g. RecipientID.

6. Set the LinkMasterFields property of the PurchaseOrders subform control
to the name of the hidden text box on the parent form, txtPSA_Hidden. Set
its LinkChildFields property to the name of the corresponding foreign key
column in the PurchaseOrders table, e.g. PSA.

When the parent form is opened at a recipient the PSA subform will show all
the PSDA records for that recipient, and the PurchaseOrders subform the
orders for the currently selected PSA in the subform. As you navigate to
each PSA record in the subform the records in the PurchaseOrders subform will
change to those for the current PSA. For this reason make sure that you show
RecordSelectors in the PSA subform to make it clear which is currently
selected, so the user knows which PSA the orders displayed relate to.

For the RecordSource of the parent form and the two subforms you'll probably
find it better to use sorted queries rather than the table itself so that the
records are shown in a logical order in each case.

Ken Sheridan
Stafford, England

 

[Karen]

Thanks Ken, this works wonderfully except the PO subform. First, in the PO
subform I'm unable to view the POs associated with the particular PSAs. As
well, as I enter new POs in that subform, they disappear from view.

Please help. I would like to be able to have the user click on the
particular PSA to view its associated POs lower on the form but that doesn't
happen. Did I miss something in your directions. Afterall, you say "As you
navigate to each PSA record in the subform the records in the PurchaseOrders
subform will change to those for the current PSA." I have set the Record
Selectors property to yes in the PSA subform.
--
Thanks, Karen

Karen:

From your description the relationships look fine. As far as the form is
concerned a way of reflecting them would be to have a parent form based on
the Recipients table, a subform based on the PSAs and a subform within the
PSA subform based on the PurchaseOrders table. This would mean the PSA
subform would have to be in single form view, though. If you want it in
continuous form view, however, you can do so by having a Recipients parent
form and two 'correlated' subforms within that, one for PSAs one for
PurchaseOrders.

To create correlated subforms you'd do the following:

1. Create the parent form, in single form view, based on Recipients.

2. Create a form based on PSAs, in continuous form view, and use it as the
source object for a subform in the parent form.

3. Create a form based on PurchaseOrders; this can be in either single or
continuous form view as preferred. Use this as the source object for another
subform in the parent Recipients form.

4. Add a text box, txtPSA_Hidden say, in the parent Recipients form and set
its Visible property to False (No in the properties sheet). Set its
ControlSource property to reference the primary key of the PSAs table, so if
this is the PSA field you'd put something like this:

=[sfcPSA].Form![PSA]

where sfcPSA is the name of the subform control, i.e. the control in the
parent from which houses the subform, not the name of its underlying form
object, unless both have the same name of course.

5. Set the LinkMasterFields and LinkChildFields properties of the PSA
subform control to the names of the relevant primary/foreign key columns from
the Recipients and PSA tables, e.g. RecipientID.

6. Set the LinkMasterFields property of the PurchaseOrders subform control
to the name of the hidden text box on the parent form, txtPSA_Hidden. Set
its LinkChildFields property to the name of the corresponding foreign key
column in the PurchaseOrders table, e.g. PSA.

When the parent form is opened at a recipient the PSA subform will show all
the PSDA records for that recipient, and the PurchaseOrders subform the
orders for the currently selected PSA in the subform. As you navigate to
each PSA record in the subform the records in the PurchaseOrders subform will
change to those for the current PSA. For this reason make sure that you show
RecordSelectors in the PSA subform to make it clear which is currently
selected, so the user knows which PSA the orders displayed relate to.

For the RecordSource of the parent form and the two subforms you'll probably
find it better to use sorted queries rather than the table itself so that the
records are shown in a logical order in each case.

Ken Sheridan
Stafford, England

I have three tables whose data flow into a query:

Table 1 - recipient info (recipients are autonumbered - primary key)
Table 2 - personal services agreements (each psa is listed once, but there
may be multiple p.s.a.s for each recipient - which is in a lookup field from
Table 1)
Table 3 - purchase orders (each psa may have multiple purchase orders; the
psa field is a lookup here)

A relationship is created b/t the tables for a query that brings in fields
from all three. I think the relationship among the tables works fine b/c as
long as there is a recipient in table 1 it shows up on the query (even if it
has no p.s.a.s or p.o.s). So if a recipient has two PSAs or two POs the
recipient is listed over and over again.

I created a linked form that brings in the recipient info on the main form.
The psa info is on a subform and then the and po info on another subform.
Here's my problem: the recipient information is listed twice on the main
form b/c it is flowing from the query. Is it possible to have the main form
show the recipient once and the subform show the psas. Then each psa will
show the affiliated pos?

Maybe I need to do something to the query? Maybe I need to change the
relationship of the tables?

 

[Karen]

Also Ken, I'm having trouble w/ what to enter as the txtPSA_Hidden box's
control source (#4 above). Keep getting #NAME? error in that box. FYI - The
primary key of the PSAs tbl is "PSA." The name of the PSA subform is
"frmPSAs Subform." How do I write this exactly?

--
Thanks, Karen

Thanks Ken, this works wonderfully except the PO subform. First, in the PO
subform I'm unable to view the POs associated with the particular PSAs. As
well, as I enter new POs in that subform, they disappear from view.

Please help. I would like to be able to have the user click on the
particular PSA to view its associated POs lower on the form but that doesn't
happen. Did I miss something in your directions. Afterall, you say "As you
navigate to each PSA record in the subform the records in the PurchaseOrders
subform will change to those for the current PSA." I have set the Record
Selectors property to yes in the PSA subform.
--
Thanks, Karen

Karen:

From your description the relationships look fine. As far as the form is
concerned a way of reflecting them would be to have a parent form based on
the Recipients table, a subform based on the PSAs and a subform within the
PSA subform based on the PurchaseOrders table. This would mean the PSA
subform would have to be in single form view, though. If you want it in
continuous form view, however, you can do so by having a Recipients parent
form and two 'correlated' subforms within that, one for PSAs one for
PurchaseOrders.

To create correlated subforms you'd do the following:

1. Create the parent form, in single form view, based on Recipients.

2. Create a form based on PSAs, in continuous form view, and use it as the
source object for a subform in the parent form.

3. Create a form based on PurchaseOrders; this can be in either single or
continuous form view as preferred. Use this as the source object for another
subform in the parent Recipients form.

4. Add a text box, txtPSA_Hidden say, in the parent Recipients form and set
its Visible property to False (No in the properties sheet). Set its
ControlSource property to reference the primary key of the PSAs table, so if
this is the PSA field you'd put something like this:

=[sfcPSA].Form![PSA]

where sfcPSA is the name of the subform control, i.e. the control in the
parent from which houses the subform, not the name of its underlying form
object, unless both have the same name of course.

5. Set the LinkMasterFields and LinkChildFields properties of the PSA
subform control to the names of the relevant primary/foreign key columns from
the Recipients and PSA tables, e.g. RecipientID.

6. Set the LinkMasterFields property of the PurchaseOrders subform control
to the name of the hidden text box on the parent form, txtPSA_Hidden. Set
its LinkChildFields property to the name of the corresponding foreign key
column in the PurchaseOrders table, e.g. PSA.

When the parent form is opened at a recipient the PSA subform will show all
the PSDA records for that recipient, and the PurchaseOrders subform the
orders for the currently selected PSA in the subform. As you navigate to
each PSA record in the subform the records in the PurchaseOrders subform will
change to those for the current PSA. For this reason make sure that you show
RecordSelectors in the PSA subform to make it clear which is currently
selected, so the user knows which PSA the orders displayed relate to.

For the RecordSource of the parent form and the two subforms you'll probably
find it better to use sorted queries rather than the table itself so that the
records are shown in a logical order in each case.

Ken Sheridan
Stafford, England

I have three tables whose data flow into a query:

Table 1 - recipient info (recipients are autonumbered - primary key)
Table 2 - personal services agreements (each psa is listed once, but there
may be multiple p.s.a.s for each recipient - which is in a lookup field from
Table 1)
Table 3 - purchase orders (each psa may have multiple purchase orders; the
psa field is a lookup here)

A relationship is created b/t the tables for a query that brings in fields
from all three. I think the relationship among the tables works fine b/c as
long as there is a recipient in table 1 it shows up on the query (even if it
has no p.s.a.s or p.o.s). So if a recipient has two PSAs or two POs the
recipient is listed over and over again.

I created a linked form that brings in the recipient info on the main form.
The psa info is on a subform and then the and po info on another subform.
Here's my problem: the recipient information is listed twice on the main
form b/c it is flowing from the query. Is it possible to have the main form
show the recipient once and the subform show the psas. Then each psa will
show the affiliated pos?

Maybe I need to do something to the query? Maybe I need to change the
relationship of the tables?

 

[Ken Sheridan]

Karen:

I think the two problems are probably related. The important thing is that
you reference the subform *control*, not its underlying form object, in the
ControlSource of the hidden text box. The underlying form object is the form
you created based on the PSAs table, the subform *control* is the control on
the parent form which houses the subform.

Open the parent from in design view and select the subform control (if you
find it difficult to select the control you can do this by clicking and
holding down the mouse button just outside the subform in a blank area of the
form and then drag the mouse pointer over the edge of the subform and release
the button). In the 'other' tab of its properties sheet you'll find its Name
property. This is what you need to reference. If Access has given it a
default name like Child1 or similar, change it to something more meaningful
like sfcPSA.

To reference a property of a subform you do so via the Form property of the
subform control, so the reference would be along the lines of:

=[sfcPSA].Form![PSA]

Good luck. I'm logging off for today now (its getting late this side of the
pond) so if you need to get back to me I'll pick up anything you post
tomorrow.

Ken Sheridan
Stafford, England

Also Ken, I'm having trouble w/ what to enter as the txtPSA_Hidden box's
control source (#4 above). Keep getting #NAME? error in that box. FYI - The
primary key of the PSAs tbl is "PSA." The name of the PSA subform is
"frmPSAs Subform." How do I write this exactly?

--
Thanks, Karen

Thanks Ken, this works wonderfully except the PO subform. First, in the PO
subform I'm unable to view the POs associated with the particular PSAs. As
well, as I enter new POs in that subform, they disappear from view.

Please help. I would like to be able to have the user click on the
particular PSA to view its associated POs lower on the form but that doesn't
happen. Did I miss something in your directions. Afterall, you say "As you
navigate to each PSA record in the subform the records in the PurchaseOrders
subform will change to those for the current PSA." I have set the Record
Selectors property to yes in the PSA subform.
--
Thanks, Karen

Karen:

From your description the relationships look fine. As far as the form is
concerned a way of reflecting them would be to have a parent form based on
the Recipients table, a subform based on the PSAs and a subform within the
PSA subform based on the PurchaseOrders table. This would mean the PSA
subform would have to be in single form view, though. If you want it in
continuous form view, however, you can do so by having a Recipients parent
form and two 'correlated' subforms within that, one for PSAs one for
PurchaseOrders.

To create correlated subforms you'd do the following:

1. Create the parent form, in single form view, based on Recipients.

2. Create a form based on PSAs, in continuous form view, and use it as the
source object for a subform in the parent form.

3. Create a form based on PurchaseOrders; this can be in either single or
continuous form view as preferred. Use this as the source object for another
subform in the parent Recipients form.

4. Add a text box, txtPSA_Hidden say, in the parent Recipients form and set
its Visible property to False (No in the properties sheet). Set its
ControlSource property to reference the primary key of the PSAs table, so if
this is the PSA field you'd put something like this:

=[sfcPSA].Form![PSA]

where sfcPSA is the name of the subform control, i.e. the control in the
parent from which houses the subform, not the name of its underlying form
object, unless both have the same name of course.

5. Set the LinkMasterFields and LinkChildFields properties of the PSA
subform control to the names of the relevant primary/foreign key columns from
the Recipients and PSA tables, e.g. RecipientID.

6. Set the LinkMasterFields property of the PurchaseOrders subform control
to the name of the hidden text box on the parent form, txtPSA_Hidden. Set
its LinkChildFields property to the name of the corresponding foreign key
column in the PurchaseOrders table, e.g. PSA.

When the parent form is opened at a recipient the PSA subform will show all
the PSDA records for that recipient, and the PurchaseOrders subform the
orders for the currently selected PSA in the subform. As you navigate to
each PSA record in the subform the records in the PurchaseOrders subform will
change to those for the current PSA. For this reason make sure that you show
RecordSelectors in the PSA subform to make it clear which is currently
selected, so the user knows which PSA the orders displayed relate to.

For the RecordSource of the parent form and the two subforms you'll probably
find it better to use sorted queries rather than the table itself so that the
records are shown in a logical order in each case.

Ken Sheridan
Stafford, England

:

I have three tables whose data flow into a query:

Table 1 - recipient info (recipients are autonumbered - primary key)
Table 2 - personal services agreements (each psa is listed once, but there
may be multiple p.s.a.s for each recipient - which is in a lookup field from
Table 1)
Table 3 - purchase orders (each psa may have multiple purchase orders; the
psa field is a lookup here)

A relationship is created b/t the tables for a query that brings in fields
from all three. I think the relationship among the tables works fine b/c as
long as there is a recipient in table 1 it shows up on the query (even if it
has no p.s.a.s or p.o.s). So if a recipient has two PSAs or two POs the
recipient is listed over and over again.

I created a linked form that brings in the recipient info on the main form.
The psa info is on a subform and then the and po info on another subform.
Here's my problem: the recipient information is listed twice on the main
form b/c it is flowing from the query. Is it possible to have the main form
show the recipient once and the subform show the psas. Then each psa will
show the affiliated pos?

Maybe I need to do something to the query? Maybe I need to change the
relationship of the tables?