Type 1k for 1000, 1m for 1000000

[Jackson via AccessMonster.com]

Hi,

I've got a form where trades are booked, the quantity field is bound to a
table that the user is updating, and the underlying table field is a number
format (obviously for trade quantity). What I'm trying to work out is if I
can in someway have this field on the form handle typing 1K and then updating
to 1000 for example. The problem obviously is that this form text box is
bound to a numeric field that won't accept text, so if I write code into the
Before Update event to convert Ks and Ms to numbers, access says 'The value
you entered isn't valid for this field.'

A longer way round is for me to make the field unbound and code it in, I
already run an update query so this is feasible, just seems there would be
someway to do this whilst leaving the field bound as a numeric format....any
ideas?

Cheers.

 

[Allen Browne]

Try the KeyPress event of the control.

If the character is K or k, set KeyCode to 0, and assign the Text followed
by 000 as the Value of the control.

Use SelStart and SelLen to understand where the cursor is in the Text, and
to reposition the cursor again (if it was not at the end of the Text.)

You can do this with the Text property, because it has not become the Value
yet.

 

[Alec M1BNK]

How's about this

You hide the box, put an unbound textbox on the form, and use an afterupdate
event thus

Private Sub txtNumBox_AfterUpdate()
Dim intCharPos as Integer
If IsNull(txtNumBox) Then Exit Sub
intCharPos = instr(tNumbox, "k") 'Look for a k
if intCharPos > 1 then
intChatPos=intCharPos-1 'Trim off the k
'transfer 1000 * value of the digits to the left of the k
'to the hidden number box
numHiddenBox=VAL(Left$(txtNumBox,intCharPos))*1000
Exit Sub
End If
intCharPos = instr(tNumbox, "m") 'Look for a m
if intCharPos > 1 then
intChatPos=intCharPos-1 'Trim off the m
'transfer 1000000 * value of the digits to the left of the m
'to the hidden number box
numHiddenBox=VAL(Left$(txtNumBox,intCharPos))*1000000
Exit Sub
End If
'if no k and no m then transfer value of the digits
'to the hidden number box
numHiddenBox=VAL(Left$(txtNumBox,intCharPos))
End Sub