-I am using Access 2013 and have a query-based continuous form listing project information, including the fields “PaymentType” and “PaymentAmount”.
-Each project is assigned one payment type, “A” or “B”.
-Payment amounts are stored in two separate tables: Tbl_PaymentsA and Tbl_PaymentsB (separate tables are necessary)
-The continuous form and two tables share a common key “ProjectID”.
-On the continuous form, the payment type information comes from the underlying query and the Payment Amount field is unbound.
-I am trying to work VBA code so that if the payment type is “A”, then the “Payment Amount” field displays the sum of the amount in Tbl_PaymentsA for that ProjectID, but if B, then the sum of the amount in Tbl_PaymentsB.
-I have tried various pieces of code without any luck, for example:
If Me.PaymentType = “A” Then Me.PaymentAmount = DSum(“Payment”, “Tbl_PaymentsA”, “ProjectID = “ &[Forms]![ContinuousForm]![ProjectID])
Me.PaymentAmount = DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ &[Forms]![ContinuousForm]![ProjectID])
(In the above case the “Payment Amount” field is blank)
Is what I am trying to do even possible ? If yes, any advice on the code would be much appreciated. If not, could anyone advise on the correct approach to this ?
Thank you in advance for any help.