• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

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

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
citywide
Asked:
citywide
  • 9
  • 6
  • 6
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
change

"[me.money received]"  with the name of the field from table payments, perhaps  "[money received]"
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:



=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
 
Rey Obrero (Capricorn1)Commented:
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
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.

 
citywideAuthor Commented:
Thanks so far for the replies..

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

=DSum("[money received]","payments","[contract id]=" & [Forms]![contract payments]![contract Id] & " and [me.Payment type] =7")
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Yop can't use Me in a text box:

[me.Payment type] =7"

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So, maybe

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

mx
0
 
citywideAuthor Commented:
I've just changed it to [Payment Type] but still get #error
0
 
citywideAuthor Commented:
Would the fact that the [Payment Type] is a drop down box for the use to select...
0
 
citywideAuthor Commented:
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
 
citywideAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
[contract id] is text data type


=DSum("[YourFieldName]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")
0
 
Rey Obrero (Capricorn1)Commented:




[contract id] is text data type


=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")
0
 
citywideAuthor Commented:
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
 
citywideAuthor Commented:
Could I also ask...

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

Thanks
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Text values have to be surrounded by double quotes
0
 
Rey Obrero (Capricorn1)Commented:



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

expanded version for viewing


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

 
0
 
citywideAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
np
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
thx
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 9
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now