SUM on dynamic subform #error

[Guest]

hoping someone can offer some suggestions what might be going on.

i've made subform subtotals before by using =sum([field name]) in the
subform's footer. i now have a dynamic subform based on a select query.
i dynamically modify a query to select the correct fields from the
table and then set the subform's recordsource to that query.
(subform onload): Me.RecordSource = "qrySelect_Data_Local"

the subform populates correctly. however the =sum in the footer doesn't
work. shows '#error'

i tested with a field name that i knew would be in the query. i also
tried to make it more dynamic by referencing the field by its index ie:
=sum(Me.Controls.Item(10).ControlSource). that didn't work either. if i
remember correctly this showed '#name' rather than '#error'. might be
mistaken.

the only way i've gotten it to work is using dsum on the subform's
onload event. (snippet, text32 is subtotal textbox):
Me.Text32 = DSum("Hrs", "dbo_Assignments", "Task_ID=" & [Task_ID] & "
AND WeekSdt=#" & Me.Controls.Item(10).ControlSource & "#")

if i set an onclick event to text32 to show me:
MsgBox (Me.Controls.Item(10).ControlSource)
MsgBox (DSum("Hrs", "dbo_Assignments", "Task_ID=" & [Task_ID] & " AND
WeekSdt=#" & Me.Controls.Item(10).ControlSource & "#"))

they all return the correct results... then why can't i enter
=sum(Me.Controls.Item(10).ControlSource) directly into the subtotal's
control source in design view of the subform? rather than populating it
in vba?

just curious. hope all that made sense. let me know if more code for
context is needed. thanks.

 

[Wayne-I-M]

Hi Mark

Create a new text box on the subform call it [Item]
The control source for this would be = FieldName.column(10)

FieldName = the name of the field that you are trying sum the results of
column 10


Create a new box in the footer section. Control source
=Sum([Item])

Hope this helps


--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

 

[Guest]

wayne,
thanks but can you explain fieldname.column(10) further? how does a
field have a column?

Hi Mark

Create a new text box on the subform call it [Item]
The control source for this would be = FieldName.column(10)

FieldName = the name of the field that you are trying sum the results of
column 10


Create a new box in the footer section. Control source
=Sum([Item])

Hope this helps


--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

hoping someone can offer some suggestions what might be going on.

i've made subform subtotals before by using =sum([field name]) in the
subform's footer. i now have a dynamic subform based on a select query.
i dynamically modify a query to select the correct fields from the
table and then set the subform's recordsource to that query.
(subform onload): Me.RecordSource = "qrySelect_Data_Local"

the subform populates correctly. however the =sum in the footer doesn't
work. shows '#error'

i tested with a field name that i knew would be in the query. i also
tried to make it more dynamic by referencing the field by its index ie:
=sum(Me.Controls.Item(10).ControlSource). that didn't work either. if i
remember correctly this showed '#name' rather than '#error'. might be
mistaken.

the only way i've gotten it to work is using dsum on the subform's
onload event. (snippet, text32 is subtotal textbox):
Me.Text32 = DSum("Hrs", "dbo_Assignments", "Task_ID=" & [Task_ID] & "
AND WeekSdt=#" & Me.Controls.Item(10).ControlSource & "#")

if i set an onclick event to text32 to show me:
MsgBox (Me.Controls.Item(10).ControlSource)
MsgBox (DSum("Hrs", "dbo_Assignments", "Task_ID=" & [Task_ID] & " AND
WeekSdt=#" & Me.Controls.Item(10).ControlSource & "#"))

they all return the correct results... then why can't i enter
=sum(Me.Controls.Item(10).ControlSource) directly into the subtotal's
control source in design view of the subform? rather than populating it
in vba?

just curious. hope all that made sense. let me know if more code for
context is needed. thanks.