Invoicing Problem

[slogie]

Hi, I have been working on an invoicing database. Everything works well except for the fact that if I change the price of an item, it changes the price on the previous invoices. How can I avoid that. Can someone please help me. I am at a total loss as how to go about this. Thanks.

 

[pbaldy]

You should be saving the price of the item as part of the sale record. It is an attribute of the sale.

 

[slogie]

Thank you for replying so quickly, but I am not following you. I saved the price as a field in the product listing. So when I am doing the invoice, I just select the part number that I want and all the information comes up.

 

[pbaldy]

Your table for the invoice data should include a field for the price, and you should populate that field when you create an invoice. I suspect your existing process is just displaying the price, not saving it. As you've found, simply storing the price in the products table won't work. The alternative would be storing the price for any given time period, but typically that's not practical.

 

[slogie]

Ok, I guess I should have stated this in the beginning. I am a novice at Access, so I am a little lost when you said that I should populate the field when the invoice is created. Could you please explain further. The existing one displays the price, as I want it to do, because I don't want to have to write in the price each time. But I also don't want it to change the original invoices.

 

[pbaldy]

Well, you're kinda making me guess at how your current application works, since you haven't posted it. My guess is that you're using something like the first technique here, and yours is a candidate for the second.

http://www.baldyweb.com/Autofill.htm

If you don't store the price with the invoice, how do you expect it not to change when you change it in the products table?

 

[alexlds]

I had this problem. I ended up using an append query to add a copy of each new record to an archive table. The original record had all sorts of calculated fields which changed every time the unit cost or whatever was updated. But the record placed in the archive table was just a copy of the calculated result - so that didnt change when the unit cost was updated.

 

[slogie]

I had this problem. I ended up using an append query to add a copy of each new record to an archive table. The original record had all sorts of calculated fields which changed every time the unit cost or whatever was updated. But the record placed in the archive table was just a copy of the calculated result - so that didnt change when the unit cost was updated.

Thanks for sharing. Your idea sounds like something I've come across before I am not sure how to do it. If you could advise further I would greatly appreciate it. Thank you.

 

[alexlds]

Ok Im a beginner too. As a far as I can remember this is what I did. Open your existing table / query. Then in create query run the Make Table query after selecting all of the fields in your existing table/query (both the ordinary ones and the calculates ones). Save the new table as say Archive Table.

Then in Create Query make and save an Append Query - again selecting all the fields from your existing table/query - and tell it to append the records to your new Archive Table. When you run the Append Query it will do just that.

What I then did was make sure that there was only one record at a time in the original query before running the append query. ie after appending the record to the archive table I deleted it in the original table. I wrote some code behind a button to do save the record , then append it to the archive then delete the original record.

Im sure that at this point experienced and professional users will be screaming and tearing their hair out or ROFL - but hey so what ???- it works for me and it does what I need it to do.

There are no auto calculated fields in the archive table - thats all done in your original table / query.

Something that has helped me. Every time I finally learn how to do something in access I make a note of what I have learned in my own words in a Word file called Access How To. Then in a year or whatever when I need to do it again I dont need to reinvent the wheel.

Ive found everyone on this forum EXTREMELY helpful- but sometimes some of them struggle to understand the things that we beginners are trying to do - usually because we are not explaining ourselves in the terms that they normally use.

Hope this helps

 

[Simon_MT]

You MUST save the Product Price to the Sales Invoice.

One method is Product AfterUpdate set Sales_Price to Product_Price.

This the only way to kept the integrity of the Invoice.

Simon

 

[slogie]

Ok Im a beginner too. As a far as I can remember this is what I did. Open your existing table / query. Then in create query run the Make Table query after selecting all of the fields in your existing table/query (both the ordinary ones and the calculates ones). Save the new table as say Archive Table.

Then in Create Query make and save an Append Query - again selecting all the fields from your existing table/query - and tell it to append the records to your new Archive Table. When you run the Append Query it will do just that.

What I then did was make sure that there was only one record at a time in the original query before running the append query. ie after appending the record to the archive table I deleted it in the original table. I wrote some code behind a button to do save the record , then append it to the archive then delete the original record.

Im sure that at this point experienced and professional users will be screaming and tearing their hair out or ROFL - but hey so what ???- it works for me and it does what I need it to do.

There are no auto calculated fields in the archive table - thats all done in your original table / query.

Something that has helped me. Every time I finally learn how to do something in access I make a note of what I have learned in my own words in a Word file called Access How To. Then in a year or whatever when I need to do it again I dont need to reinvent the wheel.

Ive found everyone on this forum EXTREMELY helpful- but sometimes some of them struggle to understand the things that we beginners are trying to do - usually because we are not explaining ourselves in the terms that they normally use.

Hope this helps

Thanks I'll give this a try and let you know how it works out.

 

[pbaldy]

So a complicated archive table is simpler than saving the value? Won't it be saved in the archive table? Maybe it's too late at night here, but it makes no sense to me.

 

[alexlds]

Hi pbaldy - you have given me a lot of help previously which has been very much appreciated. As I said, in this case experienced guys like you will prob be having a fit at what Ive done. Perhaps I can explain it better . .

Most beginners like me use the normal simple table - query - form setup with calculated fields appearing in the query eg adding VAT at 20%. But the problem with that is when the VAT rate changes and you change the 20% factor in the calculated field in the query, all the records in the table will then reflect the change. Thats fine if thats what you want.

Copying / appending the calculated results into an Archive or Invoices table gives a "passive" historic record. So I then have a record what I actually paid in VAT on each transaction last year or whenever when the VAT rate was different.

Id be surprised if there wasnt a better way of doing it - but thats what I did.

As they say, hare today gone tomorrow . . . oh well . . . . .

 

[Simon_MT]

You don't need to archive, just store the values. VAT was 17.5% then 15% then 17,5% and now 20%. The same applies with prices once stored on the Invoice details - job done. It now does matter what happens to the price within Products as the Invoice reflects the price at the date of invoicing.

Simon

 

[pbaldy]

In my opinion, the price of the product and the VAT rate are attributes of the sale in the same way that the product sold, quantity, date, customer etc are. I would have fields in the transaction table for each and store the value of each with the sale.

 

[slogie]

Ok guys, seeing that you guys said to put the price in the transaction table, I did that, but in doing so, I have to put the price in for each item. I don't want to do so, that is what I am trying to avoid. I want it to show up when I select the item. Is there anyway that I can do so with your concept?

 

[pbaldy]

I would do like I suggested 3 days ago in post 6. Use a combo to choose the product, have that code populate the price.

 

[Simon_MT]

There is no alternative but to hold the Price and Vat on the Invoice Detail otherwise the the Invoice over time will simply NOT be accurate. I know that you are not disposed to doing this but it is really no big deal.

Simon

 

[slogie]

Ok I am at a loss as what to do. Here is the sample of what I was working on and trying to incorporate what you guys were saying...but no luck. See if you can tell me what I am doing wrong. Thanks.

 

[pbaldy]

Simon, based on post 16 it sounds like slogie is doing that now. Unless I'm reading it wrong, the issue now is with having to manually input the price.