Need multiple currency symbols through an application - explored a few routes, suggestions please

[writeprivate]

@ArviLaanemets

Apologies for my reply before - I was a bit frustrated, I had no right to take it out on you. I'm having one of those days. Peace.

[orange]

writeprivate
Regarding your post #15, can you not mock it up with some test cases and sample data to get the requisite logic sorted? Seems you have an approach that you could test and refine as needed. And CJ has experience with the approach and is respected for his helpful posts.

[writeprivate]

writeprivate
Regarding your post #15, can you not mock it up with some test cases and sample data to get the requisite logic sorted? Seems you have an approach that you could test and refine as needed. And CJ has experience with the approach and is respected for his helpful posts.

That's very kind. I have actually progressed along the lines of post 15 and have got a long way forward. It is working well so far. I thought I could have the currency symbol follow the numbers but since that route isn't possible, I have gone ahead with the separate fields.

[Micron]

create an unbound txt field to the left of each monetary field, and add "CURR" to the tag property of these fields

Why not use a table field and a bound control? Then the currency symbol becomes part of the record and you avoid coding for Tag property.

[writeprivate]

Why not use a table field and a bound control? Then the currency symbol becomes part of the record and you avoid coding for Tag property.

Hmmm. I'm thinking about your suggestion. Since this is my first time working with two fields for monetary values I don't know the pros and cons of various methods. I'd like to understand what you're thinking. Here's more context to my evolved solution - please feel free to steer me with your thinking.

I have approached with the primary objective that there can never be a doubt what the monetary number represents (ie which currency).

On this basis I have essentially linked the number's currency to:

A) in the purchase/sales order management area, every relevant table is subordinate to Portfolios. So by setting the portfolio currency and making it uneditable after creation, AND having the portfolio fk in every subordinate table - I know the currency by knowing the portfolio.

B) in the bookkeeping area. Once a PO is marked 'complete', the details are inserted into the general ledger. The general ledger is a child of (new) currency accounts table - with the currency accounts table as a child of chart of accounts. So each bookkeeping account has multiple currency sub-accounts. This means that once a transaction is posted to the GL it is within a gated currency account.

Using this thinking, I have imagined the number doesn't need a currency code in the table because the currency symbol etc is purely for display.

Ps. The code to populate symbols on forms is very simple and a common global function

[Micron]

A loose interpretation of what I meant.

tblCurrency.CurrencyID (pk) related to CurrencyIDfk in portfolio table. Query joins these 2 fields, the portfolio numbers come from tblPortfolio, the symbol from tblCurrency. No code needed to populate an unbound field for currency symbol regardless of how easy that might be. If form control alignment was an issue, I'd probably hide the bound fields and concatenate their values for display in an unbound control just to make it look nicer. Having a field for each currency is not something I'd do if that's what you are suggesting. Every currency that needs to be added means table/query redesign which is bad, and it's really not good enough to say it will never happen. You plan for what is possible, not so much for what is probable. The caveat might be that there is no data type that you can use for one field that gives you the precision you need. I'm thinking double, but I don't know. Link below might provide you with more info.

tblPortfolio				tblCurrency
PortfolioID	CurrencyIDfk	more fields		CurrencyID	Currency	Description
1	1			1	$	USD
2	1			2	£	Sterling
3	2			3	€	Euro
4	4			4	¥	Yen

http://allenbrowne.com/ser-44.html

[writeprivate]

A loose interpretation of what I meant.

tblCurrency.CurrencyID (pk) related to CurrencyIDfk in portfolio table. Query joins these 2 fields, the portfolio numbers come from tblPortfolio, the symbol from tblCurrency. No code needed to populate an unbound field for currency symbol regardless of how easy that might be. If form control alignment was an issue, I'd probably hide the bound fields and concatenate their values for display in an unbound control just to make it look nicer. Having a field for each currency is not something I'd do if that's what you are suggesting. Every currency that needs to be added means table/query redesign which is bad, and it's really not good enough to say it will never happen. You plan for what is possible, not so much for what is probable. The caveat might be that there is no data type that you can use for one field that gives you the precision you need. I'm thinking double, but I don't know. Link below might provide you with more info.

tblPortfolio				tblCurrency
PortfolioID	CurrencyIDfk	more fields		CurrencyID	Currency	Description
1	1			1	$	USD
2	1			2	£	Sterling
3	2			3	€	Euro
4	4			4	¥	Yen

http://allenbrowne.com/ser-44.html

Thanks very much for this - and the time you have taken.

Right now I am using a value list for the portfolio_currency i nthe portfolio table. But this was just for testing the form code. The plan is to create 1toMany to the existing tblCurrency which is it wired up elsewhere eg. the Currency Account, so we are thinking alike. Not sure why I didn't just link to start with - thanks for validating this approach.

No i don't have a field for each currency (I think that may have been a potential route in the opening post). Currently I push the currency symbol (via hard-code by looking up the tblCurrency) to the .Value of the unbound symbol textbox. Yeah - i know !! what am i thinking!!. I think i just liked the idea of looping through the tag property It was one function I call everywhere, so i felt i could update for more currencies in future (although i actually do know i will only use 4 currencies). But I take all your points and will update like this. It will be an easy rework as the nuts and bolts are there - and frankly its how i have wired up everything else so why not this.

I also like your idea of using an unbound concatenated field and hiding the bound control. I had wanted to do the same but thought this might be considered 'cheating' if i wanted to perform some runtime calculations on the hidden bound field for say a total on the form.

Really helpful brainstorm - thanks ever so much.

UPDATE: Just looked at my portfolio table and in the Description area for portfolio_currency, I had written this; "Switch this value list to be a tblCurrency fk and then check all the form operations still work as maybe number. Ditto check not looking for hard-coded string in Subs". Seems I forgot my own plan!!

[Micron]

Right now I am using a value list for the portfolio_currency i nthe portfolio table.

That sounds like you're using a table lookup field - many would say not a great idea, me included. However, part of that post suggests you won't be doing that going forward. Just in case you would:
http://access.mvps.org/access/lookupfields.htm

[writeprivate]

That sounds like you're using a table lookup field - many would say not a great idea, me included. However, part of that post suggests you won't be doing that going forward. Just in case you would:
http://access.mvps.org/access/lookupfields.htm

Thanks for your reply.

That link is about table lookups which I never use even for testing. I always use forms to lookup. As it's important to store the number Id not the text value. But the value list is a useful temporary way to select options while prototyping a new feature - ie before you've built the future related table even though it's text.

[Welshgasman]

. As it's important to store the number Id not the text value.

You are still doing that with table lookups, just that the IDs are hidden from you.

[writeprivate]

I'm still doing what? I don't use table lookups?

[Welshgasman]

. As it's important to store the number Id not the text value.

OK, One is still doing that with table lookups, just that the IDs are hidden from you.

[writeprivate]

Oh I see what you mean. Useful to know, although I don't like to use table lookups

[Welshgasman]

Oh I see what you mean. Useful to know, although I don't like to use table lookups

I have them in the very first DB I set up back in 2011 (did not know better at that time ) However I was aware of how they worked even back then.
I have left them in there as the DB is no longer under any development and data does not change. It was a one off project for a website.