Prev: Want to Pull "Only", Not Post
Next: Two Qustions
From: Uhl on 26 Dec 2009 03:26
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
From: John W. Vinson on 26 Dec 2009 12:54
On Sat, 26 Dec 2009 09:26:47 +0100, Uhl <octavee(a)gmx.net> wrote:

>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).
--

John W. Vinson [MVP]
From: Uhl on 26 Dec 2009 14:33
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 schrieb:
> On Sat, 26 Dec 2009 09:26:47 +0100, Uhl <octavee(a)gmx.net> wrote:
>
>> 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).
From: John W. Vinson on 26 Dec 2009 15:16
On Sat, 26 Dec 2009 20:33:39 +0100, Uhl <octavee(a)gmx.net> wrote:

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

--

John W. Vinson [MVP]
From: Uhl on 27 Dec 2009 09:22
Thank you. You are right about the structure.

What I did now is a union query and that worked fine for now.

John W. Vinson schrieb:
> On Sat, 26 Dec 2009 20:33:39 +0100, Uhl <octavee(a)gmx.net> wrote:
>
>> 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.
>
 | 
Pages: 1
Prev: Want to Pull "Only", Not Post
Next: Two Qustions