A subform field that should not accept alpha characters

Fordraiders
Fordraiders used Ask the Experts™
on
A subform field that should not accept alpha characters.

I could do this the simple way and change the datatype of a field to numeric 19,0, but i cant right now.

So i have a field on a subform that i'm trying in every way possible so that anytime an alpha character is entered it will not accept it.
For the most part these work until:
A COPY AND PASTE SITUATION OCCURS.

this below is the code i have thus far,


Private Sub APPROVED_GP_AfterUpdate()
Dim var As Variant
   On Error GoTo APPROVED_GP_AfterUpdate_Error

If Len(Nz(Me.APPROVED_GP, "")) = 0 Then
   Exit Sub
End If


' checking for decimals
On Error Resume Next
var = Split([APPROVED_GP], ".", , vbTextCompare)

If Err.Number <> 0 Then
  '  Debug.Print "check"
Else
    counthypens = UBound(var)
    
    If counthypens > 1 Then
      MsgBox "You have entered too many decimals. Please Re-Enter", vbCritical, "Decimal Check"
      Me.APPROVED_GP.SetFocus
      Exit Sub
    End If
End If



Me!APPROVED_GP = Replace([APPROVED_GP], "%", "")
Me!APPROVED_GP = Replace([APPROVED_GP], "..", ".")


'Me!APPROVED_GP = Me.APPROVED_GP.Value / 100 * 100 & "%"

If IsNumeric(Me.APPROVED_GP) = True Then
    Me.APPROVED_GP.Value = Format(Me.APPROVED_GP.Value / 100, "0.00%")
    Else
    MsgBox "You have entered a Alpha character. Please Re-Enter", vbCritical, "Alpha Check"
    Exit Sub
End If

If Me!APPROVED_GP = "%" Then
Me!APPROVED_GP = ""
End If

   On Error GoTo 0
   Exit Sub

APPROVED_GP_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in field APPROVED_GP_AfterUpdate of Form dbo_t_redbook_pricing_escalation_detail_subform"

End Sub

Private Sub APPROVED_GP_KeyPress(KeyAscii As Integer)

   On Error GoTo APPROVED_GP_KeyPress_Error

Select Case True 'always your best friend
    Case (KeyAscii > 47 And KeyAscii < 58)
    Case (KeyAscii = 8)
    Case (KeyAscii = 43)
    Case (KeyAscii = 45)
    Case (KeyAscii = 46)
    Case (KeyAscii = 9)
    Case (KeyAscii = 13)
    
    Case Else
        MsgBox ("For Approved_Gp You Must Enter Numbers Only!")
        KeyAscii = 0
        Exit Sub
End Select
KeyAscii = KeyAscii


   On Error GoTo 0
   Exit Sub

APPROVED_GP_KeyPress_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Field APPROVED_GP_KeyPress of Form dbo_t_redbook_pricing_escalation_detail_subform"

End Sub

Open in new window


any help is greatly appreciated.

Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
What about applying a validation rule, refer to http://allenbrowne.com/ValidationRule.html
John TsioumprisSoftware & Systems Engineer

Commented:
I suggest using a combination of Change event and BeforeUpdate/Undo to control what you type and what you paste...
In the case of Change event you check each character as typed and if is invalid...you take it 1 char back..
in the case of BeforeUpdate you just cancel everything until the accepted characters are inserted
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I would use the BeforeUpdate event. Something like this:

Private Sub YourField_BeforeUpdate(Cancel As Integer)

    Const MaxDecimals   As Integer = 1

    If Not IsNull(Me!YourField.Value) Then
        Cancel = Not IsNumeric(Me!YourField.Value)
        
        If Cancel = False Then
            ' Field is numeric. Check decimals.
            Cancel = (Len(Mid(Str(Abs(Me!YourField.Value - Fix(Me!Felt1.Value))), 3)) > MaxDecimals)
        End If
    End If
    
    If Cancel = True Then
        MsgBox "Please correct input."
    End If

End Sub

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Personally, I prefer not to put validation code in events related to a single field.  Instead, I use the Form_BeforeUpdate event, which allows me to examine any number of validation rules in a single location, cancel the update if necessary, and display a meaningful message to the user.

Just a personal preference.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Dale,

I typically agree.  I normally stay completely away from input masks and validation rules.  VBA offers much more control and custumizations.  That said, in this case, a simple validation, such as Is Null OR Not Like "*[!0-9]*", quickly remedies the situation and avoids a whole lot of code.  Then again, the code can give the ability to give the user a custom message, sanitize the input value, ...  So it's a choice.

Isn't that the beauty of Access, multiple ways to approach so many things depending on the need, developer's capacities and desired final solution.
Distinguished Expert 2017

Commented:
If the field is bound and defined as numeric, Access won't let you insert invalid values.  Validating in the form's BeforeUpdate event will give you better control over error messages but otherwise isn't needed.

Author

Commented:
Just to verify,

Will the  BeforeUpdate  event fire off when a copy and paste occurs in the subform ?

fordraiders
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes. Check it out.

Author

Commented:
Gustav what is this ? what field name should thie  be ?

Fix(Me!Felt1.Value))), 3)  

fordraiders
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
APPROVED_GP, I guess.

Author

Commented:
GUSTAV,  I need to allow decimals in this field also...sorry

3.34
1233.44

etc...
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That's what the constant is for. So:

Const MaxDecimals   As Integer = 2

Open in new window

Author

Commented:
gustav, I changed the constant to 2 but when i enter a value with a decimal  like 33.32  i get the error message ?
John TsioumprisSoftware & Systems Engineer

Commented:
Maybe you need to perform some Round first

Author

Commented:
I need it to keep the actual value   33.32   ?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
That's a floating point error. Apply CDec:

Private Sub YourField_BeforeUpdate(Cancel As Integer)

    Const MaxDecimals   As Integer = 2

    If Not IsNull(Me!YourField.Value) Then
        Cancel = Not IsNumeric(Me!YourField.Value)
        
        If Cancel = False Then
            ' Field is numeric. Check decimals.
            Cancel = (Len(Mid(Str(Abs(CDec(Me!YourField.Value) - Fix(Me!YourField.Value))), 3)) > MaxDecimals)
        End If
    End If
    
    If Cancel = True Then
        MsgBox "Please correct input."
    End If

End Sub

Open in new window

Author

Commented:
thanks all !!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today