473,322 Members | 1,425 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,322 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 3570

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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.