error in standard MS template. please help

[martin]

Hello,

Today I fetched the standard Access-2003 template "orders management db"
from
http://office.microsoft.com/en-au/templates/TC010185481033.aspx?pid=CT101426031033

In de openingform one sees a column "extended price"
Perhaps I dont understand this too well, but shouldn't this be something
like quantity*unitprice?

Then why does it say that 15 times $5,- equals $150,- ??

And more important: how do I repair this? (I'm only a rookie, so please when
one has some details for me... ) ;-)

kind regards,

martin
- the netherlands

 

[Guest]

Hi Martin,

That's called Microsoft Math! Check out messages #2 and 3 in this thread
for a fix:

http://groups.google.com/group/micr..._frm/thread/4d4868a33986ceb6/b6adcfb9bd871efd


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jeff Smith]

Go and open the Northwind database, which is located in the "Samples" folder
under your microsoft office installation, and have a look at the
recordsource for the subform for the orders. In the template you downloaded
they are storing the calculated value and not using a query to calculate the
value on the fly.

 

[Arvin Meyer [MVP]]

Yes, it is a bug. But the bug is in the Microsoft employee who is unable to
multiply. The Extended Price field is manually filled in.

That is also a bug in the employees' ability to do database design. There is
no reason to store the results of a calculation when the computer can easily
recalculate at will. As a matter of good design the form should be based
upon a query, not directly upon the underlying table. Had the designer done
that, the Extended Price calculation could be done as a query column and
would be done correctly instead of allowing a user to make a mistake.

If you decide to fix it by adding a query, the added column should look
like:

Extended Price:
CCur([Quantity]*[UnitPrice]-([Quantity]*[UnitPrice]*[Discount]))

And the query should look like:

SELECT [Order Details].OrderDetailID, [Order Details].ProductID, [Order
Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount,
CCur([Quantity]*[UnitPrice]-([Quantity]*[UnitPrice]*[Discount])) AS
[Extended Price]
FROM [Order Details];

And if you really want to do it properly, you'll want to get rid of the
Lookup Fields and replace it with a lookup table and do a join in the query:

http://www.mvps.org/access/lookupfields.htm

 

[martin]

Thanx a lot, Tom!

I followed the instructions and it works now...
JUST until the moment I want to add a new order!
A pop-up then complains that I first have to enter a "PO-number". :-(

What is that? Is an end-user supposed to actualize some key-number?? Can
this perhaps be made to increment automatically.. and if so: how?

Another question would be:
I'd like to have the items which are OUT of stock, to be mentioned in red
font (in the Orders detail subform). Now I know this has to do something
with "conditional layout", but what exactly must be done to achieve this?

Kind regards
martin

 

[Guest]

Hi Martin,

On second thought, change the recordsource for the Order Details subform so
that it includes the Nz function. This will prevent the #Error that one sees
when they select a new item, but before they've entered the quantity:

SELECT OrderID, ProductID, Quantity, UnitPrice, Discount,
CCur(Nz([UnitPrice]*[Quantity]*(1-[Discount]))) AS Price
FROM [Order Details];

JUST until the moment I want to add a new order!
A pop-up then complains that I first have to enter a "PO-number". :-(

I believe you would have seen this on the original sample as well, since the
PurchaseOrderNumber field in the Orders table is set for Required = Yes.

What is that? Is an end-user supposed to actualize some key-number??
Yes.

Can this perhaps be made to increment automatically.. and if so: how?

Yes, it can. Check out this sample on Access MVP Roger Carlson's web site:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

In this case, you could substitute this key for the Autonumber primary key
in the table. However, perhaps easier would be to just set a unique index on
this new numeric field that you would add.

Another question would be:
I'd like to have the items which are OUT of stock, to be mentioned in red
font (in the Orders detail subform). Now I know this has to do something
with "conditional layout", but what exactly must be done to achieve this?

I think you have Conditional Formatting in mind. Try this modification to
the recordsource for the Order Details subform. We are adding the InStock
field from the Products table:

SELECT OrderID, ProductID, Quantity, UnitPrice, Discount,
CCur(Nz([Order Details].UnitPrice*[Quantity]*(1-[Discount]))) AS Price,
Products.InStock
FROM Products
INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID;


Then, with the combo box selected in form design view, click on Format >
Conditional Formatting...
Add
Condition 1: Expression is [InStock]=0

and set the font color to red. You'll need to change the InStock value for
one or more Products in the Products table, in order to test this out. This
will not change the font color of the items in the combo list itself--the
change will only be applied after you make a selection.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[martin]

Thank you very much, Tom, the color-thing works now.

but unfortunately the rest of the form doesn't seem to like me anymore.
But I better don't take it personal. ;-) ;-) after all you warned me for
this
Perhaps due to the removal of the "Price"table, something essential to the
rest of the form has been removed.
The only thing is I don't know what-exactly.
I'll give this puzzle a try. Unless you have a quick and easy solution, then
I'd sure like to hear it

Kind regards,

martin

Hi Martin,

On second thought, change the recordsource for the Order Details subform
so
that it includes the Nz function. This will prevent the #Error that one
sees
when they select a new item, but before they've entered the quantity:

SELECT OrderID, ProductID, Quantity, UnitPrice, Discount,
CCur(Nz([UnitPrice]*[Quantity]*(1-[Discount]))) AS Price
FROM [Order Details];

JUST until the moment I want to add a new order!
A pop-up then complains that I first have to enter a "PO-number". :-(

I believe you would have seen this on the original sample as well, since
the
PurchaseOrderNumber field in the Orders table is set for Required = Yes.

What is that? Is an end-user supposed to actualize some key-number??
Yes.

Can this perhaps be made to increment automatically.. and if so: how?

Yes, it can. Check out this sample on Access MVP Roger Carlson's web site:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

In this case, you could substitute this key for the Autonumber primary key
in the table. However, perhaps easier would be to just set a unique index
on
this new numeric field that you would add.

Another question would be:
I'd like to have the items which are OUT of stock, to be mentioned in red
font (in the Orders detail subform). Now I know this has to do something
with "conditional layout", but what exactly must be done to achieve this?

I think you have Conditional Formatting in mind. Try this modification to
the recordsource for the Order Details subform. We are adding the InStock
field from the Products table:

SELECT OrderID, ProductID, Quantity, UnitPrice, Discount,
CCur(Nz([Order Details].UnitPrice*[Quantity]*(1-[Discount]))) AS Price,
Products.InStock
FROM Products
INNER JOIN [Order Details] ON Products.ProductID=[Order
Details].ProductID;


Then, with the combo box selected in form design view, click on Format >
Conditional Formatting...
Add
Condition 1: Expression is [InStock]=0

and set the font color to red. You'll need to change the InStock value for
one or more Products in the Products table, in order to test this out.
This
will not change the font color of the items in the combo list itself--the
change will only be applied after you make a selection.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanx a lot, Tom!

I followed the instructions and it works now...
JUST until the moment I want to add a new order!
A pop-up then complains that I first have to enter a "PO-number". :-(

What is that? Is an end-user supposed to actualize some key-number?? Can
this perhaps be made to increment automatically.. and if so: how?

Another question would be:
I'd like to have the items which are OUT of stock, to be mentioned in red
font (in the Orders detail subform). Now I know this has to do something
with "conditional layout", but what exactly must be done to achieve this?

Kind regards
martin