471,311 Members | 1,944 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,311 developers and data experts.

Access Textboxes - Limiting decimal place entry

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.


Private Sub Form_KeyPress(KeyAscii As Integer)
If InStr(1, Me.Controls(Me.ActiveControl.Name).Text, ".") <> 0 Then
If Me.Controls(Me.ActiveControl.Name).SelStart >= InStr(1, Me.Controls(Me.ActiveControl.Name).Text, ".") Or Me.Controls(Me.ActiveControl.Name).SelStart >= InStr(1, Me.Controls(Me.ActiveControl.Name).Text, ".") + 1 Then
If Len(Me.Controls(Me.ActiveControl.Name).Text) = InStr(1, Me.Controls(Me.ActiveControl.Name).Text, ".") + 2 Then
If KeyAscii <> 8 Then
KeyAscii = 0
End If
End If
ElseIf Me.Controls(Me.ActiveControl.Name).SelStart >= InStr(1, Me.Controls(Me.ActiveControl.Name).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.
May 25 '10 #1
0 3202

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by martin | last post: by
1 post views Thread by Ken | last post: by
2 posts views Thread by DamSexy | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.