Stamp Exchange Rate Used for Quote

[Hulm1]

I have a quotation system in which several currencies can be selected. I have a table for currencies and their rates with a look up box on the quote form for the current rate (one which is periodically updated).
However, the problem is that when that rate gets changed, the calculations on all the quote sheets will change too. This is obviously a problem because I need to record the rate used permanently.
Ideas?

 

[SkipVought]

If your table for currencies and their rates is based on normalized values, ie relative to the US Dollar or Brittish Pound or whatever, then if the table values are correct, your formula ought to return the proper value, unless I am missing something.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Axworthy]

Get the rate from the rate table and save the current exchange rate in a field on the quote table.

 

[Hulm1]

Thanks. SkipVought the problem is that the rates change. So when we change the rate, ALL quotes will change unless we stamp the actual rate that day.
Axworthy, I can look up the currently set rate via a combo box. I don't know a way to automatically stamp that rate into a field in the quote permanently (from which the rest of the fields are calculated without manually copying it (with risk of error). A small exchange rate error can eradicate the profit!

Thanks again

 

[Axworthy]

A simple way if you are using a bound form is to have the control source of the combo box set to the quote's stored exchange rate.

 

[MazeWorX]

One solution would be to separate your data entry from displaying existing quotes. Assuming your cbo bound column is the rate id field it as you stated is going to change as you change your rates. bind the 'actual rate' to the cbo and write the quote to the table this way the rate not the ID is going to be written to the table. Making the data entry with an unbound form then saving it to a table will solve this issue. The 'save' could be done with an append query or vba on a cmd button. you would have to additionally create another form/report to display quotes

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MazeWorX]

See attached db A2k for an example of what im talking about. It uses vba to write to the table however the vba could be replaced with an append query just as easily. note uses DAO but again this could be changed to ADO to accomplish the same task

http://www.fileden.com/files/2009/6/6/2468005/db23.zip

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Hulm1]

Hmm OK, this is out of my league. I have never learnt recordsets etc. However, I can see that this is the way forward. Can I give you the full complication which occurred to me a bit late?

Some of the prices being entered into my quotation are also being looked up from a price list. It therefore dawned on me that I have the same issue as I do for the exchange rates as the prices on these price lists will change. Therefore I can see your approach is exactly right. The problem I think is that as I expect to have 3 or 4 subforms on tabs within the quotation such as..............

1) Mobilisation (delivery, cranes, hotels, miles travelled, etc).
2) Plant rental different rental rates for different rental periods)
3) Fittings
4) Labour

.....The code might well out do me!

Could I stretch my luck and ask you for an example like you did already with your db23?

I would like to attach my draft db, however, the file storage link here is not working. Maybe I could email it?

 

[MazeWorX]

fileden.com is free to sign up and has 2 acct types ... the free one that I use and for the more serious user a paying acct. Sign up for the free one upload your db and we can take a look at what you have

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Hulm1]

OK I have uploaded the file. The main form is "Estimate"
Please ignore the two errors which crop up with subform "Estimate_Fitting_Item". I have a requery problem which I can't figure out but will work on some other time!

 

[MazeWorX]

wrong link

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Hulm1]

Ok feeling really dumb now. How do I give you permission to log on and download the file from filden?
Sorry!

 

[MazeWorX]

what you want to do is give me alink to the file not logon

logon to fileden goto files right click it click properties click linking codes:URL paste the link and we can then retrieve the file

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Hulm1]

Ok thanks. Here it is

http://www.fileden.com/files/2010/2/8/2754006/My Documents/Quote_Draft.rar

 

[MazeWorX]

OK those annoying errors are gone ... give me a bit to absorb this and ill get something back to you tommorrow

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Hulm1]

Hi

Appreciate you looking at this. I had a play with your record sets. I modified form Estimate_mobilisation and it works great. Except for one thing. I can't get it to work with continuous forms this way. Trouble is I might have 25 line items of different fittings for example.