Search code examples
vbams-accesstextbox

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

Solution

  • 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