Order Entry

[Guest]

I have a basic Order Entry form, where the line items are in a one to many
relationship with a header. The subform is based on an "order details"
subform, basically identical to the form in the Norhtwind database.

The Product ID field on the subform, looks up the product from a products
table, but if I use the unit price from the products table in the underlying
query it causes a problem if I have a price change. The price change is
reflected in the table and in all invoices in the database. NO GOOD.
Northwind uses the unit price from the Order Details table in the query and
this permits the price to be changed on the invoice without affecting the
Products table or other invoices.When I tried that, the unit price would not
fill in on the form!

I modeled both my tables, the query, properties, lookup SQL statement and
relationships identical with the Northwind database but get different and
unacceptable results.

In other words, in Northwind, the unit price fills in and can be adjusted on
the form without affecting the price in the basic table.

I must be missing something, probably simple, but frustrating. Any help?

 

[Al Camp]

murrwheel,
Sounds like you price is acting like calculated DLookup field. Don't build the
ProductId and associated price into the query behind the order form.
Just place The ProductID from your Orders table on the form, along with the Price field
from your Order table. When a ProductID is entered, (AfterUpdate event)... that's the
time to do the Dlookup for Price and set the value of the price field to that.
Even better, make ProductID a combo box (ex. cboProductID) with ProductID in the first
column, and Price in the second, with the query for that combo based on the Products/Price
table. Bind ProductID to the combo field, and On the AfterUpdate event of cboProductID.
Price = cboProductID.Column(1)
(combo cols are actually numbered 0, 1, 2, 3, etc...)
That sets the value for Price at the time the Order is made, and orice changes in the
Products/Prices table will not affect it.

 

[Guest]

Al,
I appreciate the fast response as I have been struggling with this for 3
days. Unfortuneatly, my grasp of VBA is slight and i am having trouble
applying your suggestions. I tried putting =DLookup([UnitPrice], [Products])
in the After Update slot and got an error, "Object doesn't contain the
automation object "Products".

The second approach with a combo box completely eludes me.

Sorry to be such a dunce.

murrwheel.

 

[Al Camp]

murrwheel,
Let's work with the Dlookup method.
**I'll use example table and field names... you replace those with your own...
OK, you have an Order form that updates values in tblOrders.
On that form, you have a place to enter a ProductID and upon that entry, you want to
update the value of the Price field on that form with the current Price from tblProducts.

In design mode, select ProductID on the form, and locate the text box associated with
the AfterUpdate event.
Place the cursor in that empty field (remove what you may have entered previously)
Using the drop down arrow on the right of the property box, select Event Procedure
Now click the button with 3 dots (...) on the right, and you will see this

Private Sub ProductID_AfterUpdate()

End Sub

Place this code *between* those two lines... (again, use your own table and field
names)
Whenever ProductID is updated the code between the lines will execute.

[Price] = DLookup("[Price]", "tblProducts", "ProductID = " & ProductID)

Whenever you update/change the ProductID, the DLookup will locate the associated Price,
and place it in the Price field.
(ProductID must be unique, with no duplicates, aka... a key field)

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al,
I appreciate the fast response as I have been struggling with this for 3
days. Unfortuneatly, my grasp of VBA is slight and i am having trouble
applying your suggestions. I tried putting =DLookup([UnitPrice], [Products])
in the After Update slot and got an error, "Object doesn't contain the
automation object "Products".

The second approach with a combo box completely eludes me.

Sorry to be such a dunce.

murrwheel.

murrwheel,
Sounds like you price is acting like calculated DLookup field. Don't build the
ProductId and associated price into the query behind the order form.
Just place The ProductID from your Orders table on the form, along with the Price
field
from your Order table. When a ProductID is entered, (AfterUpdate event)... that's the
time to do the Dlookup for Price and set the value of the price field to that.
Even better, make ProductID a combo box (ex. cboProductID) with ProductID in the
first
column, and Price in the second, with the query for that combo based on the
Products/Price
table. Bind ProductID to the combo field, and On the AfterUpdate event of
cboProductID.
Price = cboProductID.Column(1)
(combo cols are actually numbered 0, 1, 2, 3, etc...)
That sets the value for Price at the time the Order is made, and orice changes in
the
Products/Prices table will not affect it.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions