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
Brook said:
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