Formatting Currency Fields in real time as user types 1

[CautionMP]

Working for a bank almost all data input by my database users are currency. Does anyone know a way to have Access add the comma seperators in real time as the user enters a number, alot of the amounts we enter are round numbers over $1mm and counting zeros is a pain. I did try to use a mask but any extra unused commas looks tacky.
Thanks in advance,
Brett

 

[KyleS]

Brett,
You could set the Input mask after update like this:

Private Sub txtMoney_AfterUpdate()
Select Case txtMoney
Case Is > 1000000000
txtMoney.InputMask = "999,999,999,999.99"
Case Is > 1000000
txtMoney.InputMask = "999,999,999.99"
Case Is > 1000
txtMoney.InputMask = "999,999.99"
Case Is < 1000
txtMoney.InputMask = &quot;999.99&quot;
End Select
End Sub

And to make it adjustable just set it to nothing when it gets focus:

Private Sub txtMoney_GotFocus()
txtMoney.InputMask = &quot;&quot;
End Sub


Kyle :

 

[KyleS]

Ben,

The code above could work with some adjustments to your liking but ignore it as there are better ways (sorry for that, I'm having a day over here let me tell you...) but if you just set the field format to curency, it should take care of your issue.

The only way to get it to format as the user types would be to have the OnChange adjust on every keystroke, and that will also look tacky...

Sorry for the initial bad response...

Kyle

 

[CautionMP]

Setting the field format to currency only updates after the field looses focus, my goal is for the user to tell the difference between 1,000,000.00 and 100,000.00 without having to leave. The select statement looks promising. Thanks for the info.

 

[KyleS]

Ok,
Glad I could help!

Try it, and I think you're going to want it &quot;OnChange&quot; as &quot;AfterUpdate&quot; doesn't work until the field has lostfocus. Let me know how it works out or if you have any more questions!

Kyle

 

[CautionMP]

Just a follow-up if your good with JS these routines could be converted to VBA and used on the OnKeyUp event.
function dollarFormat(element){
if(event.keyCode == 37 || event.keyCode == 39 || event.keyCode == 36 || event.keyCode == 46) return;
str = element.value;
var decimalIndex = str.indexOf('.');
if(decimalIndex != -1) {
integer = str.substring(0, decimalIndex);
decimal = str.substring(decimalIndex+1);
str = integer;
}
val = removechar(str,',');
if (val.length < 3) return true;
len = val.length;
count = parseInt(len/3);
if(len%3 == 0)
count = count - 1;
for(i = 1; i <= count; i++) {
index = len - (3*i);
val = val.substring(0,index) + ',' + val.substring(index);
}
if(decimalIndex != -1)
element.value = val +'.' +decimal;
else
element.value = val;
return true;
}


function removechar(str,ch){
str = str.toString();
while(str.indexOf(ch) != -1) {
before = str.substring(0,str.indexOf(ch));
after = str.substring(str.indexOf(ch)+1);
str = before + after;
}
return str;
}