lp said:
why am i receiving the followign error:
"Sub of function not defined"
on the following statement:
me.txtTotal = Sum(lstbox.Column(7))
I'm so stumped.. this is a built-in function, no?
Thanks! - Jenn
lp,
There are many causes for the "Sub or Function not defined" error message.
Misspellings are one. Also broken references. Check your project's
Preferences, by going to a module and click on Tools->Preferences from the
Access system menu. You should have a checked and valid (not Missing)
references for the items "Visual Basic Applications," and "Microsoft Access
Object Library."
However the "Sub or Function not defined" error message is, in your case,
because a wrong use of the Sum function in your form's code. This is because
the parameter for the Sum function needs to be an expression that evaluates
as valid within the recordset it belongs to. Not from outside in, but from
inside. In your case the expression "lstbox.Column(7)" resolves as a "single
value", the value of the column 7 of the record selected on the listbox.
To sum or process all "values" (as I think you intend to do) you will need
to do any of the following:
METHOD 1
Use the DSum function instead as in:
Me.txtTotal =
DSum("[Total Field]", "[Orders Table]")
or
DSum("[Total Field]", "[Orders Table]", "[CustomerID] = 7")
or
DSum("[Total Field]", "[Orders Table]", "[CustomerID] = " &
Me.txtCustomerID)
METHOD 2
Another way is to output an extra column (8) on your listbox, which you can
set as hidden by using the "Column Widths" property and setting the
correspondent value to zero (0). Before doing that you will need to add a
field on the "Record Source" property of the listbox, and set it using the
Sum function. Then you can use the following:
Me.txttotal = lstbox.Column(8)
METHOD 3
Also you can use a subform instead of the listbox. Set its view to
"datasheet", and create a textbox, and initialize it as:
Me.txttotal.controlsource = "=Sum([Your Field Name])"
You can reference that textbox value from a parent form with the following
code:
Me.txttotal = Me.SubForm!txttotal
Hope that helps, since you did not specify how the form structures the data
on the screen. Due to this, some of the methods may not fully adapt to your
specific case. However they give you an idea on how to address the problem.
Let us know.
