Access Textboxes - Limiting decimal place entry

[jrworsham]

I just spent a day and a half searching forums for this solution and it does not look like it is out there yet, so I am sharing.

In access you can specify the amount of decimal places a form control displays with the Decimal Places property. You can also specify the amount of decimal places in the table design where the data is stored. Unfortunately, both of these do not limit the data at all. A control that has the decimal places property set to 2 can have an entry of 1.23456! Access accepts the entry.

This was killing some of my form calculations. I needed a solution that would not cause other events to fire, which is what happens if you simply alter the value of a control OnExit by using Round(). I also needed a solution that I could easily implement on 360 textboxes across 6 forms. Writing a Sub for each the limited keystrokes was not going to happen.

Solution:

Private Sub Form_KeyPress(K eyAscii As Integer)
If InStr(1, Me.Controls(Me. ActiveControl.N ame).Text, ".") <> 0 Then
If Me.Controls(Me. ActiveControl.N ame).SelStart >= InStr(1, Me.Controls(Me. ActiveControl.N ame).Text, ".") Or Me.Controls(Me. ActiveControl.N ame).SelStart >= InStr(1, Me.Controls(Me. ActiveControl.N ame).Text, ".") + 1 Then
If Len(Me.Controls (Me.ActiveContr ol.Name).Text) = InStr(1, Me.Controls(Me. ActiveControl.N ame).Text, ".") + 2 Then
If KeyAscii <> 8 Then
KeyAscii = 0
End If
End If
ElseIf Me.Controls(Me. ActiveControl.N ame).SelStart >= InStr(1, Me.Controls(Me. ActiveControl.N ame).Text, ".") + 2 Then
If KeyAscii <> 8 Then
KeyAscii = 0
End If
End If
End If
End Sub

The code above is fired from the FORM’s KeyPress event and acts on all controls on the form. It determines the Text value of the ActiveControl and the cursor location in the control. Using these, it will allow or ignore the keypress based on the cursor location relative to the location of the "."

To work, the Key Preview property of the form also needs to be set to Yes.

Hope this helps somebody.