Subform with 2 or more link criteria

[Uhl]

Hello,

is it possible to link a form with a subform with 2 criteria.

Example: The Form has a field, called InvoiceNumber
The subform has 3 fields InvNumber, InvNumber2, and InvNumber3

I want that the subform with either InvNumber, InvNumber2, or InvNumber3
is connected with the form??

Thanks

 

[John W. Vinson]

Hello,

is it possible to link a form with a subform with 2 criteria.

Example: The Form has a field, called InvoiceNumber
The subform has 3 fields InvNumber, InvNumber2, and InvNumber3

I want that the subform with either InvNumber, InvNumber2, or InvNumber3
is connected with the form??

Thanks

What's the structure of your Tables? How (if at all) are they related? If you
have three invoice numbers in one record, your table design IS WRONG and needs
to be corrected!

That said... no, the Child Link Field must refer to a single field in the
subform's recordsource (or more precisely, must match the number of fields in
the Master Link Field property, if you have a multifield key).

 

[Uhl]

The Invoice Table has the field InvoiceNumber

The payment Table has 3 fields InvNumber, Invnumber2, Invnumber3,
because sometimes a customer pays up to 3 Invoices in one payment.
Usually only the Invnumber field is filled out, and the other 2 fields
are empty.

 

[John W. Vinson]

The Invoice Table has the field InvoiceNumber

The payment Table has 3 fields InvNumber, Invnumber2, Invnumber3,
because sometimes a customer pays up to 3 Invoices in one payment.
Usually only the Invnumber field is filled out, and the other 2 fields
are empty.

It sounds like you have a Many (invoices) to Many (payments) relationship:
each invoice can have zero, one or more payments, and each payment can apply
to one or more Invoices. Is that correct?

If so you need another table, rather than three invoice fields:

PaymentAllocation
InvNumber <link to the invoice to which this payment applies>
PaymentID <link to the table of payments>
AllocatedAmount <currency, how much of the payment applies to this invoice>

You would add three *records* to this table if there are three invoices
involved.

With your current design, you will be totally stuck if you ever need to apply
a payment to FOUR invoices... and if you can have three, someday you'll have
four!

The only way to do what you ask with your current design is to either use
three subforms (one linked to each InvNumber field), or programmatically
change the Child Link Field property.