I have found an Access Template that fit all requirements with a few exceptions.
Link to template:
http://office.microsoft.com/en-us/templates/goods-template-TC102483123.aspx
Exceptions:
1- Indicate taxable products
2- Store Order Retail Sales Sub Total
3- Store Order Service Sales Sub Total
4- Display Order Retail Sales Sub Total on Order Form
5- Order Retail Service Sales Sub Total on Order Form
6- Calculate tax only on taxable products
What I have done with my comments and questions:
I added a True/False bit named Taxable on the Product table and have updated several line items bit to thre.
I added the Taxable bit to the OrderDetailSubFrom. Now when I select a product the bit is displayed as True/False. Perfect.
I added two new fields in the Orders table named, OrderRetailSales and OrderServiceSales. Both are currency.
I added both new fields from the Orders table to the Orders Form above the SubTotal.
Name txtOrderServiceSales
Control Source OrderServiceSales
Name txtOrderRetailSales
Control Source OrderRetailSales
I created a query named RetailTaxableSales to be used in a macro named CalcRetailSubTotal
SELECT OrderDetails.OrderID, OrderDetails.ExtendedPrice
FROM Products INNER JOIN OrderDetails ON Products.ID = OrderDetails.ProductID;
In the Orders table I created two new macros via Table > Named Macro > Create Named Macro:
Macro -CalcRetailSubTotal
Parameters Name prmOrderID
/* Calculate new Order Retail Sales SubTotal /*
SetLocalVav
Name varOrderRetailSales
Expression = 0
For Each Record in RetailTaxableSales (the query noted above)
Where Condition = [OrderID]=[prmOrderID]
SetLocalVar
Name varOrderRetailSales
Expression = [varOrderRetailSales]+[ExtendedPrice]
SetReturnVar
Name retOrderRetailSales
Expression = [varOrderRetailSales]
Macro –UpdateRetailSalesSubTotal
Parameters Name prmOrderID
/* Calculate new Order Retail Sales Total /*
RunDataMacro
Macro Name Orders.CalcRetailSubTotal
SetLocalVar
Name varOrderRetailSales
Expression = [ReturnVars]![retOrdersRetailSales]
/* Store new Order Retail Sales Total/*
Look Up A Record in SELECT Orders.ID, Orders.OrderRetailSales FROM Orders;
Where Condition = [ID]=[prmOrderID]
If [varOrderRetailSales]<>[OrderRetailSales] Then
Edit Record
Aias
SetField
Name OrderRetailSales
Value = [varOrderRetailSales]
Listed under Objects I updated the macro named Orders with the following submacro
Submacro – UpdateRetailSalesSubTotal
/* Macro used to recalculating and updating Retail Sales SubTotal on Order Form/*
RunDataMacro Orders.CalcRetailSubTotal
Parmeters prmOrdersID = [txtID]
If [ReturnVars]![retOrderRetailSales]<>[txtOrderRetailSales] then
End If
End Submacro
I have spent a lot of time just trying to get the OrderRetailSales to display in the form when I select a product or have it stamp to the database but none of the above does that.
I think once I have that I could move on and calculate the tax on that field.
Does someone explain what I am doing wrong and guide me to a solution. Thanks, cindygo
Link to template:
http://office.microsoft.com/en-us/templates/goods-template-TC102483123.aspx
Exceptions:
1- Indicate taxable products
2- Store Order Retail Sales Sub Total
3- Store Order Service Sales Sub Total
4- Display Order Retail Sales Sub Total on Order Form
5- Order Retail Service Sales Sub Total on Order Form
6- Calculate tax only on taxable products
What I have done with my comments and questions:
I added a True/False bit named Taxable on the Product table and have updated several line items bit to thre.
I added the Taxable bit to the OrderDetailSubFrom. Now when I select a product the bit is displayed as True/False. Perfect.
I added two new fields in the Orders table named, OrderRetailSales and OrderServiceSales. Both are currency.
I added both new fields from the Orders table to the Orders Form above the SubTotal.
Name txtOrderServiceSales
Control Source OrderServiceSales
Name txtOrderRetailSales
Control Source OrderRetailSales
I created a query named RetailTaxableSales to be used in a macro named CalcRetailSubTotal
SELECT OrderDetails.OrderID, OrderDetails.ExtendedPrice
FROM Products INNER JOIN OrderDetails ON Products.ID = OrderDetails.ProductID;
In the Orders table I created two new macros via Table > Named Macro > Create Named Macro:
Macro -CalcRetailSubTotal
Parameters Name prmOrderID
/* Calculate new Order Retail Sales SubTotal /*
SetLocalVav
Name varOrderRetailSales
Expression = 0
For Each Record in RetailTaxableSales (the query noted above)
Where Condition = [OrderID]=[prmOrderID]
SetLocalVar
Name varOrderRetailSales
Expression = [varOrderRetailSales]+[ExtendedPrice]
SetReturnVar
Name retOrderRetailSales
Expression = [varOrderRetailSales]
Macro –UpdateRetailSalesSubTotal
Parameters Name prmOrderID
/* Calculate new Order Retail Sales Total /*
RunDataMacro
Macro Name Orders.CalcRetailSubTotal
SetLocalVar
Name varOrderRetailSales
Expression = [ReturnVars]![retOrdersRetailSales]
/* Store new Order Retail Sales Total/*
Look Up A Record in SELECT Orders.ID, Orders.OrderRetailSales FROM Orders;
Where Condition = [ID]=[prmOrderID]
If [varOrderRetailSales]<>[OrderRetailSales] Then
Edit Record
Aias
SetField
Name OrderRetailSales
Value = [varOrderRetailSales]
Listed under Objects I updated the macro named Orders with the following submacro
Submacro – UpdateRetailSalesSubTotal
/* Macro used to recalculating and updating Retail Sales SubTotal on Order Form/*
RunDataMacro Orders.CalcRetailSubTotal
Parmeters prmOrdersID = [txtID]
If [ReturnVars]![retOrderRetailSales]<>[txtOrderRetailSales] then
End If
End Submacro
I have spent a lot of time just trying to get the OrderRetailSales to display in the form when I select a product or have it stamp to the database but none of the above does that.
I think once I have that I could move on and calculate the tax on that field.
Does someone explain what I am doing wrong and guide me to a solution. Thanks, cindygo
