Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Possible to use DSum in the body of a continuous form ?

Posted on 2015-01-02
5
Medium Priority
?
224 Views
Last Modified: 2015-01-02
Hello,
-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])

Else

Me.PaymentAmount = DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ &[Forms]![ContinuousForm]![ProjectID])

End If

 (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.
0
Comment
Question by:Paul McCabe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527361
What you are trying to do will not work - but a minor modification might.

You are trying to set a textbox (I think) for a continuous field, but have it different for different rows. That can't be done in the way you are doing it in VBA. It will set all of the boxes like that.

Instead, why not use your Query or your Form to generate it directly, e.g.

PaymentAmount: IIf(PaymentType = “1”, DSum(“Payment”, “Tbl_PaymentsA”, “ProjectID = “ & [ProjectID], DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ & [ProjectID]))

Open in new window


or even

PaymentAmount: DSum(“Payment”, IIf(PaymentType = “1”, “Tbl_PaymentsA”, “Tbl_PaymentsB”), “ProjectID = “ & [ProjectID])

Open in new window


Note that there are two i in IIF. Hope this helps.
0
 
LVL 85
ID: 40527698
Phillip has you on the right track (i.e. do the work in the query, not the form) but to add a bit more:

A Continuous or Datasheet form will display correct values in Bound controls, but not unbound controls. If you try to set the value of unbound controls, Access will display the same value for all controls (generally the last value you tried to set, but it could differ).

The only way to show distinct values for each record is to bind the control (i.e. set a ControlSource) and then fill the data needed in the underlying query.
0
 

Author Comment

by:Paul McCabe
ID: 40527942
Phillip and Scott,

Thank you very much for your responses. I tweaked the first piece of code slightly ("PaymentType" enclosed in square brackets and changed "1" to "A") and made it the control source of a text box on the Continuous form; this correctly displays the payment amounts:
=IIf([PaymentType] = “A”, DSum(“Payment”, “Tbl_PaymentsA”, “ProjectID = “ & [ProjectID], DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ & [ProjectID]))

However, it is a bit slow, and as Scott suggests, it might be best to do the calculations in the query. I tried this but ran into a new problem; 9 seperate tables feed the query, and several of them have "ProjectID" and "PaymentType" fields. When I try to run the query, Access throws up a message telling me that multiple tables have these fields, so I guess I need to specify the table the query needs to reference. The table I want to reference is Tbl_ProjectMain, so I tried the following code:
PaymentAmount:IIf([Tbl_ProjectMain].[PaymentType] = “A”, DSum(“Payment”, “Tbl_PaymentsA”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID], DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID]))
The query executes alright, but the "PaymentAmount" column just says "error". Any ideas as to where I am going wrong here ? Also, just in case, I take it I don't have to include Tbl_PaymentsA or Tbl_PaymentsB in the query itself ?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40527944
I would say try splitting it into three columns, and see where the error is:

Column1:[Tbl_ProjectMain].[PaymentType] = “A”
Column2: DSum(“Payment”, “Tbl_PaymentsA”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID])
Column3: DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID])

However, you have a missing ). You have:

PaymentAmount:IIf([Tbl_ProjectMain].[PaymentType] = “A”, DSum(“Payment”, “Tbl_PaymentsA”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID], DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID]))

It should read:

PaymentAmount:IIf([Tbl_ProjectMain].[PaymentType] = “A”, DSum(“Payment”, “Tbl_PaymentsA”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID]), DSum(“Payment”, “Tbl_PaymentsB”, “ProjectID = “ & [Tbl_ProjectMain].[ProjectID]))
0
 

Author Comment

by:Paul McCabe
ID: 40528005
The error was in Column 1, which should have read: Column1:[Tbl_ProjectMain].[PaymentType] = 1
It now works perfectly. Thank you so much for your help !
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question