entered value in form must not overwrite tables

[Anouk]

HELP!!! I am getting quite desperate. I am a novice. I have made a database for customers, products, etc. They all have good relations. I have made a query where I can see who bought what and when and how much it costs. Now here's the problem. When my prices in my product list (this is the table of which the prices show up in my form) have to be altered (because of prices going up), it automaticaly changes the query and the form!! According to messages stated in this forum, I have opened the properties in the text boxes and changed the price fields, "enabled" to no, "locked" to yes, and the forms DataEntry to yes. Strangely in the North Wind example, the prices shown in the form (entry form), are extracted from the subformdetails table. But how can you extract something from a table if there is nothing in the table?? Totaly I have four tables (Orders, Orderdetails, Products and Customers) Alle these tables are joined on a one to many basis.
Please can somebody help me.....
Thank you
Anouk (Netherlands)

 

[Pat Hartman]

This has been discussed here a number of times but I'll go over it in brief (search for more detailed explainations).

The simplest way to resolve this problem is to store the price at the time of purchase in the OrderDetails table. This does not violate second or third normal form because the price is dependent on the order date as well as the product.
1. Add the price field to the OrderDetails table.
2. In the OrderDetails form, modify the combo used to select the ProductID so that it includes the price field.
3. In the AfterUpdate event of the ProductID field, add a single line of code:

Me.txtPrice = Me.cboProductID.Column(2)

Note that this assumes that the Price is the THIRD column of the combo's RowSource. The columns in a combo's RowSource are a zero-based array so column 1 is .Column(0), 2 is .Column(1), etc.

If you have data in the db, you can use an update query to update the price fields on the OrderDetails table to populate the price for the existing records.

 

[Anouk]

overwriting tables

Thank you so much for your reply, I understand the first part. (I am Dutch and my ACCESS is in Dutch).
I added the prize to the Orderdetails table (it's called VerkEx ). I added the fields on the form. Now the hard bit. I asked for the details of the box ProductID and somewhere nearly at the bottom I found lines like Extra Info, Afterupdate and Before update ( I think the undelined one is the one). Dont forget its all Dutch. So then I pressed the four dots, and I can make an expression. That didnt work ofcourse. Can you give me the line again using my prizename= VerkEx. And the prize in the field is the fourth box. First I have ProductId then Amount then Productname, and then VerkEx (prize). I'll send you a nice card for Xmas if I can work this out...because I am realy desperate, been at it for months.
Regards Anouk

 

[Anouk]

vallues do not show up in subform

This is driving me crazy. I managed to get the prizes in the form, done this with the cbo bit. But now they dont show up in my subform table...Even if I make a query I cant see anywhere what people have ordered. I take it that the Me. thing is the same as the cbo. But Visual Basic is something I have never done...

 

[Pat Hartman]

As I said, if you already have data in the table, you will need to run an update query to store the prices for those items. The code in the AfterUpdate event of the Product combo will add the price for each new item added to the table so you only need to run the update query once.

Me.txtPrice = Me.cboProductID.Column(2)
txtPrice is the name of the textbox control that contains the price.
cboProductID is the name of the combo control that holds the product. If the price is in the fourth column, you would specify this as .Column(3)

 

[Anouk]

I give up

Well I done everything you said, and I dont have items in my tables, because I made a test db, just to try it out. (empty) so no need for an upate query.
In the AfterUpdate I put: Me.Price=Me.cboProductID.Column(4) which is the 5th.
Thanks anyway, I think I will hang myself

 

[Pat Hartman]

Are those the correct column names?
Are you using the AfterUpdate event of the product combo?

 

[Anouk]

form details stored in tables

You seem to be a very patient person, and quite an expert of what I can see in this forum. My compliments. Could I please make a small database example and send it to you as a zipfile. Then you can see where I go wrong. It is probably a simple thing what needs to be changed. I have been behind this pc for 7 hours today, and still no luck. And yes the names are correct.
Thank you for replying.

 

[Pat Hartman]

Post the file here, I'm sure someone (maybe me) will correct it for you.

 

[Anouk]

post database

I tried to, but allthough its completly empty and only has 4 tables and 2 forms, it exceeds the amount allowed here. Its 240 and you are only allowed 100.

 

[Pat Hartman]

Compact the database and zip it.

 

[Anouk]

Sorry, I DID zip it....

 

[Pat Hartman]

Yes, but did you compact it?

 

[Anouk]

Oh dear, now I have to find out how to do that....

 

[Mile-O]

Tools -> Compact & Repair

 

[Anouk]

test dbase. Please help

Here is my test db. (4 tables, 2 forms.) I hope somebody can have a look at it, and make it work. All I want is when I have placed an order is to see it in my orderdetails. Now its all zero. Also if I change the price of a product in my product table, I only want it to change in the future orders, not in orders which have already taken place. I am getting quite desperate, and would be extremely grateful.

 

[Pat Hartman]

Your database has numerous problems. Take a look at Northwind.mdb. It is an order entry system and will show you everything you need to build an OrderEntry app (except inventory management). You could even use it as a base and mofify it to suit your needs (make a copy though so you don't mess up the original).

Why do you have multiple price fields in the order detail table? You should only have one.

When you place a calculation in the ControlSource of a field, that control is UNBOUND. That means that its value will NEVER be saved to the table. When you want to populate fields via code, you need to do it from a module.

 

[Anouk]

The reason I have 3 prices, is because we give 10% discount as from 50 euros on normal products, we don't give discount on glass, and books are a different VAT rate.
Ofcourse I know that the calculations I make in a foottext will not be shown in a table.
Northwind DOES show what a customer has ordered in its Orderdetails table. And I have tried to use it, but when deleting certain items you get problems.
My original database has its calculations in the foottext and works fine. I even can make an invoice in reports. And its not the calculations I want to see in a table, just the orderdetails...That is why I made a simple database, so I can show the problem. No matter what you have: a library, a shop, a hotel, whatever. You must be able to see what has been ordered, So somebody tell me WHERE can I see that, so that on that base I can make query's etc... How much, when, who ...etc.