Grabbing Data from a Subform

[MDM]

I have a Form that has an imbedded Subform used for order entry. These are
line item purchase transactions. Each line item has a bound field called
"Subtotal" that extends the line item price.

In the Subform Footer I have an unbound field called "Total Subtotal" that
sums the Subtotal fields as:=nz(Sum([Subtotal])).

I have a Total field bound to my main Form that is (supposed to) grab the
data from the Subform (Total Subtotal) and update this as: =Forms![Home
Survey Subform]![Total Subtotal].

However, all I get is #Name? I've compared this to a template DB from MS and
it is the same logic. I've also tried, =[Home Survey Subform].Form![Total
Subtotal] and get #Error.


Any idea why I can't seem to see the total price?

Any help is more than welcome...MDM

 

[Steve Schapel]

MDM,

=Forms![Home Survey Subform]![Total Subtotal] won't work, because the
Home Survey Subform form is not open... it is simply displayed via the
avenue of the subform control on the main form.

Try it like this...
=[Home Survey Subform]![Total Subtotal]
This assumes that Home Survey Subform is the name of the subform control
on the main form (as against the name of the form that populates it) -
these may be the same, but not necessarily so.

Some people feel it is more correct to do it like this...
=[Home Survey Subform].[Form]![Total Subtotal]
.... which I see you've tried. So here, another thing to check is that
the name of the Total textbox on the main form is not the same as any of
the fields in the form's record source table/query.

By the way, the Nz in the Total Subtotal probably doesn't achieve
anything useful, so I would dispense with that.

Also, a hint for the future, as regards terminology... forms don't have
fields, the word you need here is either "control" or "textbox" .

 

[MDM]

Hi Steve,

That didn't work either. But, I did find (by opening the subform
independently) that the sum error is occurring here. The Line Total
worksfine. The Total under the Footer of the Subform is simply
=Sum(Linetotal). Yet, the bottom of the form showsTotal as an #Error, so I
think this might be where it's coming from. Not sure why though.

Any thoughts?

Mike

MDM,

=Forms![Home Survey Subform]![Total Subtotal] won't work, because the
Home Survey Subform form is not open... it is simply displayed via the
avenue of the subform control on the main form.

Try it like this...
=[Home Survey Subform]![Total Subtotal]
This assumes that Home Survey Subform is the name of the subform control
on the main form (as against the name of the form that populates it) -
these may be the same, but not necessarily so.

Some people feel it is more correct to do it like this...
=[Home Survey Subform].[Form]![Total Subtotal]
.... which I see you've tried. So here, another thing to check is that
the name of the Total textbox on the main form is not the same as any of
the fields in the form's record source table/query.

By the way, the Nz in the Total Subtotal probably doesn't achieve
anything useful, so I would dispense with that.

Also, a hint for the future, as regards terminology... forms don't have
fields, the word you need here is either "control" or "textbox" .

--
Steve Schapel, Microsoft Access MVP

I have a Form that has an imbedded Subform used for order entry. These are
line item purchase transactions. Each line item has a bound field called
"Subtotal" that extends the line item price.

In the Subform Footer I have an unbound field called "Total Subtotal" that
sums the Subtotal fields as:=nz(Sum([Subtotal])).

I have a Total field bound to my main Form that is (supposed to) grab the
data from the Subform (Total Subtotal) and update this as: =Forms![Home
Survey Subform]![Total Subtotal].

However, all I get is #Name? I've compared this to a template DB from MS and
it is the same logic. I've also tried, =[Home Survey Subform].Form![Total
Subtotal] and get #Error.


Any idea why I can't seem to see the total price?

Any help is more than welcome...MDM

 

[Steve Schapel]

Mike,

What's in the Line Total textbox? Is that the same as what you
originally referred to as Subtotal? (If so, are you deliberately trying
to confuse me <g>?) I had assumed it was bound to a field in the form's
underlying query, as that was how you originally described it. If your
Line Total is a calculated control on the subform itself, i.e. you have
a Control Source setting somethign like...
=[UnitPrice]*[Quantity]
.... then the total control in the Footer will have to do like this...
=Sum([UnitPrice]*[Quantity])

 

[MDM]

Thanks Steve.

I went back and retitled my fields and changed the Footer total to what you
suggest on Homw Survey Subform. I now can see a total on from the Footer
control (I'll get used to writing this) if I open the Home Survey Subform
directly. I don't know if it's the right total, but a total nontheless.

I checked the Total field on the main Home Survey Form and no more #Error or
#Name$. I get nothing, blank, nada. Don't know if that's a good sign, but I
suspect not.

I'm wondering could I have something set wrong on the Form settings
themselves?

MDM

Mike,

What's in the Line Total textbox? Is that the same as what you
originally referred to as Subtotal? (If so, are you deliberately trying
to confuse me <g>?) I had assumed it was bound to a field in the form's
underlying query, as that was how you originally described it. If your
Line Total is a calculated control on the subform itself, i.e. you have
a Control Source setting somethign like...
=[UnitPrice]*[Quantity]
.... then the total control in the Footer will have to do like this...
=Sum([UnitPrice]*[Quantity])

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

That didn't work either. But, I did find (by opening the subform
independently) that the sum error is occurring here. The Line Total
worksfine. The Total under the Footer of the Subform is simply
=Sum(Linetotal). Yet, the bottom of the form showsTotal as an #Error, so I
think this might be where it's coming from. Not sure why though.

 

[MDM]

Steve

I've narrowed the problem down to the Footer total in the Subform. I've
tried every combination of ways to total the LineTotals, but so far no luck.
Right now I have this set to Sum([UnitPrice]*UnitsSold]) and get blank
results. Not even a zero!

I've also tried (again) =Sum([LineTotals]) and get #Error, and a few other
ways and get the same.

I'm baffled.

Also, here's something interesting...I noticed the UnitPrice coming over
from the Product table was showing as an Integer instead of Currency although
using UnitPrice in the calculations showed accurate data. I checked all the
control formats and all are set to Currency. For the heck, I added *100 to
the UnitPrice field (this is part of the ProductID lookup) and got properly
formatted currency data (although times 100). So I dropped this to *10 and it
still displays correct. I ended up with *1 in order to get the UnitPrice
control to display currency properly. Ever heard of this?

I'm just about tried out. I've been pouring over an Order Entry database
template that came with access. It uses the same (or almost the same) methods
I am, but the template works fine and mine doesn't.

I no other suggestions then I thank you for your time and help. I guess I'll
just keep trying to figure this out. It is the last holdup on final delivery,
so somehow I have to find the answer...MDM

Mike,

What's in the Line Total textbox? Is that the same as what you
originally referred to as Subtotal? (If so, are you deliberately trying
to confuse me <g>?) I had assumed it was bound to a field in the form's
underlying query, as that was how you originally described it. If your
Line Total is a calculated control on the subform itself, i.e. you have
a Control Source setting somethign like...
=[UnitPrice]*[Quantity]
.... then the total control in the Footer will have to do like this...
=Sum([UnitPrice]*[Quantity])

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

That didn't work either. But, I did find (by opening the subform
independently) that the sum error is occurring here. The Line Total
worksfine. The Total under the Footer of the Subform is simply
=Sum(Linetotal). Yet, the bottom of the form showsTotal as an #Error, so I
think this might be where it's coming from. Not sure why though.

 

[Steve Schapel]

Mike,

Sum([UnitPrice]*UnitsSold]) is missing a = and a [ which I presume is a
typo in your post here as against an indication of an error on your
form. Should be...
=Sum([UnitPrice]*[UnitsSold])

So, do I have this right... You have a control in the Detail section
which is called LineTotals and the Control Source of this is...
=[UnitPrice]*[UnitsSold]
.... and this is showing the correct data, right? And UnitPrice and
UnitsSold are both the correct names of fields in the query that the
subform is based on, right?

And then, in the footer of the subform, a textbox with Control Source
set to =Sum([UnitPrice]*[UnitsSold]) is blank?

As regards the other conundrum, with the UnitPrice, can you explain what
you mean about "this is part of the ProductID lookup"?

Don't worry, we'll crack this eventually!

 

[MDM]

Steve

You are absolutely correct on your assumption below. This is my exact setup.
Now, here's something I just looked into.

The ProductID, ProductName, and UnitPrice are dervied from the Products
table through a combo lookup for ProductID.

The lookup works fine, but UnitPrice is acting funky. It almost seems it's
coming over as text. For instance, $15.00 shows as 15, $150.00 shows as 150.
The Line Item Total extends fine, but the only way I've been able to get the
UnitPrice to show as currency is to multiply this by 1 (huh?). So, my
UnitPrice filed in the Home Survey Subform (really the Inventory Transactions
table) is =ProductID.Column(2)*1!! If I take the "*1" off the UnitPrice it
shows as an Integer. Also, I removed the above and set the control to the
UnitPrice field to UnitPrice and manually entered the UnitPrice myself. When
I do this, the Total in the Footer section works perfect! But when I go back
to =ProductID.Column(2) it no longer works.

I wish I could show you snipits. Is there a way to include grahics here
somewhere?

So, to recap:

My Products table holds the ProductID, ProductName, and UnitPrice. The
UnitPrice control is setup as Currency and displays properly.

My Home Survey Subform is using the Inventory Transaction table and all
controls are work perfectly.

The ProductID control within the Home Survey Subform is a combo box with a
lookup to Products. It brings back ProductName and UnitPrice. The ProductID
control is set to bound to column 1 (ProductID).

The UnitPrice within the Home Survey Subform loads fine, but regardless of
what Format I set this to it is intent to display as an Integer instead of
Currency.

For some unknown reason, the Total Line extension (=[UnitsSold]*[UnitPrice])
works perfect even though it is showing as an Integer. However, the Subform
Footer Total (=Sum([UnitsSold] * [UnitPrice]) refuses to calculate and
displays as "blank".

The UnitPrice is being stored in the Inventory Transactions file correctly
(such as $15.00 is stored as $15.00).

This is getting spooky. I cannot seem to figure out why the UnitPrice is
being such a booger. I've used this on my prior design (before you convinced
me to take a different route to managing OnHand balances) and it worked fine.

I've never, ever had so much trouble with a system like this before, albeit
new to Access.

Arrggghhhh!! MDM

Mike,

Sum([UnitPrice]*UnitsSold]) is missing a = and a [ which I presume is a
typo in your post here as against an indication of an error on your
form. Should be...
=Sum([UnitPrice]*[UnitsSold])

So, do I have this right... You have a control in the Detail section
which is called LineTotals and the Control Source of this is...
=[UnitPrice]*[UnitsSold]
.... and this is showing the correct data, right? And UnitPrice and
UnitsSold are both the correct names of fields in the query that the
subform is based on, right?

And then, in the footer of the subform, a textbox with Control Source
set to =Sum([UnitPrice]*[UnitsSold]) is blank?

As regards the other conundrum, with the UnitPrice, can you explain what
you mean about "this is part of the ProductID lookup"?

Don't worry, we'll crack this eventually!

--
Steve Schapel, Microsoft Access MVP

Steve

I've narrowed the problem down to the Footer total in the Subform. I've
tried every combination of ways to total the LineTotals, but so far no luck.
Right now I have this set to Sum([UnitPrice]*UnitsSold]) and get blank
results. Not even a zero!

I've also tried (again) =Sum([LineTotals]) and get #Error, and a few other
ways and get the same.

I'm baffled.

Also, here's something interesting...I noticed the UnitPrice coming over
from the Product table was showing as an Integer instead of Currency although
using UnitPrice in the calculations showed accurate data. I checked all the
control formats and all are set to Currency. For the heck, I added *100 to
the UnitPrice field (this is part of the ProductID lookup) and got properly
formatted currency data (although times 100). So I dropped this to *10 and it
still displays correct. I ended up with *1 in order to get the UnitPrice
control to display currency properly. Ever heard of this?

I'm just about tried out. I've been pouring over an Order Entry database
template that came with access. It uses the same (or almost the same) methods
I am, but the template works fine and mine doesn't.

I no other suggestions then I thank you for your time and help. I guess I'll
just keep trying to figure this out. It is the last holdup on final delivery,
so somehow I have to find the answer...MDM

 

[Steve Schapel]

MDM,

Ok, so the [UnitPrice] control on the form is a calculated control (i.e.
derived from the ProductID combobox via its Column property). That puts
another perspective on it. You can't use a calculated field in a
further aggregate calculation, you always have to go back to base data.

Try your total in the footer like this...
=([ProductID].Column(2)*[UnitsSold])

However, I myself would avoid this approach. I would include the
Products table directly in the query that the form is based on, in which
case you will have the UnitPrice field available to bind to a textbox on
the form. The calculations will be simpler that way. The only caution
with this approach is to make the UnitPrice's Locked property set to Yes
and Enabled set to No - this is not the place for the user to have
access to editing the product prices!

This is the first approach explained in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm

Let me know.

As regards "Arrggghhhh!!", I know exactly what you mean. As far as I
know, that's the only way to learn this stuff ;-).

--
Steve Schapel, Microsoft Access MVP

Steve

You are absolutely correct on your assumption below. This is my exact setup.
Now, here's something I just looked into.

The ProductID, ProductName, and UnitPrice are dervied from the Products
table through a combo lookup for ProductID.

The lookup works fine, but UnitPrice is acting funky. It almost seems it's
coming over as text. For instance, $15.00 shows as 15, $150.00 shows as 150.
The Line Item Total extends fine, but the only way I've been able to get the
UnitPrice to show as currency is to multiply this by 1 (huh?). So, my
UnitPrice filed in the Home Survey Subform (really the Inventory Transactions
table) is =ProductID.Column(2)*1!! If I take the "*1" off the UnitPrice it
shows as an Integer. Also, I removed the above and set the control to the
UnitPrice field to UnitPrice and manually entered the UnitPrice myself. When
I do this, the Total in the Footer section works perfect! But when I go back
to =ProductID.Column(2) it no longer works.

I wish I could show you snipits. Is there a way to include grahics here
somewhere?

So, to recap:

My Products table holds the ProductID, ProductName, and UnitPrice. The
UnitPrice control is setup as Currency and displays properly.

My Home Survey Subform is using the Inventory Transaction table and all
controls are work perfectly.

The ProductID control within the Home Survey Subform is a combo box with a
lookup to Products. It brings back ProductName and UnitPrice. The ProductID
control is set to bound to column 1 (ProductID).

The UnitPrice within the Home Survey Subform loads fine, but regardless of
what Format I set this to it is intent to display as an Integer instead of
Currency.

For some unknown reason, the Total Line extension (=[UnitsSold]*[UnitPrice])
works perfect even though it is showing as an Integer. However, the Subform
Footer Total (=Sum([UnitsSold] * [UnitPrice]) refuses to calculate and
displays as "blank".

The UnitPrice is being stored in the Inventory Transactions file correctly
(such as $15.00 is stored as $15.00).

This is getting spooky. I cannot seem to figure out why the UnitPrice is
being such a booger. I've used this on my prior design (before you convinced
me to take a different route to managing OnHand balances) and it worked fine.

I've never, ever had so much trouble with a system like this before, albeit
new to Access.

Arrggghhhh!! MDM

 

[MDM]

Steve

You wrote:

"I would include the Products table directly in the query that the form is
based on,"

I tried the other route and get #Error. How do I include the Products table
directly in the query?

Boy, I may have to go back tot he PICK RDBMS! MDM

MDM,

Ok, so the [UnitPrice] control on the form is a calculated control (i.e.
derived from the ProductID combobox via its Column property). That puts
another perspective on it. You can't use a calculated field in a
further aggregate calculation, you always have to go back to base data.

Try your total in the footer like this...
=([ProductID].Column(2)*[UnitsSold])

However, I myself would avoid this approach. I would include the
Products table directly in the query that the form is based on, in which
case you will have the UnitPrice field available to bind to a textbox on
the form. The calculations will be simpler that way. The only caution
with this approach is to make the UnitPrice's Locked property set to Yes
and Enabled set to No - this is not the place for the user to have
access to editing the product prices!

This is the first approach explained in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm

Let me know.

As regards "Arrggghhhh!!", I know exactly what you mean. As far as I
know, that's the only way to learn this stuff ;-).

--
Steve Schapel, Microsoft Access MVP

Steve

You are absolutely correct on your assumption below. This is my exact setup.
Now, here's something I just looked into.

The ProductID, ProductName, and UnitPrice are dervied from the Products
table through a combo lookup for ProductID.

The lookup works fine, but UnitPrice is acting funky. It almost seems it's
coming over as text. For instance, $15.00 shows as 15, $150.00 shows as 150.
The Line Item Total extends fine, but the only way I've been able to get the
UnitPrice to show as currency is to multiply this by 1 (huh?). So, my
UnitPrice filed in the Home Survey Subform (really the Inventory Transactions
table) is =ProductID.Column(2)*1!! If I take the "*1" off the UnitPrice it
shows as an Integer. Also, I removed the above and set the control to the
UnitPrice field to UnitPrice and manually entered the UnitPrice myself. When
I do this, the Total in the Footer section works perfect! But when I go back
to =ProductID.Column(2) it no longer works.

I wish I could show you snipits. Is there a way to include grahics here
somewhere?

So, to recap:

My Products table holds the ProductID, ProductName, and UnitPrice. The
UnitPrice control is setup as Currency and displays properly.

My Home Survey Subform is using the Inventory Transaction table and all
controls are work perfectly.

The ProductID control within the Home Survey Subform is a combo box with a
lookup to Products. It brings back ProductName and UnitPrice. The ProductID
control is set to bound to column 1 (ProductID).

The UnitPrice within the Home Survey Subform loads fine, but regardless of
what Format I set this to it is intent to display as an Integer instead of
Currency.

For some unknown reason, the Total Line extension (=[UnitsSold]*[UnitPrice])
works perfect even though it is showing as an Integer. However, the Subform
Footer Total (=Sum([UnitsSold] * [UnitPrice]) refuses to calculate and
displays as "blank".

The UnitPrice is being stored in the Inventory Transactions file correctly
(such as $15.00 is stored as $15.00).

This is getting spooky. I cannot seem to figure out why the UnitPrice is
being such a booger. I've used this on my prior design (before you convinced
me to take a different route to managing OnHand balances) and it worked fine.

I've never, ever had so much trouble with a system like this before, albeit
new to Access.

Arrggghhhh!! MDM

 

[Steve Schapel]

MDM,

Erk! Sorry, too much rush... I meant
=Sum([ProductID].Column(2)*[UnitsSold])
Not sure that this will work - never tried it!

Ok, at the moment, the Record Source of the subform is the Inventory
Transactions table, right?

Well, make a query, including the Inventory Transactions table and the
Products table, joined on the ProductID field from both. Add all fields
from the Inventory Transactions table, and also the UnitPrice field from
the Products table, in the query. And then make this query the Record
Source of the subform instead.

But now here's another thing I've noticed... now you mentioned that "the
UnitPrice is being stored in the Inventory Transactions file correctly".
Sorry, I didn't pick this up before. Do you have a UnitPrice field in
the Inventory Transaction table? So by what mechanism is the data being
entered in there? Your UnitPrice control on the subform is the one with
the [ProductID].Column(2) expression in it, right? So, you have a
control on the form named UnitPrice, and a field in the form's Record
Source, also named UnitPrice, but the UnitPrice control is not bound to
the UnitPrice field, right? Well, this won't work, and is probably
contributing to the problem. But I can't see how any data at all is
going into the UnitPrice field.

Under normal circumstances, it would not be valid to have a UnitPrice
field in the Inventory Transactions table anyway. However, it's not as
simple as that (in PICK or in Access!). If the UnitPrice in the
Products table for a given ProductID may change over time, and if the
historical records in the Inventory Transactions table need to show a
UnitPrice as at the time of the transaction, regardless of subsequent
price fluctuations, then some people would put a UnitPrice field in the
Inventory Transactions table to store this value. If so, you need to
change the design of the form, as all you want is the current Product
UnitPrice for the product being offered as the default value for the
Inventory Transaction UnitPrice for the product. In any case, I would
probably not do it like that. Instead, probably a more "correct"
approach would be to have a ProductPrices table, that records the price
changes for each product, with date of price update, and then use this
to determine the UnitPrice for any given product on any given
transaction date. Sorry, Mike, this has nothing to do with Access, it
has to do with the real-world data relationships that exist in trading
scenarios. So, hopefully you will come back and say that the Products'
UnitPrice are fixed, and we don't have to worry about price changes. If
that's the case, remove the UnitPrice field from the Inventory
Transactiosn table, and many of your worries will evaporate.

 

[MDM]

Okay Steve, I simplified the form.

I removed the UnitPrice from the Inventory Trans table, but I am still
getting #Error on the Footer total. Also, the same thing is happening to the
Unitprice coming over from the Products tabe: it's not showing as currency,
but an Integer. So 15.00 (or $15.00) in the Products table is coming over as
15 in the subform. I can't shake the feeling that this is causing the #Error,
but then when I remove the Sum() I get a proper extension.

Using one Product as an example. UnitPrice = $15.00 and UnitsSold = 2:

Control Value
Result
--------------------------------------------------------------------------------------
Price ProductID.Column(2)
15 (wrong)
Price ProductID.Column(2)*1
$15.00 (correct)
LineTotal ProductID.Column(2)*[UnitsSold] $30.00
(correct)
Footer Total Sum(ProductID.Column(2)*[UnitsSold]) #Error (wrong)
Footer Total Sum(Price*[UnitsSold])
#Error (wrong)
Footer Total [Price]*[UnitsSold] (same as LineTotal) $30.00
Footer Total Sum([Price])*Sum([Units.Sold]) #Error
Footer Total Sum(ProductID.Column(2))*Sum([UnitsSold]) #Error

Could my problem be related to grouping?
I'm still not clear why UnitPrice is coming over as an Integer. Any thoughts
on this?

All else has been simplified and working fine. I added Price back in just
to watch the data, but even without Price showing I still get the darn #Error
in Footer Total.

MDM

MDM,

Erk! Sorry, too much rush... I meant
=Sum([ProductID].Column(2)*[UnitsSold])
Not sure that this will work - never tried it!

Ok, at the moment, the Record Source of the subform is the Inventory
Transactions table, right?

Well, make a query, including the Inventory Transactions table and the
Products table, joined on the ProductID field from both. Add all fields
from the Inventory Transactions table, and also the UnitPrice field from
the Products table, in the query. And then make this query the Record
Source of the subform instead.

But now here's another thing I've noticed... now you mentioned that "the
UnitPrice is being stored in the Inventory Transactions file correctly".
Sorry, I didn't pick this up before. Do you have a UnitPrice field in
the Inventory Transaction table? So by what mechanism is the data being
entered in there? Your UnitPrice control on the subform is the one with
the [ProductID].Column(2) expression in it, right? So, you have a
control on the form named UnitPrice, and a field in the form's Record
Source, also named UnitPrice, but the UnitPrice control is not bound to
the UnitPrice field, right? Well, this won't work, and is probably
contributing to the problem. But I can't see how any data at all is
going into the UnitPrice field.

Under normal circumstances, it would not be valid to have a UnitPrice
field in the Inventory Transactions table anyway. However, it's not as
simple as that (in PICK or in Access!). If the UnitPrice in the
Products table for a given ProductID may change over time, and if the
historical records in the Inventory Transactions table need to show a
UnitPrice as at the time of the transaction, regardless of subsequent
price fluctuations, then some people would put a UnitPrice field in the
Inventory Transactions table to store this value. If so, you need to
change the design of the form, as all you want is the current Product
UnitPrice for the product being offered as the default value for the
Inventory Transaction UnitPrice for the product. In any case, I would
probably not do it like that. Instead, probably a more "correct"
approach would be to have a ProductPrices table, that records the price
changes for each product, with date of price update, and then use this
to determine the UnitPrice for any given product on any given
transaction date. Sorry, Mike, this has nothing to do with Access, it
has to do with the real-world data relationships that exist in trading
scenarios. So, hopefully you will come back and say that the Products'
UnitPrice are fixed, and we don't have to worry about price changes. If
that's the case, remove the UnitPrice field from the Inventory
Transactiosn table, and many of your worries will evaporate.

--
Steve Schapel, Microsoft Access MVP

Steve

You wrote:

"I would include the Products table directly in the query that the form is
based on,"

I tried the other route and get #Error. How do I include the Products table
directly in the query?

Boy, I may have to go back tot he PICK RDBMS! MDM

 

[Steve Schapel]

MDM,

Thanks for your persistence.

Sorry for the misunderstanding. The whole point of my earlier
suggestion was to avoid trying to use the Column property of the
combobox in a calculation. Did you add the Products table to the query
that the form is based on? The Price control is then bound to (i.e. its
Control Source is) the UnitPrice field from the query.

Then, the correct option in the form footer would be...
=Sum([UnitPrice]*[UnitsSold])

By the way, what is the name of the control in the form footer?

 

[MDM]

Steve

My original query in the Row Source within the ProductID combo box reads:,
"SELECT DISTINCTROW Products.ProductID, Products.ProductName,
Products.UnitPrice FROM Products ORDER BY Products.ProductName; "

Now, I played with adding controls from the Inventory Transactions table,
but got weird results, so I'm not sure I did this right.

I have removed the UnitPrice field from my Inventory Transactions table and
am using an unbound control for Price instead (or trying to). Should I put
the Price control back into the Inventory Transactions table?

- Am I using the query in the combo box correctly?
- Do I save the query and give it a name?
- How do I call the query up when I open the Home Survey form?
- Or should it open only in the Subform?

The control source for the Footer total is "TotalLine".

And yes, I am nothing but persistent...but I might say the same for you.
Thanks for hanging in there with me...

Mike

MDM,

Thanks for your persistence.

Sorry for the misunderstanding. The whole point of my earlier
suggestion was to avoid trying to use the Column property of the
combobox in a calculation. Did you add the Products table to the query
that the form is based on? The Price control is then bound to (i.e. its
Control Source is) the UnitPrice field from the query.

Then, the correct option in the form footer would be...
=Sum([UnitPrice]*[UnitsSold])

By the way, what is the name of the control in the form footer?

--
Steve Schapel, Microsoft Access MVP

Okay Steve, I simplified the form.

I removed the UnitPrice from the Inventory Trans table, but I am still
getting #Error on the Footer total. Also, the same thing is happening to the
Unitprice coming over from the Products tabe: it's not showing as currency,
but an Integer. So 15.00 (or $15.00) in the Products table is coming over as
15 in the subform. I can't shake the feeling that this is causing the #Error,
but then when I remove the Sum() I get a proper extension.

Using one Product as an example. UnitPrice = $15.00 and UnitsSold = 2:

Control Value
Result
--------------------------------------------------------------------------------------
Price ProductID.Column(2)
15 (wrong)
Price ProductID.Column(2)*1
$15.00 (correct)
LineTotal ProductID.Column(2)*[UnitsSold] $30.00
(correct)
Footer Total Sum(ProductID.Column(2)*[UnitsSold]) #Error (wrong)
Footer Total Sum(Price*[UnitsSold])
#Error (wrong)
Footer Total [Price]*[UnitsSold] (same as LineTotal) $30.00
Footer Total Sum([Price])*Sum([Units.Sold]) #Error
Footer Total Sum(ProductID.Column(2))*Sum([UnitsSold]) #Error

Could my problem be related to grouping?
I'm still not clear why UnitPrice is coming over as an Integer. Any thoughts
on this?

All else has been simplified and working fine. I added Price back in just
to watch the data, but even without Price showing I still get the darn #Error
in Footer Total.

 

[MDM]

Steve

All works PERFECTLY if I enter UnitPrice manually into the subform. What
gives?

Mike

MDM,

Thanks for your persistence.

Sorry for the misunderstanding. The whole point of my earlier
suggestion was to avoid trying to use the Column property of the
combobox in a calculation. Did you add the Products table to the query
that the form is based on? The Price control is then bound to (i.e. its
Control Source is) the UnitPrice field from the query.

Then, the correct option in the form footer would be...
=Sum([UnitPrice]*[UnitsSold])

By the way, what is the name of the control in the form footer?

--
Steve Schapel, Microsoft Access MVP

Okay Steve, I simplified the form.

I removed the UnitPrice from the Inventory Trans table, but I am still
getting #Error on the Footer total. Also, the same thing is happening to the
Unitprice coming over from the Products tabe: it's not showing as currency,
but an Integer. So 15.00 (or $15.00) in the Products table is coming over as
15 in the subform. I can't shake the feeling that this is causing the #Error,
but then when I remove the Sum() I get a proper extension.

Using one Product as an example. UnitPrice = $15.00 and UnitsSold = 2:

Control Value
Result
--------------------------------------------------------------------------------------
Price ProductID.Column(2)
15 (wrong)
Price ProductID.Column(2)*1
$15.00 (correct)
LineTotal ProductID.Column(2)*[UnitsSold] $30.00
(correct)
Footer Total Sum(ProductID.Column(2)*[UnitsSold]) #Error (wrong)
Footer Total Sum(Price*[UnitsSold])
#Error (wrong)
Footer Total [Price]*[UnitsSold] (same as LineTotal) $30.00
Footer Total Sum([Price])*Sum([Units.Sold]) #Error
Footer Total Sum(ProductID.Column(2))*Sum([UnitsSold]) #Error

Could my problem be related to grouping?
I'm still not clear why UnitPrice is coming over as an Integer. Any thoughts
on this?

All else has been simplified and working fine. I added Price back in just
to watch the data, but even without Price showing I still get the darn #Error
in Footer Total.

 

[Steve Schapel]

Mike,

My original query in the Row Source within the ProductID combo box reads:,
"SELECT DISTINCTROW Products.ProductID, Products.ProductName,
Products.UnitPrice FROM Products ORDER BY Products.ProductName; "

DISTINCTROW's not necessary. Nor is UnitPrice. Do it like this...

SELECT Products.ProductID, Products.ProductName
FROM Products ORDER BY Products.ProductName

I have removed the UnitPrice field from my Inventory Transactions table and
am using an unbound control for Price instead (or trying to). Should I put
the Price control back into the Inventory Transactions table?

No. Don't put a Price *field* in the Inventory Transactions table.

The Record Source of the subform should be a query that looks something
like this...
SELECT [Inventory Transactions].ProductID, [Inventory
Transactions].UnitsSold, Products.UnitPrice
FROM [Inventory Transactions] INNER JOIN Products ON [Inventory
Transactions].ProductID = Products.ProductID

In the detail section of the form, the Control Source of the total
control will be like this...
=[UnitsSold]*[UnitPrice]

In the footer, the Control Source of the textbox named TotalLine will be
like this...
=Sum([UnitsSold]*[UnitPrice])

 

[MDM]

YOU ARE A SAINT!

It works. I couldn't figure out where to access the Record Source and then
it finally dawned on me...Check the Help. Once I found the source box I
quickly setup the tables and fields I needed (you suggested) and BINGO. It
works beautiful.

Now who says you can't teach an old dog new tricks!

You're the best Steve and I really appreciate your patience and perseverance
in helping me through this.

Bless you!

Mike

Mike,

My original query in the Row Source within the ProductID combo box reads:,
"SELECT DISTINCTROW Products.ProductID, Products.ProductName,
Products.UnitPrice FROM Products ORDER BY Products.ProductName; "

DISTINCTROW's not necessary. Nor is UnitPrice. Do it like this...

SELECT Products.ProductID, Products.ProductName
FROM Products ORDER BY Products.ProductName

I have removed the UnitPrice field from my Inventory Transactions table and
am using an unbound control for Price instead (or trying to). Should I put
the Price control back into the Inventory Transactions table?

No. Don't put a Price *field* in the Inventory Transactions table.

The Record Source of the subform should be a query that looks something
like this...
SELECT [Inventory Transactions].ProductID, [Inventory
Transactions].UnitsSold, Products.UnitPrice
FROM [Inventory Transactions] INNER JOIN Products ON [Inventory
Transactions].ProductID = Products.ProductID

In the detail section of the form, the Control Source of the total
control will be like this...
=[UnitsSold]*[UnitPrice]

In the footer, the Control Source of the textbox named TotalLine will be
like this...
=Sum([UnitsSold]*[UnitPrice])

 

[Steve Schapel]

Cheers, Mike. Another hurdle cleared - great news!

--
Steve Schapel, Microsoft Access MVP

YOU ARE A SAINT!

It works. I couldn't figure out where to access the Record Source and then
it finally dawned on me...Check the Help. Once I found the source box I
quickly setup the tables and fields I needed (you suggested) and BINGO. It
works beautiful.

Now who says you can't teach an old dog new tricks!

You're the best Steve and I really appreciate your patience and perseverance
in helping me through this.

Bless you!

Mike

Mike,

My original query in the Row Source within the ProductID combo box reads:,
"SELECT DISTINCTROW Products.ProductID, Products.ProductName,
Products.UnitPrice FROM Products ORDER BY Products.ProductName; "

DISTINCTROW's not necessary. Nor is UnitPrice. Do it like this...

SELECT Products.ProductID, Products.ProductName
FROM Products ORDER BY Products.ProductName

I have removed the UnitPrice field from my Inventory Transactions table and
am using an unbound control for Price instead (or trying to). Should I put
the Price control back into the Inventory Transactions table?

No. Don't put a Price *field* in the Inventory Transactions table.

The Record Source of the subform should be a query that looks something
like this...
SELECT [Inventory Transactions].ProductID, [Inventory
Transactions].UnitsSold, Products.UnitPrice
FROM [Inventory Transactions] INNER JOIN Products ON [Inventory
Transactions].ProductID = Products.ProductID

In the detail section of the form, the Control Source of the total
control will be like this...
=[UnitsSold]*[UnitPrice]

In the footer, the Control Source of the textbox named TotalLine will be
like this...
=Sum([UnitsSold]*[UnitPrice])

 

[s_wadhwa]

Hi,

Can anyone can answer my query that how we can set Data on Subform from
a recordset.

when I'm trying to set it like

Set frm1 = Forms("fsubroomsdept")
Set frm1.Recordset = rstQry
str1 = rstQry!BuildingName
Do While rstQry.EOF <> True
For Each ctl1 In frm1
If TypeOf ctl1 Is TextBox Or TypeOf ctl1 Is ComboBox Then
Select Case ctl1.Name
Case "BuildingName"
ctl1.SetFocus
ctl1.Value = str1
Case "buildingNumber"
ctl1.SetFocus
ctl1.Value = str2
Case Else
Exit Sub
End Select
End If
Next
rstQry.MoveNext
Loop

it throws an error saying that you can't set a value its read only.

please help any comments/ suggestions are welcome.

thanks,
Shalini

Cheers, Mike. Another hurdle cleared - great news!

--
Steve Schapel, Microsoft Access MVP

YOU ARE A SAINT!

It works. I couldn't figure out where to access the Record Source and then
it finally dawned on me...Check the Help. Once I found the source box I
quickly setup the tables and fields I needed (you suggested) and BINGO. It
works beautiful.

Now who says you can't teach an old dog new tricks!

You're the best Steve and I really appreciate your patience and perseverance
in helping me through this.

Bless you!

Mike

Mike,

MDM wrote:

My original query in the Row Source within the ProductID combo box reads:,
"SELECT DISTINCTROW Products.ProductID, Products.ProductName,
Products.UnitPrice FROM Products ORDER BY Products.ProductName; "
DISTINCTROW's not necessary. Nor is UnitPrice. Do it like this...

SELECT Products.ProductID, Products.ProductName
FROM Products ORDER BY Products.ProductName

I have removed the UnitPrice field from my Inventory Transactions table and
am using an unbound control for Price instead (or trying to). Should I put
the Price control back into the Inventory Transactions table?
No. Don't put a Price *field* in the Inventory Transactions table.

The Record Source of the subform should be a query that looks something
like this...
SELECT [Inventory Transactions].ProductID, [Inventory
Transactions].UnitsSold, Products.UnitPrice
FROM [Inventory Transactions] INNER JOIN Products ON [Inventory
Transactions].ProductID = Products.ProductID

In the detail section of the form, the Control Source of the total
control will be like this...
=[UnitsSold]*[UnitPrice]

In the footer, the Control Source of the textbox named TotalLine will be
like this...
=Sum([UnitsSold]*[UnitPrice])