How do I change a Price in a current record onlY?

J

JCJ

New member
Local time
Today, 21:30
Joined
Mar 18, 2013
Messages
4
I hope I'm going about this in the correct way?
I am trying to teach myself access 2007 but the following problem defeats me. I set up two tables; PRODUCTS – which includes Product Name and price, and ORDERS - which uses a lookup field to select the product. I also created an Order Form from a query which selects the price from the products table – so far so good. However, when I change a price in the product table, it changes not only the current order record but also all the previous records. I have tried various ways of locking the price field on the form but to no avail. What am I doing wrong? Any help would be much appreciated.
JCJ
 
I don't think you have enough tables in your database, but I haven't seen your database. Perhaps you could tell us the purpose of the database, and describe all the tables involved.

I have written a few posts dealing with the issue you describe.

see the series of comments and diagrams at
www.access-programmers.co.uk/forums/showthread.php?t=237359

Pay attention to the AgreedUponPrice.

Post back if you have issues
 
Hello JCJ, Welcome to AWF.. :)

Well the description suggests that the Design has some issues.. Specially this
JCJ said:
..which uses a lookup field to select the product.
Click to expand...
Table level lookups are not recommended at all, they are evil.. Considering the issue, you need to understand that a product can have fluctuating price, based on several reasons - Special discount, New price (that might include tax), Old price (that might not include tax).. So it is best that the price would stay in the Orders table.. that will relate to a particular Order, for a product.. As a product can have many Orders so can a product can have many prices.. So I would suggest,

* Move the price/amount field to the Orders table..
* Remove the LookUp, and creat a relationship between the two tables and change your Form design to Main Form/Subform where you will be able to Create and manipulate the data..
 
I agree with Paul and should have mentioned or asked if you had lookups at the table field level-- that's considered a no-no.
 
First of all, thanks for the reply. At present I am only trying to replicte in Access what I've done in the past when, believe it or not, I ran my son's small manufacturing business successfully using Excel. I would intend to have the same tables for products, customers, suppliers, hauliers etc, all of which I have managed to link together ok in a test database. It was when I came to the issue of pricing I got really stuck as I can not quite get my head around how to set the price/order tables up in the first place. I also have to admit that at 69 the old grey matter is slowing down. However, I will look to the location you directed me to and see if I can work it out - its quite a conundrum - Regards JCJ
 
Yes look at some of the relationship jpgs in that thread.
Read the dialog and post back if there are questions.
 
Avoiding table level lookups is not the same as not using combo/listboxes on forms. Combos on forms = :) Combos on tables = :(

The simplest solution is to copy the price from the product record to the order details record. I know at first blush, that seems to be a violation of normal forms but it actually isn's since prices change over time and you need to retain the price at the time of the sale.

To do this, add a third column to the product's RowSource query to hold UnitPrice. Then in the AfterUpdate event of the product combo, copy the price.

Me.OrderUnitPrice = Me.cboProduct.Column(2)

This has the added advantage of allowing you to actually override the price when the order is placed should that be necessary.

FYI - columns are a zero based array so .Column(2) is the THIRD column in the RowSource. Adjust the number as necessary for your query.
 
Many thanks, I will give it a go - Reagards JCJ
 
Sorry to hijack this thread, but I have a similar problem.

My form is for reservations and it is a form/subform set up where you create top level info such as company name, reservation number, etc... and a sub form where you add items line by line, much like adding items to an invoice. In this subform is cboBOM description, Lease Cost, Discount cost, Date OUt and Date In. For displaying lease cost, I am trying what pat said and made column(4) of cboBOMDescription = to daily rate which is taken from the equipment table.

The problem is that when I choose an item, all of the items in the subform take on the same price as the one I just added. I used the line: Me.TxtLeaseCost.Value = FormatCurrency(Me.cboBOMDescription.Column(4))
in the after update of the combo. I assume I need some kind of criteria so that the price only updates in the appropriate row??
 
Just to clarify, here is a pic.
 

Attachments

  • Reservations.jpg
    Reservations.jpg
    88.5 KB · Views: 241
Sounds like your TxtLeaseCost control is unbound. You need to have this text box bound to a LeaseCost field in the underlying record set. The detail section of a continuous form is just one row of controls which is repeated for each row in the record set, so unbound controls will behave the way you are describing in this situation.
 
I used to have it bound to an expression column in my underlying query. The problem is that if I never had any dates chosen, I would get a "Field not updateable" error.
 
Just had an "Aha!" moment. Created a field called LeaseCost on the many side of my table relationship. This will store the lease cost for each item on a reservation for historical purposes. Also used VBA to make the lease cost field = cbo.column(4).

Thanks for the help Beetle!
 
You must log in or register to reply here.

Similar threads

asteropi
Solved Price History
2 3
Replies
45
Views
2,170
CarlettoFed
C
C
Subform Footer Textbox wont update
Replies
11
Views
500
Pat Hartman
P
H
Solved Dlookup Or SetValue not working
Replies
17
Views
828
mike60smart
mike60smart
C
Sub or function not defined
Replies
12
Views
989
Pat Hartman
P
H
New price is not updated in the report
Replies
2
Views
312
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom