Tough One

[chrismcbride]

I have a database that is used to create Purchase Orders, Quotes, Invoices and so forth. I have suppliers and customers in multiple countries and am required to show and manipulate multiple currencies. My usual M.O. is to bring a product price onto a form in it's native currency. From there I convert it to Canadian to make things clearer for users. I would like to be able to format the form controls to show the native currency's symbol as part of the value (ie. Format). This is simple enough in a control where I do not need to allow updates to the value. What I can't figure out is how to get the formatting and still be able to enter values...
Any ideas????
Chris

 

[MHM]

Probably you format any data before pushing into the control - you succeed by doing it vice versa:
Just select any (double-)value as DataSource for any control - but avoid doing any formatting like Format(dblData, "#,##0.00 DM").
Then choose "Currency" as format for this control (control/Properties).
This enables any user to see that data shown with the local (Windows-)currency-settings - which are stored on that computer.
You can test this by changing your currency-settings - the control changes its formatting behaviour immediately.

Have a good day!

 

[chrismcbride]

One Windows setting for currency will not suffice as many different currency symbols are required. Perhaps even within the same record...
Chris

 

[simongallop]

Most probably not what you are looking for but how about a dropdown box (or whatever you are using) where the user chooses the currency and then code which will insert the correct sign in a label in front of the textbox, and textalign left so that they appear together?

 

[chrismcbride]

I do something like that now, however I use dlookup to find the value automatically. I then put the symol right beside the number field and leave off all the formatting on the number field. I was just hoping that there might be a better way. It doesn't matter all that much as I can display the numbers in reports formatted properly. So my customers and suppliers will see the proper data. It's just users that have a somewhat screwy looking UI.
Chris

 

[Richie]

Assuming your using just a general number field you could try something like this
Private Sub Form_Current()
If Me.country = "UK" Then
Me.test.Format = "£#####;0"
ElseIf Me.country = "USA" Then
Me.test.Format = "$#####;0"
ElseIf Me.country = "France" Then
Me.test.Format = "ff#####;0"
ElseIf Me.country = "Spain" Then
Me.test.Format = "#####pts;0"
Else: Me.test.Format = "#####;0"
End If
End Sub
Etc,etc

Private Sub test_AfterUpdate()
Form_Current
End Sub
Since the number is still stored as a general number it shouldn't affect calculations. I am curious though how you cope with currency fluctuations.

 

[chrismcbride]

Richie
Thanks a lot! Worked fine and I learned something, too. I had tried exactly what you suggested but encountered a strange error so I gave up and tried some other stuff. Turns out that the problem was not with my code, which was the same as yours, but with the fact that I only tested with German currency. I tried all sorts of combinations of ...
"DM #,###.00;0"
but got things that looked like...

345####

I thought that I was doing something wrong. But since...
"ff#####;0"
...works, then it must be something to do with the letters d and m.(Day and Month???). I think that it is that the VBA Format method thinks that I am trying to format a date, and that is why the error. What do you think?

In response to your question, I have had to make many amendments to my initial design in order to support the variable currencies. I have exchange rates and currency types stored all over the place. I allow foreign currencies to be converted to Canadian in order to be manipulated on forms. I allow exchange to rates to be entered on forms in order to convert Canadian values into diferent currencies for Reports. There is really too much to write here, but I would be pleased to answer any direct questions you might have
Thanks again
Chris

 

[Richie]

Glad it works Chris I was just curious about the rates nothing specific just that it would appear to me to be a nightmare.Not sure about DM will try and fathom that one.

 

[chrismcbride]

Nightmare, to say the least!

 

[Richie]

Try this .Format="""DM""#####;0"

 

[chrismcbride]

Works like a charm, but I have since learned something else. German currency should be formatted...

3.456,89 DM

So I don't have an issue any longer with the earlier error. I am currently working on how to reverse the thousands separator and decimal separator formats...
Lucky Me!!!
Thanks
Chris