473,396 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,396 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.

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 3580

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

Similar topics

3
by: intl04 | last post by:
Is it possible to create a Word form as the data entry form for an Access database? I didn't see any reference to this possibility in my Access books, so I'm sorry if this is a question that is...
1
by: martin | last post by:
Hi, I am having difficulty trimming any traling 0's from my decimal numbers. I store then in the database to 2 decimal places. This means that when they come back to my application they still...
1
by: Ken | last post by:
HI all I would like to round up a double number, for example, 100.689 and round up to 100.6 i know i can do it with Round function. However, it doesn't show the any decimal place if the number...
0
by: pomeroymiles | last post by:
I have a pivotchart which I cause to take a huge list of people's test percentiles and calculate the average for each year. I then display the average above each bar. The problem is that there are...
3
by: flickimp | last post by:
Hi Got various fields in T-SQL where a decimal point and a zero follows each value: ie: 46.0 54.0 234.0
2
by: DamSexy | last post by:
Im doing some work for uni and I am a beginner at javascript. This is the code that I have: var canvas; canvas = openGraphics(); var radius; radius = 20; var area; area =...
3
by: George Eapen | last post by:
Hi all, i want to check whether a value entered into a specific html field is numeric as well as should contain one decimal value.( eg.23 is not accepted..It should be 23.0) i have come up with...
1
by: maharajakecil | last post by:
hello VB expertise. how should i modify my source code if i want change my X and Y value into one decimal place like 1.2,1.3,1.4 and so on. thank for the help. i really appreciate it! Private...
1
by: chinni0719 | last post by:
Can you plz tell me how to truncate zeros after decimal the case is like this round(992.55,0) result =993.00 round(993.888) result = 994.000
5
by: MyWaterloo | last post by:
I need to change the decimal value in one of my databases. Currently my company uses an ID system with nice whole numbers, i.e. no decimal numbers. Now my boss has this grand scheme to start using...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.