DSum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code:

Private Sub Form_Current()
If Not IsNull(Me.ID) Then
Me.Desks = DSum("Desks", "tblInstalationBuildings",
"[tblInstalationBuildings].[Compound] =" & Me.ID)
End If
End Sub

This sets all Desks fields in the continuous form to be the same value
including the default value for desks of a new record. The continuous form is
a continuous list of compounds. I am trying to display the sum of all desks
in that compound which is the sum of all desks in tblInstalationBuildings
with Compound equal to the ID of this record (compound). The continuous form
is a subform so i am limited in my options. Am I placing this code in the
wrong function? Thank You.
 
I forgot to mention that this gives me the correct sum depending on the
record selected but changes all Desks fields to the sum related to the
selected record when selection is changed.
 
Eddie,

Try removing the code, and instead put the expression in the Control
Source property of the Desks textbox, like this...
=DSum("[Desks]","tblInstalationBuildings","[Compound]=" & [ID])

--
Steve Schapel, Microsoft Access MVP

I forgot to mention that this gives me the correct sum depending on the
record selected but changes all Desks fields to the sum related to the
selected record when selection is changed.

:

I have the following code:

Private Sub Form_Current()
If Not IsNull(Me.ID) Then
Me.Desks = DSum("Desks", "tblInstalationBuildings",
"[tblInstalationBuildings].[Compound] =" & Me.ID)
End If
End Sub

This sets all Desks fields in the continuous form to be the same value
including the default value for desks of a new record. The continuous form is
a continuous list of compounds. I am trying to display the sum of all desks
in that compound which is the sum of all desks in tblInstalationBuildings
with Compound equal to the ID of this record (compound). The continuous form
is a subform so i am limited in my options. Am I placing this code in the
wrong function? Thank You.
Click to expand...
Click to expand...
 
If i set the textbox to be
=DSum("Desks","tblInstalationBuildings","[tblInstalationBuildings].[Compound] =" & [ID])
it works fine except for the fact that i get #error for a new record. How
can i have this condition = 0?

Edward Jones (Eddie) said:
I forgot to mention that this gives me the correct sum depending on the
record selected but changes all Desks fields to the sum related to the
selected record when selection is changed.

Edward Jones (Eddie) said:
I have the following code:

Private Sub Form_Current()
If Not IsNull(Me.ID) Then
Me.Desks = DSum("Desks", "tblInstalationBuildings",
"[tblInstalationBuildings].[Compound] =" & Me.ID)
End If
End Sub

This sets all Desks fields in the continuous form to be the same value
including the default value for desks of a new record. The continuous form is
a continuous list of compounds. I am trying to display the sum of all desks
in that compound which is the sum of all desks in tblInstalationBuildings
with Compound equal to the ID of this record (compound). The continuous form
is a subform so i am limited in my options. Am I placing this code in the
wrong function? Thank You.
Click to expand...
Click to expand...
 
Eddie,

Try it like this...
=Nz(DSum("Desks","tblInstalationBuildings","[tblInstalationBuildings].[Compound]
=" & [ID]),0)

Do you use this form for data entry/editing? If not, and it is just for
data display, there is another way to do it, probably better.
 
I put in the following code and still got #Error as the displayed valu
=nz(DSum("Desks","tblInstalationBuildings","[tblInstalationBuildings].[Compound] =" & [ID]),0)
any suggestions?

Steve Schapel said:
Eddie,

Try it like this...
=Nz(DSum("Desks","tblInstalationBuildings","[tblInstalationBuildings].[Compound]
=" & [ID]),0)

Do you use this form for data entry/editing? If not, and it is just for
data display, there is another way to do it, probably better.

--
Steve Schapel, Microsoft Access MVP

If i set the textbox to be:
=DSum("Desks","tblInstalationBuildings","[tblInstalationBuildings].[Compound] =" & [ID])
it works fine except for the fact that i get #error for a new record. How
can i have this condition = 0?
Click to expand...
Click to expand...
 
Are compound and ID text fields?
You may need quotation marks around the target string. You can use single
quotes
A single quote after the equal sign = ' " and one enclosed in double
quotes at the end " ' "


Me.Desks = DSum("Desks", "tblInstalationBuildings",
"[tblInstalationBuildings].[Compound] ='" & Me.ID & "'")
 
Eddie,

There are a couple of ways that might help.

One is to set the AllowAdditions property of the form to No, so you
don't see a row with a non-record. If you use the form for adding new
records, you would then need a little command button somewhere to
programmatically set the AllowAdditions to Yes while you added the new
record, or else add the new record via a separate subform placed
immediately above or below the existing subform. These sorts of
techniques are in common usage.

Another approach might be to create a User-Defined Function, in a
standard module, like this...

Public Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function

.... and then you can use this function in the Control Source expression
of your textbox, like this...
=nnz(DSum("[Desks]","tblInstalationBuildings","[Compound]=" & [ID]))
 
Back
Top