Set 'units' of controls, using another controls selection

[AntonRSL2016]

I have a table, form and report set-up for an order form. One of the controls on the form allows currency selection from a combobox, three other controls are numerical controls for prices. Is it possible for the three price controls to take the currency selection from the currency selection control?

All help much appreciated.

Anton

[PhilOfWalton]

You are a little unclear in what you want.

Are yo saying that if the price is 12.34 you want to show it as
€12.34 if Euros have been selected
$12.34 if Dollars have been selectes
£12.34 if Sterling has been selected?

Phil

[AntonRSL2016]

Exactly....is it possible? I am quite experienced now using Access but I am by no means an expert!

[PhilOfWalton]

Assuming the only field in your combo box is the currency symbol, and that is the bound column then something like this should work:-

StrPrice = MyComboBox & Format(MyPrice, "General Number")
 

Phil

[AntonRSL2016]

Thank you. I entered this code, withthe control names updated as belo:

Private Sub txtUnit_Price_AfterUpdate()
StrPrice = cboCurrency & Format(txtUnit_Price, "General Number")
End Sub

Unfortunately it didn't have any effect. Does the format of the cboCurrency combobox matter? Currently it is a number format because it is the currency ID number being stored in the field (of the underlying table) rather than the symbol itself. I guess that must stop your code working because the expression is not seing a symbol in the cboCurrency control!

I will try regenerating the structue with the currency symbol stored in the control/field.

Anton

[PhilOfWalton]

I has assumed (obviously incorrectly) that the CboCurrency was based on a table with a single field of the currency symbol.
If it is a 2 column combo box you may need to change the code to:-

StrPrice = cboCurrency.Column(1) & Format(txtUnit_Price, "General Number")
 

The Column Number starts at 0 so if the RowSource look like this

CurrencyID    CurrencySymbol
      1           £
      2           €
      3           $
 

then the currency column is the second column (Column 1)

Phil

[NeoPa]

Hi Anton.

Why don't you tell us what's in the table and how the ComboBox is populated. From that we can probably work out what you're trying to ask for and suggest a meaningful solution for you.

[AntonRSL2016]

Hello, my appologies for leaving this reply so long, distractions.

The underlying table has ten fields, most are text entry-type. This problem concerns three of the tables fields, shown in the Tbl_Purchase_Order_Foundation_Level.jpg. I feed data into this table from Frm_Purchase_Order_Request, see Frm_Purchase_Order_Request.jpg, which also populates a report.

I am happy for the table to continue to show number format for the Unit_Price and Line_Value fields, but I would like the controls on the form and in the report to have a currency format. Currently £ is fixed in the controls format selection, but I would like the selection in the Currency control Combo box to govern the format of the Unit_Price and Line_Value controls.

I tried the code suggested but it wouldn't work for me, which I am sure is my fault rather than an issue with the code itself.

Anton

[NeoPa]

Hi Anton.

Your pictures have so little background it's hard to see what's a table and what's a form. Your explanation doesn't make it any clearer. What exactly is your problem?

[AntonRSL2016]

Good morning NeoPa,

I would like to select a currency symbol in one control, and have this currency symbol apply to two other controls. Referring to the jpg, Frm_Purchase_Order_Request (the form, which is tabular rather than columnar) I select £, $ or € in the 'Currency' control, and I would like the control 'Line Value' to have the same currency symbol shown.

Does that make sense?

Anton

[NeoPa]

You need to set the .Format property of the TextBox control on your form in that case.

NB. The escape character (\) must be used as Access recognises various different currency symbols simply to mean use the local one. Thus a format string of the following would be required :

"\£#,##0.00"

[AntonRSL2016]

Thank you very much.

[NeoPa]

I'm glad I could help Anton :-)