468,505 Members | 1,756 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

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.

Solution:

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 3005

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 NPC403 | last post: by
3 posts views Thread by gieforce | last post: by
reply views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.