My autofill data changes!

WakoSako

I Own A SOH
Local time
Today, 00:02
Joined
May 16, 2012
Messages
38
Hi I am new here so hello everyone :)
Ok I have built a point of sale database that I can book stock into and set a sellout price! So when I create an invoice I put the product code in and it pulls the description and sell price in for me (So far so good) Once I compleat the invoice's I can go back and check the older ones for my accounting (No problem) However this all fails when I change the sell price in my products file as it also changes the sell price on my old invoices, Making my accounts a joke :( I have very little hair left now so I came here lol I need some idea on how to keep my stored data static please??

Thanks in advance lol :)
 
You can create a Table with the Product Code and Sale Price Plus a Date effective from. Then using some code you select the appropiate price based upon the Date Sold. In theory this works well, but in practice not so good.

What I would advise is that you store the actual Sale Price against the Sale.

You can still store the Current Sale Price in a separate Table and use that table as a lookup.
 
Thanks mate that would work fullproof but was trying to save as much time as posibal on the process lol If only you could set default value to lookup the sell out against the product code! But thanks for your reply mate :)
 
If only you could set default value to lookup the sell out against the product code!

But that is exactly what I am suggesting. The difference from your original is that you store the Sale price against the invoice.
 
Not sure I follow you? I would still need to type the sell price into the inv for it to be stored static yes?
 
You search for The Product Code, which is in a Table together with the Sale Price, Description etc.

This Product Code is stored in the Invoice Table. So at the same time store the Sale Price. Same effort once you have the code written.
 
Lol I am a novice compaired to you guys! I manage a very small bit of codeing but normally Google it and modify to my needs! :)
 
Lol I am a novice compaired to you guys! I manage a very small bit of codeing but normally Google it and modify to my needs! :)

That is what most people do until they have a Library of thier own.

Not many people code directly from their memory.
 
I don't feel quite so stupid then :) If you have the time could you talk me though the steps that you mean? (In laymans terms lol) Ok I have a table with Product id, description, cost and sell! I also have My invoice (With sub form so I can enter many products per inv. I select the product code from a drop down whitch will then pull in the description and price! (I have 3000 products) So how do I get that price to stay the same on stored inv when I change the product table price? Do I need to alter table, Qry or form? or dabble in VBA? Sorry I am self taught so dont aways understand tech talk lol
 
It is late for me and I have an early appointment tomorrow.

If someone else does not jump in I will write an example for you.
 
After Updating your Product Code on the Invoice Table you (store) set [InvSellOut] = [ProductSellOut].

So no matter what you do to the ProductSellOut it will not affect the InvoiceSetOut. You may change the ProductSellOut whilst invoices are being processed however the InvoiceSellOut depends upon the price agreed with your Customer.

Simon
 
Hope the attached sample demonstrates what I have been talking about.
 

Attachments

Your Product table has the current unit price and the OrderDetails table (where the sale is recorded) should have a column for UnitPrice and another for quantity. You calculate the extended price by multiplying UnitPrice * Quantity.

To save the UnitPrice, include the unit price from the Product table in the query used as the RowSource for the combo used to select a product. In the AfterUpdate event of the product combo, copy the unit price from the combo to the current record.

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

The RowSource of a combo is a zero-based array. The first column is addressed as .Column(0), the second as .Column(1), the third as .Column(2), etc. I am assuming that price will be the third column in the query. If it isn't, then adjust the column number accordingly.
 
RainLover thankyou so much for your time! (And to the others that have posted) That is excatley what I need my database to do! :) Ok 2 days ago if anyone had asked me what I was like on Access I would have said I am the king of Access! But after comming here I can see I am just the Access Jester :( I see I have so much to learn and take in (But I want to learn this cool stuff)
RainLover I know you made the DB simple for me to understand but maybie I am more simple then you thought :( I have spent 2 hours trying to work out how you did this and still I am in the dark! I checked the TBL, QRY, and Form and cant see it! So went into the VBA screen and tried to understand it lol yeah that made me evan more confused! Ok is this what makes it work?

Private Sub ProductFK_BeforeUpdate(Cancel As Integer)
Me.txtSaleValue = Me.txtSaleValueCurrent

End Sub
Private Sub txtSaleValue_AfterUpdate()
End Sub
Private Sub txtSaleValue_BeforeUpdate(Cancel As Integer)
End Sub

Or maybie I should go play with Calc.exe or Sol.exe?
 
First have a look at the Record Source of the Form.

That would be the Query.

You can see the Join. So the top part of the Form shows the Fields on the Left or the query and the Bottom Half shows the right.

Now look at the Properties of the Combo Box.

It finds the Primary Key in the Products Table and places that in the Invoice Table's Foreign Key. You will see that the Current Price shows in the Bottom of the form. This price has also been saved in the Invoice Table. And if you want it can be changed because of Bulk Discounts, Damaged stock etc.

The code in the Before Update saves the listed price from the products table to the invoice table.

If you change the Value in the products table the saved value in the invoice table remains unchanged.

So it is not just one thing I did it is a combination of relatively simple things.

Hope that helps.
 
Thank you so much for explaning that :) I will go and study and digest it :)
 
If you truly want to learn then learn Normalization and Naming Conventions.

You will only learn VBA through experience, but that doesn't take long. You should be an expert in about 10 Years. In my case I have done my 10 years and still have a ways to go.

When writing a Database think simple and practicable. Don't get carried away with a bunch of fancy extras that are not really required.

Good Luck.
 
Thanks for that :) Yep I tend to over complicate things and have to many bells and whistles lol Sometimes Less is more right? lol
 

Users who are viewing this thread

Back
Top Bottom