CODEWITHSUNDEEP
vbams-accesstextbox
Dan Kirchner
Dan Kirchner

Reputation: 47

Multiple text boxes to call the same VBA procedure

I am developing an Access Database and have several forms; all of which have the same text box on them.
The text box for each form is from the same record source, same name, same properties, etc. After the textbox is updated I have VBA running an Instr procedure which captures key phrases commonly used in these text boxes and replaces them with a common phrase.
How can I get each text box from each form to call the same procedure, that way if I have to improve the code over time I am only doing so in one place versus going to each form to update the code.

Example code.

textbox1_AfterUpdate()

Dim A as TextBox
Set A= Me.Textbox1

If InStr(A," Attachment Number ") Then
  Me.FunctionalArea.SetFocus
  A=Replace(A,"Attachment Number","<<Att."&" "& Left(Me.FunctionalArea).text,1)&""&"XXX>>")
  A=SetFocus
End If

If InStr(A, " Program Name ") Then
  A = Replace(A, " Program Name ", " <<ProgramNameXX>> ")
End If

If InStr(A, " Office Address ") Then
  A = Replace(A, " Office Address ", " <<OfficeAddressXX>> ")
End If

Upvotes: 0

Views: 1110

Answers (2)

freeflow
freeflow

Reputation: 4355

You just call the code with a parameter of the textbox.

Something along the lines of

Public Sub textbox1_AfterUpdate()

    DoTextBoxActions Me.Textbox1
    
End Sub


Public Sub DoTextBoxActions(ByRef ipTextBox As TextBox)

        If InStr(ipTextBox.Text, " Attachment Number ") Then
          ipTextBox.FunctionalArea.SetFocus
          ipTextbox=Replace(ipTextbox.Text,"Attachment Number","<<Att."&" "& Left(ipTextbox.FunctionalArea).text,1)&""&"XXX>>")
          ipTextBox.Parent.SetFocus = SetFocus
        End If
        
        If InStr(ipTextBox.Text, " Program Name ") Then
          ipTextBox = Replace(ipTextBox.Text, " Program Name ", " <<ProgramNameXX>> ")
        End If
        
        
        If InStr(ipTextBox.Text, " Office Address ") Then
          ipTextBox = Replace(ipTextBox, " Office Address ", " <<OfficeAddressXX>> ")
        End If
           
End Sub

Upvotes: 1

Albert D. Kallal
Albert D. Kallal

Reputation: 48964

You can do this.

When you place that text box on each form, in place of building a "event" code stub, you can enter this:

=MyFunctionName()

Or, thus in your case, you would place this code in a standard code module (NOT the forms code module).

Public Function MyGlobalAfterUpdate

   ' pick up the form and the control
   ' do this first, do this fast, do this right away
   ' since a timer event, mouse click, focus change etc. can
   ' case the screen.ActiveForm, and screen.ActiveControl to change
   ' once we grab these values, then you ok
   
   Debug.Print "global after"

   Dim MyControl    As TextBox
   Dim MyForm       As Form

   Set MyForm = Screen.ActiveForm
   Set MyControl = Screen.ActiveControl


   Debug.Print "Control name = " & MyControl.Name
   Debug.Print "Text of control = " & MyControl.Value

   Dim strText As String

   strText = MyControl.Value

   Debug.Print strText

   ' note that we have FULL use of the form values
   ' in place of me!Some value, or control?
   ' you can go
   MyForm.Refresh
   MyForm!LastUpdate = now()
   
   ' save the data in the form
   ' If MyForm.Dirty = true then MyForm.Dirty = False

End sub

So you are free to do whatever you want in this code. And you simple replace "me" the forms reference with MyForm, but once you grabbed the active form, then anything you would or could do with "Me", you can do the SAME with MyForm. As noted, you could in theory using Screen.ActiveForm, but you are MUCH better to pick up a reference as fast as possible and as soon as possible, since those values and focus could change, and often it will - so get/grab/take a reference to the controls as fast and as soon as possible. Once you grabbed the reference from screen, then minor changes in focus etc. don't matter - since you picked up the form and control right away.

The key concept, the key takeaway? you can grab both the current form with screen.ActiveForm, and you can get/grab the current control that fired the after update event with Screen.ActiveControl.

So, in summary:

Don't create a code stub in the form. In the controls after update event, place the name of the PUBLIC function in a STANDARD code module.

eg like this:

=MyGlobalAfterUpdate()

Upvotes: 0

Related Questions