txtbx on a form that calculates total of "Sold" field in a one to

[Guest]

I often wish that people would share how their database will be applied, so
here you go: I built a database to track e-bay items I've sold. There are
two tables, first one has primary key ebay item number with the details and
calculated listing fees to be input on a form. This form has sub-form for
data entry into second table which tracks items sold (could be several for
the same item number). I'd like, on the main form to keep a running total of
all the "Sold Price" 's in the Sold Items table that are related to the item
number currently being viewed in the main form. I'm very close with all the
other attributes of this database...could you please point me in the right
direction?

 

[Guest]

Two possible methods would be:

1. Add a text box to the form with a ControlSource:

=DSum("[Sold Price]", "[Sold Items]", "[Item Number] = " & [Item Number])

where Item Number is the name of the primary key column of the main Items
table and the foreign key column in Items Sold which references it. Its
assumed it’s a number data type. If its text use:

=DSum("[Sold Price]", "[Sold Items]", "[Item Number] = " & Chr(34) & [Item
Number] & Chr(34))

2. Add a hidden unbound text box to the subform, txtTotalSales say with a
ControlSource of:

=Sum([Sold Price])

and for the ControlSource of the text box on the parent form use:

=YourSubformControl.Form!txtTotalSales

where YourSubFormControl is the name of the subform control in the parent
form, i.e. the control which houses the subform, not the name of its
underlying form object (unless they both have the same name, of course).

Ken Sheridan
Stafford, England

 

[Guest]

Thank you Ken, those are both excellent solutions and I got both solutions to
work, almost. With the first solution, I am getting an error message.
=Dsum("[Sold Price]","[Sold Items]","[eBay Item Number]=" & [eBay Item
Number])
[Sold Price] is the field I want added
[Sold Items] is the table containing [Sold Price]
[eBay Item Number] is the field from [Sold Items] that needs to match the
value in the combobox on this form. The combobox values are a value lookup
from the primary key field [eBay Item Number] in the main table [eBay Items].
The second solution works like a charm as long as I can refresh the records
only while the subform is open.
What do you think I should do?
--
mclitle "I''''m a Sponge"

Two possible methods would be:

1. Add a text box to the form with a ControlSource:

=DSum("[Sold Price]", "[Sold Items]", "[Item Number] = " & [Item Number])

where Item Number is the name of the primary key column of the main Items
table and the foreign key column in Items Sold which references it. Its
assumed it’s a number data type. If its text use:

=DSum("[Sold Price]", "[Sold Items]", "[Item Number] = " & Chr(34) & [Item
Number] & Chr(34))

2. Add a hidden unbound text box to the subform, txtTotalSales say with a
ControlSource of:

=Sum([Sold Price])

and for the ControlSource of the text box on the parent form use:

=YourSubformControl.Form!txtTotalSales

where YourSubFormControl is the name of the subform control in the parent
form, i.e. the control which houses the subform, not the name of its
underlying form object (unless they both have the same name, of course).

Ken Sheridan
Stafford, England

I often wish that people would share how their database will be applied, so
here you go: I built a database to track e-bay items I've sold. There are
two tables, first one has primary key ebay item number with the details and
calculated listing fees to be input on a form. This form has sub-form for
data entry into second table which tracks items sold (could be several for
the same item number). I'd like, on the main form to keep a running total of
all the "Sold Price" 's in the Sold Items table that are related to the item
number currently being viewed in the main form. I'm very close with all the
other attributes of this database...could you please point me in the right
direction?

 

[Guest]

Firstly I'm assuming the item number is stored a s number data type. Try
referencing the combo box for the value of the item number. If the item
number is the BoundColumn of the combo box then you just reference it by
name, so if the combo box is bound to the foreign key [eBay Item Number]
field and its Name is cboItemNumber say you'd use:

=Dsum("[Sold Price]","[Sold Items]","[eBay Item Number]=" & [cboItemNumber])

If the item number is not the bound column then you have to use the Column
property to reference it. This property is zero-based, so if the item number
was the second column in the combo box's RowSource for instance you'd use:

=Dsum("[Sold Price]","[Sold Items]","[eBay Item Number]=" &
[cboItemNumber].Column(1))

Ken Sheridan
Stafford, England

 

[Guest]

WooHoo! I got it. I had to change the Item Number to text because it was too
many digits. You had already answered the question. Thank you so much.
--
mclitle "I''''m a Sponge"

Thank you Ken, those are both excellent solutions and I got both solutions to
work, almost. With the first solution, I am getting an error message.
=Dsum("[Sold Price]","[Sold Items]","[eBay Item Number]=" & [eBay Item
Number])
[Sold Price] is the field I want added
[Sold Items] is the table containing [Sold Price]
[eBay Item Number] is the field from [Sold Items] that needs to match the
value in the combobox on this form. The combobox values are a value lookup
from the primary key field [eBay Item Number] in the main table [eBay Items].
The second solution works like a charm as long as I can refresh the records
only while the subform is open.
What do you think I should do?
--
mclitle "I''''m a Sponge"

Two possible methods would be:

1. Add a text box to the form with a ControlSource:

=DSum("[Sold Price]", "[Sold Items]", "[Item Number] = " & [Item Number])

where Item Number is the name of the primary key column of the main Items
table and the foreign key column in Items Sold which references it. Its
assumed it’s a number data type. If its text use:

=DSum("[Sold Price]", "[Sold Items]", "[Item Number] = " & Chr(34) & [Item
Number] & Chr(34))

2. Add a hidden unbound text box to the subform, txtTotalSales say with a
ControlSource of:

=Sum([Sold Price])

and for the ControlSource of the text box on the parent form use:

=YourSubformControl.Form!txtTotalSales

where YourSubFormControl is the name of the subform control in the parent
form, i.e. the control which houses the subform, not the name of its
underlying form object (unless they both have the same name, of course).

Ken Sheridan
Stafford, England

I often wish that people would share how their database will be applied, so
here you go: I built a database to track e-bay items I've sold. There are
two tables, first one has primary key ebay item number with the details and
calculated listing fees to be input on a form. This form has sub-form for
data entry into second table which tracks items sold (could be several for
the same item number). I'd like, on the main form to keep a running total of
all the "Sold Price" 's in the Sold Items table that are related to the item
number currently being viewed in the main form. I'm very close with all the
other attributes of this database...could you please point me in the right
direction?

 

[Guest]

Yeah, they are pretty long! Glad you got it working.

Ken Sheridan
Stafford, England