[Last Call] Learn how to a build a cloud-first strategyRegister Now


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
  • 2
  • 2
LVL 23

Accepted Solution

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

ID: 42329100
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 23
ID: 42329110
you're welcome ~ happy to help

Author Comment

ID: 42329118
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

830 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