Solved

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

Posted on 2008-10-14
21
448 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 119

Expert Comment

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


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

Accepted Solution

by:
Rey Obrero 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
ID: 22718037
Text values have to be surrounded by double quotes
0
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now