help with form/subform fields...

[Guest]

good day all...

I have frminvoices that I use to create new invocies as well as track
payments through another payments subform.

What I would like to do is add the following fields to my main invoice
form so that I wouldn't have to go to the payments subform: last payment
made(based on subform field payment date) and balance due (based on field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[tina]

you can add two unbound textbox controls on your main form, and use domain
aggregate functions to get the data directly from the payments table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the DMax()
and DSum() aggregate functions in Access Help to learn details about how
they work, and post back if you run into any problems.

hth

 

[Guest]

Hello Tina,

Here is what I have on my frmpaymentssubform...

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

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

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

the fields on my supayments subform work great and pull the data, except
that they pull data based on my entire tblinvoicepayments on not on the
particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

you can add two unbound textbox controls on your main form, and use domain
aggregate functions to get the data directly from the payments table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the DMax()
and DSum() aggregate functions in Access Help to learn details about how
they work, and post back if you run into any problems.

hth

good day all...

I have frminvoices that I use to create new invocies as well as track
payments through another payments subform.

What I would like to do is add the following fields to my main invoice
form so that I wouldn't have to go to the payments subform: last payment
made(based on subform field payment date) and balance due (based on field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[tina]

it's not clear what you're trying to do. you originally asked about
displaying data based on the subform records, on the main form. now you're
posting expressions that are not the ControlSource values of unbound
controls, saying they're in the subform - but i have no idea *where* in the
subform you're using the expressions, or how.

did you try the solution i posted?

hth

Hello Tina,

Here is what I have on my frmpaymentssubform...

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

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

MaxPaymentAmount=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invo
iceid] And [PaymentID]=[MaxPaymentID]")

the fields on my supayments subform work great and pull the data, except
that they pull data based on my entire tblinvoicepayments on not on the
particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

you can add two unbound textbox controls on your main form, and use domain
aggregate functions to get the data directly from the payments table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the DMax()
and DSum() aggregate functions in Access Help to learn details about how
they work, and post back if you run into any problems.

hth

good day all...

I have frminvoices that I use to create new invocies as well as track
payments through another payments subform.

What I would like to do is add the following fields to my main invoice
form so that I wouldn't have to go to the payments subform: last payment
made(based on subform field payment date) and balance due (based on field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[Guest]

Tina,

Before I can pass the max fields to the main form I have to establish them
on the subform, that is what i'm trying to do. and everything works except
that it reads from the entire table instead of the invoice/record that I am
currently on within my form.

Brook

it's not clear what you're trying to do. you originally asked about
displaying data based on the subform records, on the main form. now you're
posting expressions that are not the ControlSource values of unbound
controls, saying they're in the subform - but i have no idea *where* in the
subform you're using the expressions, or how.

did you try the solution i posted?

hth

Hello Tina,

Here is what I have on my frmpaymentssubform...

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

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

MaxPaymentAmount=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invo
iceid] And [PaymentID]=[MaxPaymentID]")

the fields on my supayments subform work great and pull the data, except
that they pull data based on my entire tblinvoicepayments on not on the
particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

you can add two unbound textbox controls on your main form, and use domain
aggregate functions to get the data directly from the payments table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the DMax()
and DSum() aggregate functions in Access Help to learn details about how
they work, and post back if you run into any problems.

hth


good day all...

I have frminvoices that I use to create new invocies as well as track
payments through another payments subform.

What I would like to do is add the following fields to my main invoice
form so that I wouldn't have to go to the payments subform: last payment
made(based on subform field payment date) and balance due (based on field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[tina]

Before I can pass the max fields to the main form I have to establish
them

on the subform, that is what i'm trying to do.

a domain aggregate function pulls data directly from the domain - in this
case, from the table. you don't need to refer to the subform at all. suggest
you try the solution i posted, and note that i made an error in the syntax,
leaving out a closing bracket. so the correct syntax is

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyFieldInMainForm])

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = " & [PrimaryKeyFieldInMainForm])

the above also assumes that the primary/foreign key pair are Number data
type. if the data type is Text instead, you need to surround the primary key
value with single quotes, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = '" &
[PrimaryKeyFieldInMainForm] & "'")

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = '" & [PrimaryKeyFieldInMainForm] & "'")

hth

Tina,

Before I can pass the max fields to the main form I have to establish them
on the subform, that is what i'm trying to do. and everything works except
that it reads from the entire table instead of the invoice/record that I am
currently on within my form.

Brook

it's not clear what you're trying to do. you originally asked about
displaying data based on the subform records, on the main form. now you're
posting expressions that are not the ControlSource values of unbound
controls, saying they're in the subform - but i have no idea *where* in the
subform you're using the expressions, or how.

did you try the solution i posted?

hth

Hello Tina,

Here is what I have on my frmpaymentssubform...

MaxPaymentDate=DLookUp("PaymentDate","tblinvoicepayments","[InvoiceID]=[Invo
iceID] And [PaymentID]=[MaxPaymentID]") MaxPaymentID=DMax("PaymentID","tblinvoicepayments","[InvoiceID]=[InvoiceID]"
) MaxPaymentAmount=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invo
iceid] And [PaymentID]=[MaxPaymentID]")

the fields on my supayments subform work great and pull the data, except
that they pull data based on my entire tblinvoicepayments on not on the
particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

:

you can add two unbound textbox controls on your main form, and use domain
aggregate functions to get the data directly from the payments table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the DMax()
and DSum() aggregate functions in Access Help to learn details about how
they work, and post back if you run into any problems.

hth


good day all...

I have frminvoices that I use to create new invocies as well as track
payments through another payments subform.

What I would like to do is add the following fields to my main invoice
form so that I wouldn't have to go to the payments subform: last payment
made(based on subform field payment date) and balance due (based

on
field

amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[Guest]

Thank you... I thought that I had to pull the data from the form and not the
tblitself.

That worked...

I do have one more question thought, is there a way to store and update
those DMAx fields to show on a report?

thanks,

Brook

Before I can pass the max fields to the main form I have to establish them
on the subform, that is what i'm trying to do.

a domain aggregate function pulls data directly from the domain - in this
case, from the table. you don't need to refer to the subform at all. suggest
you try the solution i posted, and note that i made an error in the syntax,
leaving out a closing bracket. so the correct syntax is

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyFieldInMainForm])

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = " & [PrimaryKeyFieldInMainForm])

the above also assumes that the primary/foreign key pair are Number data
type. if the data type is Text instead, you need to surround the primary key
value with single quotes, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = '" &
[PrimaryKeyFieldInMainForm] & "'")

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = '" & [PrimaryKeyFieldInMainForm] & "'")

hth

Tina,

Before I can pass the max fields to the main form I have to establish them
on the subform, that is what i'm trying to do. and everything works except
that it reads from the entire table instead of the invoice/record that I am
currently on within my form.

Brook

it's not clear what you're trying to do. you originally asked about
displaying data based on the subform records, on the main form. now you're
posting expressions that are not the ControlSource values of unbound
controls, saying they're in the subform - but i have no idea *where* in the
subform you're using the expressions, or how.

did you try the solution i posted?

hth


Hello Tina,

Here is what I have on my frmpaymentssubform...


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


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


MaxPaymentAmount=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invo
iceid] And [PaymentID]=[MaxPaymentID]")

the fields on my supayments subform work great and pull the data, except
that they pull data based on my entire tblinvoicepayments on not on the
particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

:

you can add two unbound textbox controls on your main form, and use
domain
aggregate functions to get the data directly from the payments table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the
DMax()
and DSum() aggregate functions in Access Help to learn details about how
they work, and post back if you run into any problems.

hth


good day all...

I have frminvoices that I use to create new invocies as well as track
payments through another payments subform.

What I would like to do is add the following fields to my main
invoice
form so that I wouldn't have to go to the payments subform: last
payment
made(based on subform field payment date) and balance due (based on
field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[tina]

you're welcome

is there a way to store and update
those DMAx fields to show on a report?

data normalization rules say that you should not saved calculated values in
a table, and there is rarely a good business or technical reason to break
that rule - especially in a case like this, where the values change
frequently. when you need this data for a report, you can generate it pretty
much the same way you did for the form - at runtime, so it's always current
and accurate.

hth

Thank you... I thought that I had to pull the data from the form and not the
tblitself.

That worked...

I do have one more question thought, is there a way to store and update
those DMAx fields to show on a report?

thanks,

Brook

Before I can pass the max fields to the main form I have to

establish
them

on the subform, that is what i'm trying to do.

a domain aggregate function pulls data directly from the domain - in this
case, from the table. you don't need to refer to the subform at all. suggest
you try the solution i posted, and note that i made an error in the syntax,
leaving out a closing bracket. so the correct syntax is

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyFieldInMainForm])

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = " & [PrimaryKeyFieldInMainForm])

the above also assumes that the primary/foreign key pair are Number data
type. if the data type is Text instead, you need to surround the primary key
value with single quotes, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = '" &
[PrimaryKeyFieldInMainForm] & "'")

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = '" & [PrimaryKeyFieldInMainForm] & "'")

hth

Tina,

Before I can pass the max fields to the main form I have to

establish
them

on the subform, that is what i'm trying to do. and everything works except
that it reads from the entire table instead of the invoice/record that

I
am

currently on within my form.

Brook

:

it's not clear what you're trying to do. you originally asked about
displaying data based on the subform records, on the main form. now you're
posting expressions that are not the ControlSource values of unbound
controls, saying they're in the subform - but i have no idea *where*

in
the

subform you're using the expressions, or how.

did you try the solution i posted?

hth


Hello Tina,

Here is what I have on my frmpaymentssubform...

MaxPaymentDate=DLookUp("PaymentDate","tblinvoicepayments","[InvoiceID]=[Invo

iceID] And [PaymentID]=[MaxPaymentID]")

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

)

MaxPaymentAmount=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invo

iceid] And [PaymentID]=[MaxPaymentID]")

the fields on my supayments subform work great and pull the data, except
that they pull data based on my entire tblinvoicepayments on not

on
the

particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

:

you can add two unbound textbox controls on your main form, and use
domain
aggregate functions to get the data directly from the payments table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the
DMax()
and DSum() aggregate functions in Access Help to learn details

about
how

they work, and post back if you run into any problems.

hth


good day all...

I have frminvoices that I use to create new invocies as well

as
track

payments through another payments subform.

What I would like to do is add the following fields to my main
invoice
form so that I wouldn't have to go to the payments subform: last
payment
made(based on subform field payment date) and balance due

(based
on

field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[Guest]

Tina,

Again thanks, I did set up a quick report and pull the data directly.. and
it worked great.

Thanks and you have helped me tremedously

Brook

you're welcome

is there a way to store and update
those DMAx fields to show on a report?

data normalization rules say that you should not saved calculated values in
a table, and there is rarely a good business or technical reason to break
that rule - especially in a case like this, where the values change
frequently. when you need this data for a report, you can generate it pretty
much the same way you did for the form - at runtime, so it's always current
and accurate.

hth

Thank you... I thought that I had to pull the data from the form and not the
tblitself.

That worked...

I do have one more question thought, is there a way to store and update
those DMAx fields to show on a report?

thanks,

Brook

Before I can pass the max fields to the main form I have to establish
them
on the subform, that is what i'm trying to do.

a domain aggregate function pulls data directly from the domain - in this
case, from the table. you don't need to refer to the subform at all. suggest
you try the solution i posted, and note that i made an error in the syntax,
leaving out a closing bracket. so the correct syntax is

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyFieldInMainForm])

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = " & [PrimaryKeyFieldInMainForm])

the above also assumes that the primary/foreign key pair are Number data
type. if the data type is Text instead, you need to surround the primary key
value with single quotes, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = '" &
[PrimaryKeyFieldInMainForm] & "'")

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = '" & [PrimaryKeyFieldInMainForm] & "'")

hth


Tina,

Before I can pass the max fields to the main form I have to establish
them
on the subform, that is what i'm trying to do. and everything works except
that it reads from the entire table instead of the invoice/record that I
am
currently on within my form.

Brook

:

it's not clear what you're trying to do. you originally asked about
displaying data based on the subform records, on the main form. now
you're
posting expressions that are not the ControlSource values of unbound
controls, saying they're in the subform - but i have no idea *where* in
the
subform you're using the expressions, or how.

did you try the solution i posted?

hth


Hello Tina,

Here is what I have on my frmpaymentssubform...



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



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



MaxPaymentAmount=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invo
iceid] And [PaymentID]=[MaxPaymentID]")

the fields on my supayments subform work great and pull the data,
except
that they pull data based on my entire tblinvoicepayments on not on
the
particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

:

you can add two unbound textbox controls on your main form, and use
domain
aggregate functions to get the data directly from the payments
table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course. look up the
DMax()
and DSum() aggregate functions in Access Help to learn details about
how
they work, and post back if you run into any problems.

hth


good day all...

I have frminvoices that I use to create new invocies as well as
track
payments through another payments subform.

What I would like to do is add the following fields to my main
invoice
form so that I wouldn't have to go to the payments subform: last
payment
made(based on subform field payment date) and balance due (based
on
field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook

 

[tina]

again, you're very welcome

Tina,

Again thanks, I did set up a quick report and pull the data directly.. and
it worked great.

Thanks and you have helped me tremedously

Brook

you're welcome

is there a way to store and update
those DMAx fields to show on a report?

data normalization rules say that you should not saved calculated values in
a table, and there is rarely a good business or technical reason to break
that rule - especially in a case like this, where the values change
frequently. when you need this data for a report, you can generate it pretty
much the same way you did for the form - at runtime, so it's always current
and accurate.

hth

Thank you... I thought that I had to pull the data from the form and

not
the

tblitself.

That worked...

I do have one more question thought, is there a way to store and update
those DMAx fields to show on a report?

thanks,

Brook

:

Before I can pass the max fields to the main form I have to establish
them
on the subform, that is what i'm trying to do.

a domain aggregate function pulls data directly from the domain - in this
case, from the table. you don't need to refer to the subform at all. suggest
you try the solution i posted, and note that i made an error in the syntax,
leaving out a closing bracket. so the correct syntax is

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyFieldInMainForm])

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = " & [PrimaryKeyFieldInMainForm])

the above also assumes that the primary/foreign key pair are Number data
type. if the data type is Text instead, you need to surround the

primary
key

value with single quotes, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = '" &
[PrimaryKeyFieldInMainForm] & "'")

=[InvoiceAmountFieldInMainForm] - DSum("[PaymentAmount]", "PaymentsTable",
"[ForeignKeyField] = '" & [PrimaryKeyFieldInMainForm] & "'")

hth


Tina,

Before I can pass the max fields to the main form I have to establish
them
on the subform, that is what i'm trying to do. and everything

works
except

that it reads from the entire table instead of the invoice/record

that
I

am
currently on within my form.

Brook

:

it's not clear what you're trying to do. you originally asked about
displaying data based on the subform records, on the main form. now
you're
posting expressions that are not the ControlSource values of unbound
controls, saying they're in the subform - but i have no idea

*where*
in

the
subform you're using the expressions, or how.

did you try the solution i posted?

hth


Hello Tina,

Here is what I have on my frmpaymentssubform...

MaxPaymentDate=DLookUp("PaymentDate","tblinvoicepayments","[InvoiceID]=[Invo

iceID] And [PaymentID]=[MaxPaymentID]")

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

)

MaxPaymentAmount=DLookUp("AmountDue","tblinvoicepayments","[InvoiceID]=[invo

iceid] And [PaymentID]=[MaxPaymentID]")

the fields on my supayments subform work great and pull the data,
except
that they pull data based on my entire tblinvoicepayments on

not
on

the
particular inboice number/record that I am on within my form?

Do you have any suggestions... I hope this is clear...

Brook

:

you can add two unbound textbox controls on your main form,

and
use

domain
aggregate functions to get the data directly from the payments
table, as

=DMax("[PaymentDate]", "PaymentsTable", "[ForeignKeyField] = " &
[PrimaryKeyField)

=[InvoiceAmount] - DSum("[PaymentAmount]", "PaymentsTable",
""[ForeignKeyField] = " & [PrimaryKeyField)

substitute the correct table and field names, of course.

look up
the

DMax()
and DSum() aggregate functions in Access Help to learn

details
about

how
they work, and post back if you run into any problems.

hth


good day all...

I have frminvoices that I use to create new invocies as

well
as

track
payments through another payments subform.

What I would like to do is add the following fields to

my
main

invoice
form so that I wouldn't have to go to the payments

subform:
last

payment
made(based on subform field payment date) and balance due (based
on
field
amount due).

does anyone have any ideas on how to accomplish this?

Thanks,

Brook