subform receiving data from parent form

[Guest]

I need assistance with creating a subform that allows multiple approvers for
an invoice

I have two tables:
Invoice
- Invoice_ID (PK)
- other invoice info

Approver
- Approver_ID (PK)
- other approver info

Approval
- Approver_ID (FK)
- Invoice_ID (FK)

When setting up the form I included all the information from the Approval
and Invoice tables and set up to view by Invoice (approvers are a subform).

When openning the subform, I see both the Approver_ID and the Invoice_ID
fields. What is happening is the Invoice_ID is defaulting to "0" instead of
the value of the parent form. I'd like to have the Invoice_ID use the parent
form's Invoice_ID.

After I confirm this is working correctly, will I be able to remove or hide
this field to prevent people from editing/changing?

 

[Allen Browne]

Open your Approval table in design view.
Remove the zero from the Default Value property for both fields.
Consider also setting the Required property to Yes, so they can't save a
record without a value in these foreign key fields.
Save, and close the table.

If you have not already done so, open the Relationships window (Tools menu),
and create relationships from Invoice.Invoice_ID to Approval.Invoice_ID, and
also from Approvate.Approver_ID to Approval.Approver_ID. Make sure the
Referential Integrity box is checked for both relations.

If your main form is bound to the Invoice table, and your subform to the
Approval table, and if the LinkMasterFields and LinkChildFields propertie of
the subform control are set to Invoice_ID, then the subform will inherit the
value of the Invoice_ID value from the main form as soon as you start adding
the record.

And yes, once you have this all working, you can set the Visible property to
No for the foreign key field in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...