SUM on dynamic subform #error

G

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.
 
G

Guest

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.
 
G

Guest

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

Wayne-I-M said:
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.


[email protected] said:
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.
Click to expand...
Click to expand...
 
G

Guest

Sorry I missread your post

I think - looking at your post - that your subform fieldname is being taken
from the query so you need to reference that and not the original name. I
may be tempted to use DSum (as you already have) to get round this problem.

=DSum("[YourFieldName]","[YourQueryName]")
Note I have simplified this calculation as not really sure about your
concencated sum - but you know your D B better than others.
DSum("Hrs", "dbo_Assignments", "Task_ID=" & [Task_ID] & "
AND WeekSdt=#" & Me.Controls.Item(10).ControlSource & "#")
Click to expand...



--
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.


[email protected] said:
wayne,
thanks but can you explain fieldname.column(10) further? how does a
field have a column?

Wayne-I-M said:
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.


[email protected] said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

thanks. i guess i'll stick with the dsum approach.

i thought i was referencing the new name from the query when i used the
item's controlsource...

Me.Controls.Item(10).ControlSource
i could have simplified it to: me.week1.controlsource i guess (still
doesn't work though)

'week1' is the name of the textbox i'm trying to sum. (same as
item(10)).

i'm not sure why being dynamic causes the error. if the form's
recordsource wasn't set in vba and i just bound it to a table, it
appears to work fine.

Wayne-I-M said:
Sorry I missread your post

I think - looking at your post - that your subform fieldname is being taken
from the query so you need to reference that and not the original name. I
may be tempted to use DSum (as you already have) to get round this problem.

=DSum("[YourFieldName]","[YourQueryName]")
Note I have simplified this calculation as not really sure about your
concencated sum - but you know your D B better than others.
DSum("Hrs", "dbo_Assignments", "Task_ID=" & [Task_ID] & "
AND WeekSdt=#" & Me.Controls.Item(10).ControlSource & "#")
Click to expand...



--
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.


[email protected] said:
wayne,
thanks but can you explain fieldname.column(10) further? how does a
field have a column?

Wayne-I-M said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Sum 4
Show subform column total on parent form 1
Problems with DSum or Running Sum on a Subform 4
Subform Totals 4
Total in a subform 1
Using DSUM to get running total on a filtered form 0
sum of selected records in a subform 2
referring to Subform Total in main form 1

Top