DSUM with variables

[Brennan]

I have a form that uses the DSUM function to pull information from a master
table. Right now, I am using the DSUM because have about 40 textboxes with
the DSUM as a control source because they all pull and sort data from the
same table. I am open to using SQL to populate the textboxes, but that is a
lot of rework. Anyhow, I am adding more criteria to the form to drill deeper
into the data and I need to know if you can create a stored variable and use
that variable in the DSUM equation. For example, I would like to change the
field that is used as a criteria in the DSUM function. Here is an example of
what I am doing and the VBA I have in to cause it to function.

DSUM function
=Val(nz(DSum("[sumofNet Amount - US]","POSTSALECOSTv1","[FML Account Code *]
= 4435 and crit = crit2 and [Accounting Period *]=" & [AccountingPeriod] &
""),0))

VBA
Private Sub Combo119_AfterUpdate()
Dim crit, crit1 As String

Me.Rank = Me.Combo119.Value
If Me.Rank = 1 Then Me.Criteria = "Region"
If Me.Rank = 1 Then Me.criteria2 = Me.Combo119.column(1)
If Me.Rank = 2 Then Me.Criteria = "Industry"
If Me.Rank = 2 Then Me.criteria2 = Me.Combo119.column(2)
If Me.Rank = 3 Then Me.Criteria = "Level 1"
If Me.Rank = 3 Then Me.criteria2 = Me.Combo119.column(3)
If Me.Rank = 4 Then Me.Criteria = "Level 2"
If Me.Rank = 4 Then Me.criteria2 = Me.Combo119.column(4)
If Me.Rank = 5 Then Me.Criteria = "Level 3"
If Me.Rank = 5 Then Me.criteria2 = Me.Combo119.column(5)
crit = Me.Criteria.Value
crit1 = Me.criteria2.Value

AS you can see, I am using the Combobox to determine which fields need to be
used as criteria and the value that should be used as the criteria for that
field. I then store those values in a textbox and then to a variable. I
welcome any suggestions. Thanks

Brennan

 

[Steve Sanford]

Hi Brennan,

Lets talk about the VBA code first.

You have declared crit as a variant and crit1 as a string.
To declare both variables as Strings, you need to use:

Dim crit as String, crit1 As String

or (the way I use - more typing but easier to see errors):

Dim crit as String
Dim crit1 As String


Instead of multiple If() statements, I would the Select Case syntax:

'-------- code beg -----------------
Private Sub Combo119_AfterUpdate()

Select Case Me.Combo119
Case 1
Me.Criteria = "Region"
Me.criteria2 = Me.Combo119.Column(1)
Case 2
Me.Criteria = "Industry"
Me.criteria2 = Me.Combo119.Column(2)
Case 3
Me.Criteria = "Level 1"
Me.criteria2 = Me.Combo119.Column(3)
Case 4
Me.Criteria = "Level 2"
Me.criteria2 = Me.Combo119.Column(4)
Case 5
Me.Criteria = "Level 3"
Me.criteria2 = Me.Combo119.Column(5)
Case Else
Msgbox "---- ERROR in criteria selection!!! -----"
End Select
End Sub
'-------- code end -----------------


I can't imagine the expression for the control source of the textboxes are
returning values. You have three criteria:

[FML Account Code *] = 4435 and <= this is OK

crit = crit2 and <= this is the same as saying 1 = 2. Always False.

[Accounting Period *]=" & [AccountingPeriod] <= ??? a field equal to a
field???

& "" <= this in not needed


This might work, if "crit" is the name of the field in the table. And I am
not sure what "[AccountingPeriod]" is...

=Val(nz(DSum("[sumofNet Amount - US]","POSTSALECOSTv1","[FML Account Code *]
= 4435 and [" & crit & "] = '" & crit2 & "' and [Accounting Period *]=" &
[AccountingPeriod] ),0))


(BTW, you shouldn't use special characters in object names.)

I would think the form is reall slow with 40 DSUM() functions. I might try
using recordsets in the VBA to get the values for the textboxes. Just a
thought...

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have a form that uses the DSUM function to pull information from a master
table. Right now, I am using the DSUM because have about 40 textboxes with
the DSUM as a control source because they all pull and sort data from the
same table. I am open to using SQL to populate the textboxes, but that is a
lot of rework. Anyhow, I am adding more criteria to the form to drill deeper
into the data and I need to know if you can create a stored variable and use
that variable in the DSUM equation. For example, I would like to change the
field that is used as a criteria in the DSUM function. Here is an example of
what I am doing and the VBA I have in to cause it to function.

DSUM function
=Val(nz(DSum("[sumofNet Amount - US]","POSTSALECOSTv1","[FML Account Code *]
= 4435 and crit = crit2 and [Accounting Period *]=" & [AccountingPeriod] &
""),0))

VBA
Private Sub Combo119_AfterUpdate()
Dim crit, crit1 As String

Me.Rank = Me.Combo119.Value
If Me.Rank = 1 Then Me.Criteria = "Region"
If Me.Rank = 1 Then Me.criteria2 = Me.Combo119.column(1)
If Me.Rank = 2 Then Me.Criteria = "Industry"
If Me.Rank = 2 Then Me.criteria2 = Me.Combo119.column(2)
If Me.Rank = 3 Then Me.Criteria = "Level 1"
If Me.Rank = 3 Then Me.criteria2 = Me.Combo119.column(3)
If Me.Rank = 4 Then Me.Criteria = "Level 2"
If Me.Rank = 4 Then Me.criteria2 = Me.Combo119.column(4)
If Me.Rank = 5 Then Me.Criteria = "Level 3"
If Me.Rank = 5 Then Me.criteria2 = Me.Combo119.column(5)
crit = Me.Criteria.Value
crit1 = Me.criteria2.Value

AS you can see, I am using the Combobox to determine which fields need to be
used as criteria and the value that should be used as the criteria for that
field. I then store those values in a textbox and then to a variable. I
welcome any suggestions. Thanks

Brennan