Number formatting problems

[Sophie]

Salutations
I am having big problems with the format of numbers in txtboxes. Here is my
situation using easy numbers: 4 txtboxes accept numerical values. Their
table has Format Property 0.000 and Decimal Places 3. A 5th txtbox shows
the sum, like the column at left. Now, I need to be able to stop workers
from entering, say 3.9999. When they do, we get the column at right.

5.000 5.000
3.999 4.000
1.000 1.000
2.000 2.000
--------- --------
11.999 12.000

I know this sounds dumb, but the 'groundrules' over which I have no control
are that ONLY 3 decimals are allowed and NO ROUNDING is to take place.
***So, entering 3.999999999999 must just give 3.999. Also, I don't mean that
just 3 decimals are displayed and the others aren't, but that the actual
value of the number is 3.999 (no other non-zero decimals, showing or not).
This is important because these values are used in subsequent calculations.

I can actually 'fake' the result using strings, but I need these values to
be numbers.

 

[John Nurick]

Hi Sophie,

One way is to put something like this in the textbox's BeforeUpdate
event:

With Me.ActiveControl
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
Cancel = True
End If
End With

If you want feedback while the user is typing rather than when they move
away from the textbox, you could use a similar test in the KeyPress or
Change event.

 

[Sophie]

John - thanks! This code is a big help.

 

[Sophie]

John - you're code works awesome in the BeforeUpdate event, but...

Instead of using this in 18 textboxes, Ive now been trying to put your code
in a Sub called by each each textbox's BeforeUpdate event. Something like...

Private Sub txtTestA_BeforeUpdate(Cancel As Integer)
Call CheckDecimalPlaces("txtTestA")
End Sub

Private Sub CheckDecimalPlaces(CheckControlName As String)
With Me!CheckControlName
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
Cancel = True
End If
End With
End Sub

I think the problem is that the Cancel in the second sub is not defined.
I've tried lots of remedies, but I can't seem to get the syntax right. Any
clues?

Great thanks

 

[Daniel]

Hi Sophie,

You'll need to modify the CheckDecimalPlaces function so that it
'tells' the BeforeUpdate function whether it should cancel or not.

Something like this should work (air code):

Private Sub txtTestA_BeforeUpdate(Cancel As Integer)
Cancel = CheckDecimalPlaces("txtTestA")
End Sub

Private Function CheckDecimalPlaces(CheckControlName As String) As
Boolean
'returns true if there was a problem
CheckDecimalPlaces = False
With Me!CheckControlName
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
CheckDecimalPlaces = True
End If
End With
End Sub

Daniel

 

[John Nurick]

You can just do this:

Private Sub CheckDecimalPlaces(ByRef Cancel As Integer)
With Me.ActiveControl
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
Cancel = True
End If
End With
End Sub

and call it from each textbox's BeforeUpdate event procedure with:

CheckDecimalPlaces Cancel