Data saved so it won't automatically change

[markswan20]

Hi everyone.

I have created a form that pulls data from column 1 in a combo box and places it in a different text box. The information is used as the price of the product.

The information is taken from a second table called ProductID, my problem is when I need to change my prices within the ProductID table they automatically change for my existing customers. Is there a way of making the information automatically or on the click of a button transfer to my main database so that when i do change my prices it won't affect my older customers.

Thank you for you time
Mark

 

[OnTheFly]

You would have to store the price with each record that it pertains to just like you do quantity et al. The other option would be to use dates in your price table so that you are storing history. Then the price can be determined based on the date of change.

Hope this helps.

OnTheFly

 

[markswan20]

Thanks for your reply OnTheFly,

Is there a way I could create some sort of macro that takes the data from the combo box and puts it in a different box thus using this as the correct information bound to the table?

Thank you
Mark

 

[OnTheFly]

Yes. I don't know the structure of your combo box so I will give you an example and you can extrapolate from there.

Let's say the combo box (cmbProducts) has the following columns:
ProdID
ProdDesc
Price

The text box bound to the field you want to store the price in is called txtPrice.

In the After Update event of the combo box you would enter

Me.txtPrice=NZ(Me.cmbProducts.Column(2),0)

This will enter a 0 if nothing was selected and the price from column 3 if the make a choice.

The column property starts its count at 0 that is why you use Column(2).

Hope this helps.

OnTheFly

 

[markswan20]

Thank you for your reply OnThe Fly.

I have this working so when i select the relevent product the relevant price relating to the product displays successfully in the given box however my problem is the price changes i only want these to happen for new customers but at the moment they affect all customers both old and new which makes life very confusing when a customers call back and the price is different to the original.

This problem has bugged me for a while is there some sort of afterupdate that can copy information to the database for later referal?

Even if it meant that the price was displayed in a different box and this box updated a different box which was stored.

It's a little long winded but if it worked hey who cares!

Cheers
Mark

 

[PhilWB]

You have to take a step back, and create two fields. One Product Price that can be the original Price and Order Price. Order Price can be a Dlookup or combo based on Product Price, but either way, every order will have it's own Order Price therefore allowing you to have seperate prices for each customer.
Phil

 

[OnTheFly]

OK, I am getting confused. Let me see if I am even close to reading this situation correctly. You have products with prices. You have customers that purchase these products. In some table somewhere you have the customer ID and the Product ID that they purchased. Are you storing a unit pricefor these products in this table?

If yes, then I guess I am confused by what you were asking in the first place. I guess I was assuming that the combo box form was when you were entering a new order or something.




Hope this helps.

OnTheFly