field on main form based on subform data

[Brook]

Good day all,

I have a frminvoices that has frminvoicedetailssubform and
frminvoicepaymentsubform. On frminvoicepaymentsubform I have the following
relevant fiels PaymentDate, PaymentAmt, and AmountDue.

What I would like is to have a field on my frminvoices that will display the
PaymentDate if Amountdue = 0.

Does anyone have any suggestions on how to do this?

Thanks,

Brook

 

[Sprinks]

Brook,

To display the payment date of the current record of the subform on the main
form if the AmountDue is zero, set an unbound textbox' ControlSource to:

=IIf([Forms]![frmInvoices]![frmInvoicePaymentSubform]![AmountDue]=0,[Forms]![frmInvoices]![frmInvoicePaymentSubform]![PaymentDate],"")

Check my spelling; you used two esses in the name of the details subform but
only one in the payments subform.

Hope that helps.
Sprinks

 

[Brook]

Thank you for the suggestion,

the problem that is looks like is that its looking a the first record in
the payments subform and not the last record that would indicate a final
payment.

Any suggestions?

Brook

Brook,

To display the payment date of the current record of the subform on the main
form if the AmountDue is zero, set an unbound textbox' ControlSource to:

=IIf([Forms]![frmInvoices]![frmInvoicePaymentSubform]![AmountDue]=0,[Forms]![frmInvoices]![frmInvoicePaymentSubform]![PaymentDate],"")

Check my spelling; you used two esses in the name of the details subform but
only one in the payments subform.

Hope that helps.
Sprinks

Good day all,

I have a frminvoices that has frminvoicedetailssubform and
frminvoicepaymentsubform. On frminvoicepaymentsubform I have the following
relevant fiels PaymentDate, PaymentAmt, and AmountDue.

What I would like is to have a field on my frminvoices that will display the
PaymentDate if Amountdue = 0.

Does anyone have any suggestions on how to do this?

Thanks,

Brook

 

[Sprinks]

Hi, Brook.

The function actually use the value from the current record in the subform.
If you haven't toggled into the subform, this will be the first record. In
any case, another solution is needed. There may be other ways, but I suggest
using the DMax and DLookup functions to:

- Find the PaymentID of the last record associated with the current invoice
- Lookup the AmountDue for this invoice and PaymentID
- Use the IIf function to display the date associated with this record if
zero, else ""

While (I think) you could stuff the whole thing into a single ControlSource
on the main form, I think its easier to use a divide and conquer strategy and
do the first two steps in invisible controls on the subform. I named them
MaxPaymentID and MaxAmountDue.

The first [InvoiceID] in the criteria parameter string is the field in
Payments, while the second is the InvoiceID from the current record in the
subform (they should all be the same since they're childrent of the parent
invoice).

ControlSource for MaxPaymentID:
=DMax("PaymentID","Payments","[InvoiceID]=[InvoiceID]")

ControlSource for MaxAmountDue:
=DLookUp("AmountDue","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID] =
[MaxPaymentID]")

The ControlSource for the mainform control then becomes
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!PaymentDate,"")

Hope that helps.
Sprinks

 

[Brook]

Thanks Sprinks,

I am going to try this and will posta repsonse. Thank you for your help!

Brook

Hi, Brook.

The function actually use the value from the current record in the subform.
If you haven't toggled into the subform, this will be the first record. In
any case, another solution is needed. There may be other ways, but I suggest
using the DMax and DLookup functions to:

- Find the PaymentID of the last record associated with the current invoice
- Lookup the AmountDue for this invoice and PaymentID
- Use the IIf function to display the date associated with this record if
zero, else ""

While (I think) you could stuff the whole thing into a single ControlSource
on the main form, I think its easier to use a divide and conquer strategy and
do the first two steps in invisible controls on the subform. I named them
MaxPaymentID and MaxAmountDue.

The first [InvoiceID] in the criteria parameter string is the field in
Payments, while the second is the InvoiceID from the current record in the
subform (they should all be the same since they're childrent of the parent
invoice).

ControlSource for MaxPaymentID:
=DMax("PaymentID","Payments","[InvoiceID]=[InvoiceID]")

ControlSource for MaxAmountDue:
=DLookUp("AmountDue","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID] =
[MaxPaymentID]")

The ControlSource for the mainform control then becomes:
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!PaymentDate,"")

Hope that helps.
Sprinks

Thank you for the suggestion,

the problem that is looks like is that its looking a the first record in
the payments subform and not the last record that would indicate a final
payment.

Any suggestions?

Brook

 

[Sprinks]

Brook,

My apologies, but I just realized that it doesn't quite work. You need
another control in the subform to record the date of the the last record,
MaxPaymentDate.

MaxPaymentDate ControlSource:
=DLookUp("PaymentDate","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID]
= [MaxPaymentID]")

Then the main form control's ControlSource becomes
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!MaxPaymentDate,"")

Sprinks

Thanks Sprinks,

I am going to try this and will posta repsonse. Thank you for your help!

Brook

Hi, Brook.

The function actually use the value from the current record in the subform.
If you haven't toggled into the subform, this will be the first record. In
any case, another solution is needed. There may be other ways, but I suggest
using the DMax and DLookup functions to:

- Find the PaymentID of the last record associated with the current invoice
- Lookup the AmountDue for this invoice and PaymentID
- Use the IIf function to display the date associated with this record if
zero, else ""

While (I think) you could stuff the whole thing into a single ControlSource
on the main form, I think its easier to use a divide and conquer strategy and
do the first two steps in invisible controls on the subform. I named them
MaxPaymentID and MaxAmountDue.

The first [InvoiceID] in the criteria parameter string is the field in
Payments, while the second is the InvoiceID from the current record in the
subform (they should all be the same since they're childrent of the parent
invoice).

ControlSource for MaxPaymentID:
=DMax("PaymentID","Payments","[InvoiceID]=[InvoiceID]")

ControlSource for MaxAmountDue:
=DLookUp("AmountDue","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID] =
[MaxPaymentID]")

The ControlSource for the mainform control then becomes:
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!PaymentDate,"")

Hope that helps.
Sprinks

Thank you for the suggestion,

the problem that is looks like is that its looking a the first record in
the payments subform and not the last record that would indicate a final
payment.

Any suggestions?

Brook

 

[Brook]

Hello Sprinks,

I have been working on the code that you have suggested and have run into
a small snag.

The fields are pulling properly, but the code is looking at the entire
table (tblinvoicepayments), so therefore if my last Payment Made was on
9/13/05, hasa paymentID of 6, and an AmountDue of 755, this is showing up on
all my invoice payment subforms.

frminvoicepaymentssubform fields added:

MaxPaymentDate =
DLookUp("PaymentDate","tblinvoicepayments","[InvoiceID]=[InvoiceID] And
[PaymentID]=[MaxPaymentID]")

MaxPaymentID
=DMax("PaymentID","tblinvoicepayments","[InvoiceID]=[InvoiceID]")

MaxAmountDue
=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invoiceid] And
[PaymentID]=[MaxPaymentID]")

Am I overlooking something?

Brook

Brook,

My apologies, but I just realized that it doesn't quite work. You need
another control in the subform to record the date of the the last record,
MaxPaymentDate.

MaxPaymentDate ControlSource:
=DLookUp("PaymentDate","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID]
= [MaxPaymentID]")

Then the main form control's ControlSource becomes:
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!MaxPaymentDate,"")

Sprinks

Thanks Sprinks,

I am going to try this and will posta repsonse. Thank you for your help!

Brook

Hi, Brook.

The function actually use the value from the current record in the subform.
If you haven't toggled into the subform, this will be the first record. In
any case, another solution is needed. There may be other ways, but I suggest
using the DMax and DLookup functions to:

- Find the PaymentID of the last record associated with the current invoice
- Lookup the AmountDue for this invoice and PaymentID
- Use the IIf function to display the date associated with this record if
zero, else ""

While (I think) you could stuff the whole thing into a single ControlSource
on the main form, I think its easier to use a divide and conquer strategy and
do the first two steps in invisible controls on the subform. I named them
MaxPaymentID and MaxAmountDue.

The first [InvoiceID] in the criteria parameter string is the field in
Payments, while the second is the InvoiceID from the current record in the
subform (they should all be the same since they're childrent of the parent
invoice).

ControlSource for MaxPaymentID:
=DMax("PaymentID","Payments","[InvoiceID]=[InvoiceID]")

ControlSource for MaxAmountDue:
=DLookUp("AmountDue","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID] =
[MaxPaymentID]")

The ControlSource for the mainform control then becomes:
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!PaymentDate,"")

Hope that helps.
Sprinks


:

Thank you for the suggestion,

the problem that is looks like is that its looking a the first record in
the payments subform and not the last record that would indicate a final
payment.

Any suggestions?

Brook

 

[Brook]

Good Day sprinks,

I wanted to see if you had a chance to talk a look at my reponse to your
code suggestions?

Thanks for your help!

Brook

Brook,

My apologies, but I just realized that it doesn't quite work. You need
another control in the subform to record the date of the the last record,
MaxPaymentDate.

MaxPaymentDate ControlSource:
=DLookUp("PaymentDate","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID]
= [MaxPaymentID]")

Then the main form control's ControlSource becomes:
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!MaxPaymentDate,"")

Sprinks

Thanks Sprinks,

I am going to try this and will posta repsonse. Thank you for your help!

Brook

Hi, Brook.

The function actually use the value from the current record in the subform.
If you haven't toggled into the subform, this will be the first record. In
any case, another solution is needed. There may be other ways, but I suggest
using the DMax and DLookup functions to:

- Find the PaymentID of the last record associated with the current invoice
- Lookup the AmountDue for this invoice and PaymentID
- Use the IIf function to display the date associated with this record if
zero, else ""

While (I think) you could stuff the whole thing into a single ControlSource
on the main form, I think its easier to use a divide and conquer strategy and
do the first two steps in invisible controls on the subform. I named them
MaxPaymentID and MaxAmountDue.

The first [InvoiceID] in the criteria parameter string is the field in
Payments, while the second is the InvoiceID from the current record in the
subform (they should all be the same since they're childrent of the parent
invoice).

ControlSource for MaxPaymentID:
=DMax("PaymentID","Payments","[InvoiceID]=[InvoiceID]")

ControlSource for MaxAmountDue:
=DLookUp("AmountDue","Payments","[InvoiceID]= [InvoiceID] AND [PaymentID] =
[MaxPaymentID]")

The ControlSource for the mainform control then becomes:
=IIf(Forms!Invoices!Payments!MaxAmountDue=0,Forms!Invoices!Payments!PaymentDate,"")

Hope that helps.
Sprinks


:

Thank you for the suggestion,

the problem that is looks like is that its looking a the first record in
the payments subform and not the last record that would indicate a final
payment.

Any suggestions?

Brook