By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Data Validation and Conversion in Form_Error

P: 4
Hey guys,

I have a form that has a series of number input fields for length and width of items to be ordered. The users are asking to be able to input fractions instead of decimals. I've already got it so they can input a fraction by double-clicking in the field, but that's cumbersome. Instead, I've been trying to trap the error in the Form_Error event and convert the fraction to a number there. I can make this work, but it requires that the user hit <enter> a second time... which is frustrating. If I try to manually push focus to the next control I get an error.

The current code is below. I've tried lots of different versions, setting the Dirty flag to false, refreshing the data, whatever. I can force the value in (generating another error in the process) by putting it into the control's Text value... But, I haven't found anything that really works.

My final backup plan will be to overlay some unbound controls on the form and just get my data from there. Sadly, that just *feels* sloppy.

Any ideas about how to set the value in the control and move to the next control would be most welcome!

Thanks!
--Jim C

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim str As String
Dim d_In As Double


Select Case DataErr
Case 2113
Set ctl = Me.ActiveControl
If ctl.Name = "boxDLOLength" Or ctl.Name = "boxDLOHeight" Or ctl.Name = "boxLength" Or ctl.Name = "boxHeight" Then
str = ctl.Text
d_In = FtInchStrngToDouble(str)
If d_In > 0 Then
ctl.Undo
Response = acDataErrContinue
On Error Resume Next
ctl.Text = d_In
If Err Then Err.Clear
Me.boxHeight.SetFocus
End If
End If
Case Else
End Select
End Sub
4 Weeks Ago #1

✓ answered by MrXmas1967

Finally found it. I worked my way through every event backwards until I found one that happened JUST PRIOR to the data validation check.

Here's what worked:

Private Sub boxLength_KeyDown(KeyCode As Integer, Shift As Integer)

Dim d_In As Double

If KeyCode = 13 Then
If Not IsNumeric(boxLength.Text) Then
d_In = FtInchStrngToDouble(boxLength.Text)
If d_In > 0 Then

boxLength.Text = d_In

End If
End If
End If
End Sub

This waits until the <ENTER> is pressed, and catches the control's TEXT property prior to validation. If it's not numeric, it tries to convert the string to a double.

Whew. Thanks for letting me pester you with this.

Share this Question
Share on Google+
5 Replies


P: 65
You will have to change the input to accept plain text.
Then, when done, convert the expressions to decimals.

To convert fractions (inches, I guess) you can use my functions found at GitHub (too extensive to post here): VBA.Round

Browse for the paragraph:

Converting between meters and inches

The functions provided will handle very large and very small values for inches:

Expand|Select|Wrap|Line Numbers
  1. from 7922816299999618530273437599 
  2. to 1/2097152 or the decimal value 0.000000476837158203125
The format of the imperial output covers a very wide range:
  • Feet and inches, or inches only
  • No fraction for a numerator of zero
  • No fraction at all
  • Dash or no dash between feet and inches
  • Only feet if total of inches is 12 or more
  • Zero feet if total of inches is smaller than 12
  • No inches if feet are displayed and inches are zero
  • No units
  • Units spelled out as ft, ft., or foot/feet and in, in., or inch/inches

It is the module Imperial.bas to study.
4 Weeks Ago #2

P: 4
Thanks. My function to convert from fractions to decimal works ok.
I was keeping the idea of an unbound overlay as my last resort. The idea of using a bound text field is in the same basic area. I'd like to avoid storing text -- it just feels sloppy.

Everything works fine on my code with the exception that the user has to hit enter a second time if they input a fraction.

Appreciate you looking at this however.
4 Weeks Ago #3

P: 65
No reason to feel sloppy.
If text is what you have, then store it (temporarily) as such.
4 Weeks Ago #4

P: 4
I've been storing numbers all alone. I use them to calculate price and whatnot. I can intercept the error at the Form_Error event and correct it. It just requires the user to hit enter that second time. I feel like I'm missing something.
4 Weeks Ago #5

P: 4
Finally found it. I worked my way through every event backwards until I found one that happened JUST PRIOR to the data validation check.

Here's what worked:

Private Sub boxLength_KeyDown(KeyCode As Integer, Shift As Integer)

Dim d_In As Double

If KeyCode = 13 Then
If Not IsNumeric(boxLength.Text) Then
d_In = FtInchStrngToDouble(boxLength.Text)
If d_In > 0 Then

boxLength.Text = d_In

End If
End If
End If
End Sub

This waits until the <ENTER> is pressed, and catches the control's TEXT property prior to validation. If it's not numeric, it tries to convert the string to a double.

Whew. Thanks for letting me pester you with this.
4 Weeks Ago #6

Post your reply

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