Subform based on a View

[Raptor_45]

Hi, running access and connected to an SQL Server database. I'm trying to create a subform that is based on a view that I made. The main form, I use a combo box to select the customer name and then store that key value and insert it into CustomerID in the SALES table. I want to do the same thing for the subform, I select the model of the product, then store that key value into product_id from SalesDetails. The reason I made a view for the subform is I want to create an alias called extended price that multiplies the quantity of the product model by the unit price.

It works but I want the model field to be a combo box so I can select from a list all the products. On a current record, it won't allow me to change it to something else, and if I make a new record, all the products are listed when I click the combo box but it can't select anything. Nothing happens when I click a product in the combo box.

I'm not sure what I'm doing wrong, maybe making the subform from the view is not the right way to do it? Not sure how else to calculate the extended price. I need to use the Convert(money function to make it currency. Here is what I did for the view:

SELECT dbo.SALESDETAILS.Quantity, dbo.SALESDETAILS.[Unit Price], CONVERT(Money, dbo.SALESDETAILS.Quantity * dbo.SALESDETAILS.[Unit Price])
AS ExtendedPrice, dbo.SALESDETAILS.SalesID, dbo.SALESDETAILS.Product_ID, dbo.PRODUCT.Model
FROM dbo.SALESDETAILS INNER JOIN
dbo.PRODUCT ON dbo.SALESDETAILS.Product_ID = dbo.PRODUCT.Product_ID

If you take a look at the picture I attached of the form, that's what I want, I just want the model field to be a combo box so I can select from the list in the PRODUCTS table, then insert that key into the SALES table.

[pbaldy]

I would probably not bother with the view. I'd bind the form to the table, use a textbox to display the extended price, and a combo box with a row source getting options from the products table. You can use this for the model:

http://www.baldyweb.com/Autofill.htm

[Raptor_45]

I would probably not bother with the view. I'd bind the form to the table, use a textbox to display the extended price, and a combo box with a row source getting options from the products table. You can use this for the model:

http://www.baldyweb.com/Autofill.htm

Well that works and if I make the text box and in the row source go: =[Quantity] * [Unit Price] it gives the the extended price. But how would I go about making a subtotal
box outside the subform? I looked at the northwind example and read some other information and it looks like I need to do something like this: =[SALESDETAILS Subform1].[Form]![OrderSubtotal] with ordersubtotal being an expression inside a view. I want subtotal to add all the totals from the subform and then I eventually want to add in freight and sales tax. Freight will be a column in SALESDETAILS table and sales tax will be another expression.

If I want to sum the extended prices from all the items in the subform, does the subform have to be based on a view with an expression that will do the summing? Because if I put a text box outside the subform and say: =SUM([Extended Price]) it doesn't work, since it's a calculated value. Thanks,

[pbaldy]

The typical way would be a textbox in the report footer:

=Sum([Quantity] * [Unit Price])

and then refer to that textbox from the main form. Another option is having that calculation in a query the subform is based on.

[Raptor_45]

Another option is having that calculation in a query the subform is based on.

I made a view and based the subform on it, and the view has a calculated field called ExtendedPrice. When I run the form it displays the ExtendedPrice corretly.
In the subform footer I put a textbox called Order Subtotal and I used the SUM function: =SUM([ExtendedPrice])

But when I make a subtotal textbox outside the subform it can't access it. Just says #Error, what do you need to do in order for it to refer to the textbox in the subform footer?

[Raptor_45]

Never mind, I made a mistake and was refering to the wrong subform name, that fixed it. Thanks!