VBA to insert currency

W

Wayne-I-M

Has anyone created a VBA solution to inserting 12.34 into a field using VBA.
_ _ _ _ _ _ _ _ _ _ _
I have a touchscreen application that is used to complete various forms.
One of the forms consists of a few number and currency input boxes that are
used elseware in the application (they can’t be text).

For the numbers it is simple to add digits to a field formatted as general
number.

Me.FieldName = Me.FieldName & 1
Will add the digit 1 to whatever is in the box so 1234 will become 12341 etc

This of course does not work for currency as you can’t use & sign to add to
a currency field.

I have tried formating the table field to text then using CCur to knock it
back to currency AfterUpdate (this works except for the “.†– decimal point,
£12.34 – the dot before the 34 pence section).

I have also tried converting the control to text – inserting the text – then
back to CCur on AfterUpdate. Does not work ;-(

Has anyone created a VBA solution to inserting 12.34 into a field

Any help or tips pointing somewhere in the right direction would be really
appreciated (spent quite a while on this).
 
W

Wayne-I-M

Sorry may have been a little confusing with that the last post.

Basically I have a form with a number of buttons.
1 2 3 4 etc up to 9 and . (that’s a dot)

Depending on other factors on the form the buttons have an afterupdate event
to add the correpondng number to a text box.

If etc etc etc
Something then
Me.BoxName = Me.BoxName & 1 (or 2 3 4 etc)
End if

Dead simple.

This works fine in all events “except†currency when there is a need for the
dot. BUT this is text and the boxes are all either number or currency.

Me.BoxName = Me.BoxName & “.†(this does not work of course – just an
example)

This is the problem.

I am not “adding to a number 1+1=2 I am “joining†(sorry don’t know the
correct English word for this) 1+1 = 11 and 234+8 = 2348 etc This works
fine.

But

How do I insert the Dot after the second to the right digit.

I have tried all sorts of strange formatting and CCur functions but had no
success so I hope someone will have a idea I can work on
 
S

storrboy

I don't quite understand everything you're saying, but here is a
simple method of entering a currency value using command buttons. I
gave each button (0 to 9, decimal and a clear button) a Click event
of

AppendAmnt [digit]

[digit] being the number or decimal to add to a textbox. The clear
button passes a digit of "" (empty string) to clear the boxes.

The append function looks like

Function AppendAmnt(Digit As Variant)
If Digit = "" Then
Me!txtHide = ""
Me!txtAmnt = 0
Else
Me!txtHide = Me!txtHide & Digit
Me!txtAmnt = CCur(Me!txtHide)
End If
End Function

The txtHide box is not visible on the form and contains no formating
and is not bound. The digits are concatenated (that's the *joining*
word you were unsure of) into this text box when the AppendAmnt
function is called. The txtAmnt textbox is bound to a table or query
field. In my testing, I used a currency field. When the AppendAmnt
function is called, it first adds the digit to the hidden box, then
sets the value of txtAmnt to be the currency equivalent of txtHide.
The decimal is added and the string is correctly converted into
currency. At least as far as I tried it. You may need to test and
refine for your uses. I'm sure there is a more elegant way, but this
was quicker to come up with.
 
W

Wayne-I-M

Hi Thanks for the tip

Bits of code on thier own don't mean much (without the rest) so I won't post
them here. But your tip pointed me in the right direction so thanks for that.

Basically I creates a number unbound boxes and used the buttons to set thier
value and afterupdate CCur the vaue into the table fields.

Thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top