Subform with 2 or more link criteria

  • Thread starter Thread starter Uhl
  • Start date Start date
U

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
 
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
Click to expand...

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).
 
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.
 
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.
Click to expand...

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.
 
Back
Top