edioting data in Form based on a query

[dennisthemennis]

I have a table with customer details and a table with payments made. I would
like to display fields from the customer details and calculate a total of
payments made and outstanding balance, if I do this with a query i am unable
to edit any of the customer details.

 

[Ofer]

If you don't need to update the table with the payment made, then bound the
form to the customer table only, and display the payments using dlookup in
the control source of the field.

' If the customer is number type
= DLookup("[Payment]","[PaymentTableName]","Customer = " &
[CustomerFieldNameInForm])

' If the customer is text type
= DLookup("[Payment]","[PaymentTableName]","Customer = '" &
[CustomerFieldNameInForm] & "'")

 

[dennisthemennis]

Thanks for that - i should have said that I will need to summarise the
amounts and calculate the Balance O/s, I have a query to do this at the
moment, will the solution you suggested still apply ?

If you don't need to update the table with the payment made, then bound the
form to the customer table only, and display the payments using dlookup in
the control source of the field.

' If the customer is number type
= DLookup("[Payment]","[PaymentTableName]","Customer = " &
[CustomerFieldNameInForm])

' If the customer is text type
= DLookup("[Payment]","[PaymentTableName]","Customer = '" &
[CustomerFieldNameInForm] & "'")
--
I hope that helped
Good luck

I have a table with customer details and a table with payments made. I would
like to display fields from the customer details and calculate a total of
payments made and outstanding balance, if I do this with a query i am unable
to edit any of the customer details.

 

[Ofer]

Yes, just use the dlookup on the query and not on the table

' If the customer is number type
= DLookup("[Payment]","[QueryName]","Customer = " &
[CustomerFieldNameInForm])

' If the customer is text type
= DLookup("[Payment]","[QueryName]","Customer = '" &
[CustomerFieldNameInForm] & "'")
--
I hope that helped
Good luck

Thanks for that - i should have said that I will need to summarise the
amounts and calculate the Balance O/s, I have a query to do this at the
moment, will the solution you suggested still apply ?

If you don't need to update the table with the payment made, then bound the
form to the customer table only, and display the payments using dlookup in
the control source of the field.

' If the customer is number type
= DLookup("[Payment]","[PaymentTableName]","Customer = " &
[CustomerFieldNameInForm])

' If the customer is text type
= DLookup("[Payment]","[PaymentTableName]","Customer = '" &
[CustomerFieldNameInForm] & "'")
--
I hope that helped
Good luck

I have a table with customer details and a table with payments made. I would
like to display fields from the customer details and calculate a total of
payments made and outstanding balance, if I do this with a query i am unable
to edit any of the customer details.

 

[dennisthemennis]

Thanks Ofer, that worked great !!!

Yes, just use the dlookup on the query and not on the table

' If the customer is number type
= DLookup("[Payment]","[QueryName]","Customer = " &
[CustomerFieldNameInForm])

' If the customer is text type
= DLookup("[Payment]","[QueryName]","Customer = '" &
[CustomerFieldNameInForm] & "'")
--
I hope that helped
Good luck

Thanks for that - i should have said that I will need to summarise the
amounts and calculate the Balance O/s, I have a query to do this at the
moment, will the solution you suggested still apply ?

If you don't need to update the table with the payment made, then bound the
form to the customer table only, and display the payments using dlookup in
the control source of the field.

' If the customer is number type
= DLookup("[Payment]","[PaymentTableName]","Customer = " &
[CustomerFieldNameInForm])

' If the customer is text type
= DLookup("[Payment]","[PaymentTableName]","Customer = '" &
[CustomerFieldNameInForm] & "'")
--
I hope that helped
Good luck


:

I have a table with customer details and a table with payments made. I would
like to display fields from the customer details and calculate a total of
payments made and outstanding balance, if I do this with a query i am unable
to edit any of the customer details.