Solved

Dsum #Name error fron control source of text box in subform footer....

Posted on 2008-10-14
21
475 Views
Last Modified: 2013-11-28
I've tried using this in the control source of a text box in the subform footer, but I get the #Name error....


=DSum("[me.money received]","payments","[contract id]=" & [Forms]![contact payments]![contract Id] & " and [me.Payment type] =7")

[me.money received] - In subform
Payments - Table behind subform
[contract id] - In subform
[Form]![contract payments]![contract id] - In Master form
[me.payment type] - In subform

Idea's on what I have wrong.... Cheers
0
Comment
Question by:citywide
  • 9
  • 6
  • 6
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22717484
change

"[me.money received]"  with the name of the field from table payments, perhaps  "[money received]"
0
 
LVL 75
ID: 22717486



=DSum("[me.money received]","payments","[contract id]=" & [Forms]![contact payments]![contract Id] & " and [me.Payment type] =7")

This part ... DSum("[me.money received]", ... that needs to be the Field name in table Payments ...

=DSum("[YourFieldName]","payments","[contract id]=" & [Forms]![contact payments]![contract Id] & " and [me.Payment type] =7")
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22717503
also, you mentioned
[Form]![contract payments]![contract id] - In Master form

and you are using

---------------------------vvvvvv- missing "r" --shouldn't it be  (contract)
"[contract id]=" & [Forms]![contact payments]![contract Id] & "
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:citywide
ID: 22717522
Thanks so far for the replies..

This is what I have changed it to and now I get #error in the text box....
0
 

Author Comment

by:citywide
ID: 22717536
Sorry...

=DSum("[money received]","payments","[contract id]=" & [Forms]![contract payments]![contract Id] & " and [me.Payment type] =7")
0
 
LVL 75
ID: 22717550
Yop can't use Me in a text box:

[me.Payment type] =7"

mx
0
 
LVL 75
ID: 22717554
So, maybe

=DSum("[YourFieldName]","payments","[contract id]=" & [Forms]![contact payments]![contract Id] & " and [Payment type] =7")

mx
0
 

Author Comment

by:citywide
ID: 22717580
I've just changed it to [Payment Type] but still get #error
0
 

Author Comment

by:citywide
ID: 22717596
Would the fact that the [Payment Type] is a drop down box for the use to select...
0
 

Author Comment

by:citywide
ID: 22717605
Sorry,

I'm hitting submit before I read over my responses..

Lets try again, could the fact that the [payment type] is a drop down box for the user to enter the type be causing the error.
0
 

Author Comment

by:citywide
ID: 22717980
Hi all,

I'm hoping a little more info may help...

The master form mentioned above is opened from another form by the afterupdate code below...
Private Sub Contract_ID_AfterUpdate()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "contract payments"
   
    stLinkCriteria = "[Contract ID]= '" & Me![Contract ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Now in my Dsum statement I refer to the [contract ID] = the [Contract ID] from the master form which in this case is populated by the above code could this be an issue. I know that it may have nothing to do with it but I am just throwing things out there looking for a solution.

Would Sum(IIf be an option instead of Dsum...

Cheers.
0
 
LVL 75
ID: 22717994
ok ... if [contract id] - Is in the subform ....


=DSum("[YourFieldName]","payments","[contract id]=" & [contract Id] & " and [Payment type] =7")

Where YourFieldName needs to be the Field in the payment table that you want to Sum, not the Payment Type.  Also, I removed the main form reference in front of contract ID.

It would help if - you gave the Control Names something different than the Control Source field names ... eg

txtContractID ... txtPaymentType ... etc ... in which case it would look like


=DSum("[Payments]","payments","[contract id]=" & [txtContractID] & " and [Payment type] =7")

 ----------^^^^^^^^^ guessing at the field name you want to Sum.

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22717998
[contract id] is text data type


=DSum("[YourFieldName]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 22718003




[contract id] is text data type


=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")
0
 

Author Comment

by:citywide
ID: 22718015
Thanks heaps guys,
I have tried each solution as you guys have posted them

Capricorn1, your last post did the job. Could I ask how I would also include in this statement extra criteria such as addition or subtraction of other payment types such as rebates or adjustments to give me a final outstanding balance.

Excellent great job. I was starting to sweat over this one...
0
 

Author Comment

by:citywide
ID: 22718029
Could I also ask...

Is the additional ' before or after the ".... I can't tell if it is ' "  or " '...

Thanks
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 22718037
Text values have to be surrounded by double quotes
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22718106



'" & [Forms]![contract payments]![contract Id] & "'

expanded version for viewing


    ' " & [Forms]![contract payments]![contract Id] & " '

 
0
 

Author Comment

by:citywide
ID: 22718134
Cheers guys,

I'll open a new Question regarding multiple criteria for this Dsum statement.

Capricorn1 even though your solution did solve my issue would you mind if I gave DatabaseMX 100 points for his help also.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22719810
np
0
 
LVL 75
ID: 22722598
thx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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