help with a DLookup Subform to Form

[Brook]

Good Day All,

I have the following field and controlsource for the form field, am having
trouble comparing the invoiceid from the frminvoices to the invoiceid on the
frminvoicedetailssubform. How do I need to change my code? Does anyone have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

 

[tina]

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And

[paymentid]=[MaxPaymentID]")

assuming that the "second" [invoiceid] and the [MaxPaymentID] refer to
controls on the form, and that the whole expression is used in the
ControlSource of a calculated control on the same form, try

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" & [invoiceid] & "
And [paymentid]=" & [MaxPaymentID])

the above also assumes that both fields are number data type, rather than
text.

hth

Good Day All,

I have the following field and controlsource for the form field, am having
trouble comparing the invoiceid from the frminvoices to the invoiceid on the
frminvoicedetailssubform. How do I need to change my code? Does anyone have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

 

[Brook]

Tina,

I wanted to say thank you so much, that helped me out tremendously!!

The code worked perfectly..

Thanks again..

Brook

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

assuming that the "second" [invoiceid] and the [MaxPaymentID] refer to
controls on the form, and that the whole expression is used in the
ControlSource of a calculated control on the same form, try

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" & [invoiceid] & "
And [paymentid]=" & [MaxPaymentID])

the above also assumes that both fields are number data type, rather than
text.

hth

Good Day All,

I have the following field and controlsource for the form field, am having
trouble comparing the invoiceid from the frminvoices to the invoiceid on the
frminvoicedetailssubform. How do I need to change my code? Does anyone have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

 

[tina]

you're welcome

Tina,

I wanted to say thank you so much, that helped me out tremendously!!

The code worked perfectly..

Thanks again..

Brook

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

assuming that the "second" [invoiceid] and the [MaxPaymentID] refer to
controls on the form, and that the whole expression is used in the
ControlSource of a calculated control on the same form, try

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" & [invoiceid] & "
And [paymentid]=" & [MaxPaymentID])

the above also assumes that both fields are number data type, rather than
text.

hth

Good Day All,

I have the following field and controlsource for the form field, am having
trouble comparing the invoiceid from the frminvoices to the invoiceid

on
the

frminvoicedetailssubform. How do I need to change my code? Does anyone have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

 

[Brook]

Hello Tina,

I have a question that maybe you can answer before I post another question
in the forum.

Currently, if there is no data in my tblinvoicepayments the fields that I
have been working with show "#Error".

Can I prevent this with something saying "No Data to Report" or just have
it Null?

Thanks..

Brook

you're welcome

Tina,

I wanted to say thank you so much, that helped me out tremendously!!

The code worked perfectly..

Thanks again..

Brook

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

assuming that the "second" [invoiceid] and the [MaxPaymentID] refer to
controls on the form, and that the whole expression is used in the
ControlSource of a calculated control on the same form, try

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" & [invoiceid] & "
And [paymentid]=" & [MaxPaymentID])

the above also assumes that both fields are number data type, rather than
text.

hth


Good Day All,

I have the following field and controlsource for the form field, am
having
trouble comparing the invoiceid from the frminvoices to the invoiceid on
the
frminvoicedetailssubform. How do I need to change my code? Does anyone
have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid] And
[paymentid]=[MaxPaymentID]")

 

[tina]

let's clarify the nomenclature first. tables have fields, forms have
controls. if the the form is bound to a table or query, then the controls on
the form may be bound to fields in the table/query.

what controls on your form are showing the #Error? and are those controls
bound to fields in the underlying table?

hth

Hello Tina,

I have a question that maybe you can answer before I post another question
in the forum.

Currently, if there is no data in my tblinvoicepayments the fields that I
have been working with show "#Error".

Can I prevent this with something saying "No Data to Report" or just have
it Null?

Thanks..

Brook

you're welcome

Tina,

I wanted to say thank you so much, that helped me out tremendously!!

The code worked perfectly..

Thanks again..

Brook

:

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid]
And

[paymentid]=[MaxPaymentID]")

assuming that the "second" [invoiceid] and the [MaxPaymentID] refer to
controls on the form, and that the whole expression is used in the
ControlSource of a calculated control on the same form, try

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" &

[invoiceid]
& "

And [paymentid]=" & [MaxPaymentID])

the above also assumes that both fields are number data type, rather than
text.

hth


Good Day All,

I have the following field and controlsource for the form field, am
having
trouble comparing the invoiceid from the frminvoices to the

invoiceid
on

the
frminvoicedetailssubform. How do I need to change my code? Does anyone
have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid]
And

[paymentid]=[MaxPaymentID]")

 

[Brook]

Hello Tina,

Sorry about posting the incorrect data

Here are the controls that are showing an "#Error"

MaxPaymentDate =DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" &
[invoiceid] & "And [paymentid]=" & [MaxPaymentID])

MaxPaymentID =DMax("paymentid","tblinvoicepayments","[invoiceid]=" &
[invoiceid])

MaxAmountDue =DLookUp("amountdue","tblinvoicepayments","[invoiceid]=" &
[invoiceid] & "And [paymentid]=" & [MaxPaymentID])

What I am doing with these controls is using them to pass a last payment
date and amount due onto my main form (frminvoices), and the reason for this,
is so that I don't have to enter my frminvoicespaymentssubform to get the
info.

These fields show "#Error" when there have been no payments made, so I was
wondering if there was anything I could do to change the "#Error" to a
"customized phrase" like "No Payemnts Made"?

Thanks and sorry for any confusion..

Brook

let's clarify the nomenclature first. tables have fields, forms have
controls. if the the form is bound to a table or query, then the controls on
the form may be bound to fields in the table/query.

what controls on your form are showing the #Error? and are those controls
bound to fields in the underlying table?

hth

Hello Tina,

I have a question that maybe you can answer before I post another question
in the forum.

Currently, if there is no data in my tblinvoicepayments the fields that I
have been working with show "#Error".

Can I prevent this with something saying "No Data to Report" or just have
it Null?

Thanks..

Brook

you're welcome


Tina,

I wanted to say thank you so much, that helped me out tremendously!!

The code worked perfectly..

Thanks again..

Brook

:

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid]
And
[paymentid]=[MaxPaymentID]")

assuming that the "second" [invoiceid] and the [MaxPaymentID] refer to
controls on the form, and that the whole expression is used in the
ControlSource of a calculated control on the same form, try

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" & [invoiceid]
& "
And [paymentid]=" & [MaxPaymentID])

the above also assumes that both fields are number data type, rather
than
text.

hth


Good Day All,

I have the following field and controlsource for the form field, am
having
trouble comparing the invoiceid from the frminvoices to the invoiceid
on
the
frminvoicedetailssubform. How do I need to change my code? Does anyone
have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid]
And
[paymentid]=[MaxPaymentID]")

 

[tina]

Sorry about posting the incorrect data

no problem, once you learn the correct terms, then you know them for the
future.

for your calculated controls, try the following, as

MaxPaymentDate
=IIf([MaxPaymentID] Is Null, "No payments made", DLookUp("paymentdate",
"tblinvoicepayments", "[invoiceid]=" & [invoiceid] & " And [paymentid]=" &
[MaxPaymentID]))

MaxPaymentID
=IIf([invoiceid] Is Null, Null, DMax("paymentid", "tblinvoicepayments",
"[invoiceid]=" & [invoiceid]))

MaxAmountDue
=IIf([MaxPaymentID] Is Null, "No payments made", DLookUp("amountdue",
"tblinvoicepayments", "[invoiceid]=" & [invoiceid] & " And [paymentid]=" &
[MaxPaymentID]))

hth

Hello Tina,

Sorry about posting the incorrect data

Here are the controls that are showing an "#Error"

MaxPaymentDate =DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" &
[invoiceid] & "And [paymentid]=" & [MaxPaymentID])

MaxPaymentID =DMax("paymentid","tblinvoicepayments","[invoiceid]=" &
[invoiceid])

MaxAmountDue =DLookUp("amountdue","tblinvoicepayments","[invoiceid]=" &
[invoiceid] & "And [paymentid]=" & [MaxPaymentID])

What I am doing with these controls is using them to pass a last payment
date and amount due onto my main form (frminvoices), and the reason for this,
is so that I don't have to enter my frminvoicespaymentssubform to get the
info.

These fields show "#Error" when there have been no payments made, so I was
wondering if there was anything I could do to change the "#Error" to a
"customized phrase" like "No Payemnts Made"?

Thanks and sorry for any confusion..

Brook

let's clarify the nomenclature first. tables have fields, forms have
controls. if the the form is bound to a table or query, then the controls on
the form may be bound to fields in the table/query.

what controls on your form are showing the #Error? and are those controls
bound to fields in the underlying table?

hth

Hello Tina,

I have a question that maybe you can answer before I post another question
in the forum.

Currently, if there is no data in my tblinvoicepayments the fields that I
have been working with show "#Error".

Can I prevent this with something saying "No Data to Report" or just have
it Null?

Thanks..

Brook

:

you're welcome


Tina,

I wanted to say thank you so much, that helped me out tremendously!!

The code worked perfectly..

Thanks again..

Brook

:

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid]
And
[paymentid]=[MaxPaymentID]")

assuming that the "second" [invoiceid] and the [MaxPaymentID]

refer
to

controls on the form, and that the whole expression is used in the
ControlSource of a calculated control on the same form, try

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=" & [invoiceid]
& "
And [paymentid]=" & [MaxPaymentID])

the above also assumes that both fields are number data type, rather
than
text.

hth


Good Day All,

I have the following field and controlsource for the form

field,
am

having
trouble comparing the invoiceid from the frminvoices to the invoiceid
on
the
frminvoicedetailssubform. How do I need to change my code?

Does
anyone

have
any suggestions?

thanks,

Brook

MaxPaymentDate

=DLookUp("paymentdate","tblinvoicepayments","[invoiceid]=[invoiceid]
And
[paymentid]=[MaxPaymentID]")