edioting data in Form based on a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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] & "'")
 
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 ?


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


dennisthemennis said:
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.
Click to expand...
Click to expand...
 
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


dennisthemennis said:
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 ?


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


dennisthemennis said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ofer, that worked great !!!

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


dennisthemennis said:
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 ?


Ofer said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top