Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2017-10-12
Medium Priority
Last Modified: 2017-10-12
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.
Question by:rberke
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 22

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
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,

Author Comment

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
LVL 22
you're welcome ~ happy to help

Author Comment

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Join & Write a Comment

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question