• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

How to code After Update Event as =NumericOnly([Me]) INSTEAD of =NumericOnly([homePhone])

I have a Form that maintains 10 types phone numbers (homePhone, cellPhone, faxPhone, bestPhone, etc etc)

All 10 boxes are designed to discard non numeric data. (This allows cut and paste of email text like  "type 9 then 555 123.2795").

When designing the form I used control click to  group all 10 controls, then applied the following properties "one fell swoop".
1) Format tab >Format: (@@@) @@@-@@@@
2) Data tab > Enabled: Yes
3)              > Locked:No
4) Event tab> After Update: =numericOnly([Me])
That numericOnly([Me]) did not work. When I ran the form it gave << The object does not contain the automation object 'me' >>
I then tried =NumericOnly([name]) and that failed because it passes the name of the control, not the control itself.

I fixed the problem by changing all 10 textboxes "one by one".  For instance =numericOnly([homePhone])

But, I vaguely remember some sort of trick that would allow all 10 events to be set "one fell swoop".  Can anybody refresh my memory on this.
  • 2
  • 2
1 Solution
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
numericOnly must be a custom function to to what this does ... here is a general function (put into a standard module so anything can call it) to return only numbers from a string:
Function GetDigits(psStringWithNumbers As String, Optional pBooStopIfCharAfterDigit As Boolean = False) As String
'080806, 171012 s4p
   '  psStringWithNumbers is the string with possible digits to extract
   '  pBooStopIfCharAfterDigit is True iof you want to stop as soon as it is not empty even if there are characters after digits;
   '        False if you want all digits from the string regardless of what might be between them
   Dim sNumber As String _
      , i As Integer _
      , sChar As String * 1
   sNumber = ""
   'loop through and only keep numeric characters
   For i = 1 To Len(psStringWithNumbers)
      sChar = Mid(psStringWithNumbers, i, 1)
      If IsNumeric(sChar) Then
         sNumber = sNumber & sChar
         'uncomment if you don't want to stop looking
         If pBooStopIfCharAfterDigit Then
            If Len(sNumber) > 0 Then
               GetDigits = sNumber
               Exit Function
            End If
         End If
      End If
   Next i
   GetDigits = sNumber
End Function

Open in new window

you can call a private sub with code:
   Dim sDigitsOnly As String
   With Me.ActiveControl
      If IsNull(.Value) Then Exit Sub
      sDigitsOnly = GetDigits(.Value)
      If Len(sDigitsOnly) > 0 Then .Value = sDigitsOnly
   End With

Open in new window

on the AfterUpdate event of each control you want to extract digits from

have an awesome day,
rberkeConsultantAuthor Commented:
That was almost perfect and gave me the clue I needed.  Your solution used Me. which meant it would need to be duplicated in every form.

Instead of using me.activecontrol, I used screen.activecontrol, and got a solution that supports all forms in the project.

I grouped all 10 controls in MyForm1 and changed them all in "one fell swoop"

After Update:    =NumericOnlyThis()

And the following code into a standard module.
  Function NumericOnlyThis()
    Call NumericOnly(Screen.ActiveControl)
  End Function

Function NumericOnly(ByRef control As Object)
Dim sOut As String
Dim sIn As String
Dim i
If control Is Nothing Then Exit Function
If IsNull(control) Then Exit Function
sIn = control
For i = 1 To Len(sIn)
    Select Case Mid(sIn, i, 1)
        Case "0" To "9": sOut = sOut & Mid(sIn, i, 1)
    End Select
Next i
sOut = Left(sOut, 10)
control = sOut
End Function

Open in new window

Thanks a bunch
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
rberkeConsultantAuthor Commented:
for future researches, I think the following comments improve the code a bit

 Function NumericOnlyThis()
' put this in a standard module

' Users often cut and paste telephone numbers into their forms.
' Some time the text is ugly like  << call me at 555 (optional area code) then 123=4567 >>.
' this generic routine can be placed in the After Update event of any form.
'  This works best on text fields.  Textboxes that maintain numeric table columns probably won't work.

    Call NumericOnly(Screen.ActiveControl)
  End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now