IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Please note: The solution discussed in this article only works for tables, queries and _bound_ form/report controls.
Today I was reminded of the age old problem of the Currency field datatype in Access not reflecting the current users regional formatting settings as set in the Operating System.
The problem is that by default, Access stores the format as specified on the developers machine and does not change the formatting if an end user has a different currency format set in their regional settings.
Some would see this as expected behaviour. Some would see this as a flaw (or even a bug). The Access helpfile doesn't help much since the behaviour does not appear to match what is described.
For example, if I use the currency datatype on a table on my machine which has UK regional settings (so pound currency symbol £) and then later sell the application on to someone in the US (with US regional settings), some people would expect the formatting to be shown in dollars on their machine - but that is not the case.
Internally Access interprets the text format 'Currency' and actually stores the resolved format as specified by your regional settings. So in this case, Access actually stored the Format property as '£#,##0.00;-£#,##0.00'.
This issue is further emphasised by this seemingly wrong behaviour (to many people, anyway) also occurring on forms and reports when specifying the Currency format in controls.
If you want your currency formatting to always match the current users regional settings, then fortunately there is an easy fix.
The fix is to still use the Currency data type, but _remove_ the format property in the Access field properties list:
Since you're now not storing any specific Currency formatting, Access uses the current users settings rather than yours.
You can take advantage of this in forms and reports too - but only if you are using bound controls. To make use of this in forms and reports, you must remove the Format property for your currency controls (leave the property blank) and the format will again be picked up from the users regional settings (as long as you also do the fix from above for the underlying bound field format as well).
If you use unbound controls, unfortunately you must set the format to "Currency" at runtime using a VBA subroutine. To do this, the simplest option is to loop through each control on the form/report and for any currency control set the format to "Currency". To identify whether a control holds a currency value, you need to either use the Tag property on the control or personally I set the format property to "UserCurrency" in the Access property sheet and then identify them that way instead.
Put this code in a standard module:
Public Sub FixUpCurrencyControls(ByRef FormOrReport As Object) Dim Ctrl As Access.Control For Each Ctrl In FormOrReport.Controls If TypeOf Ctrl Is TextBox Then If Ctrl.Format = """UserCurrency""" Then Ctrl.Format = "Currency" End If End If Next End Sub
And then in your form/report, use the OnOpen event to call our subroutine:
Private Sub Form_Open(Cancel As Integer) Call FixUpCurrencyControls(Me) End Sub
Have your say - comment on this article.
What did you think of 'Using the Currency field data type - without the hassle'?
|Ken Owen says...|
24 August 2009
I may have accidently given you 3 stars when I meant to give you 5. Thank you for providing this solution.
|Wayne Phillips says...|
24 August 2009
No problem Ken -- I've adjusted it for you. Thanks for your comments.
|Patrick Wood says...|
08 January 2010
Thanks for the excelent and well written article. I live in the USA and have a client in the UK so this is very helpful for me. I have found that dates can be a problem area also since in the USA we typically use mm/dd/yyyy and in the UK dd/mm/yyyy is the norm.
|Ian Van Audenhaege says...|
30 January 2011
Thank you for this solution. I live is an English/French area of Canada, so it is important that the currency formatting to always match the current user's regional settings. This solved the problem for me.
Have your say...