Preserving Values

[BobJ]

Hi,

I have a form in which orders are placed. Each product we sell has a set value, however this does sometimes change depending on the client. What i need is to enable my db users to change the value for the unit price of a product in the form without the original value in the table changing.

I also need to make a drop down box change the prefix of my OrderID (Autonumber and primary Key) depending on 1 of the 2 selections. Now i know it isnt possible to do that with an autonumber, and im not sure where to start.

Any ideas?

Cheers,

bob.

 

[macca the hacke]

Why don't you get the text box that stores the price of each object to default to the master price, but give the users the ability to overwrite this price. You would therefore need to ensure that you have 2 tables - on that stores default prices, and one that stores prices that you have actually charged.

Why don't you change your invoice number so it consists of 3 parts - (pricing source/autonumber/primary key) - and have say 1 for master price and 2 for amended price?

 

[BobJ]

sounds good, but how do i go about doing that, im new to access so bear with me hehe

 

[macca the hacke]

Without seeing how your database is built is difficult, but something along the lines of:

1) I take it you already have 2 tables - one storing product id and price, and 1 storing invoice no and product id
2) Need a new table storing invoice no and price
3) To set a default value for a text box you would need to insert some code on the form so that after the product has been selected it goes and gets the price from the master pricing table. You would then insert this price into a text box. This would be linked to the new table that stores prices actually quoted on invoice.

To do 3) above, code would look something like:

******
cmb_productid_afterupdate()

if cmb_productid.value <>"" then

dim CcyPrice as currency
dim strProductID as string
strProductID = cmb_productid.value

ccyprice = currentdb.openrecordset("select [tbl_pricingtable].[price] from [pricingtable] where [productid] = '" & strproductid & "'").fields("price")

txtprice.value = ccyprice

end if

******

txtprice needs to be linked to the new table

You will need to change a few references above, but I am sure you can figure those out