CODEWITHSUNDEEP
vbams-access
user10423230
user10423230

Reputation:

Access VBA put a form element name as an argument

Let's see if anyone knows how to solve this problem:

I have a form with several elements: Some of them are textboxes called A1, A2, A3, A4...

Now, their AfterUpdate SubProcedure is extremely long but barely similar for each of them: A1_AfterUpdate, A2_AfterUpdate, A3_AfterUpdate...etc... are very similar but for the names of the textboxes they change.

My idea was to gather all that was equal in a subprocedure defined this way:

Private Sub Update(Box As String, Menu As Boolean)
   If Menu=True{ 
   Me!Box.Text = "This is the text that is going to change"
   }
End Sub

So, the only thing I must do is to call it this way, for instance:

Update(A1, True)

But it doesn't seems to work. Any idea on how to reach this objective?

Upvotes: 0

Views: 91

Answers (2)

iDevlop
iDevlop

Reputation: 25252

If you use a function instead of a sub: 

Private Function UpdateCtl(Menu As Boolean)
   If Menu Then 
       activecontrol = "This is the text that is going to change"
   End If
End Sub

then you can call it directly from the control's AfterUpdate property: =UpdateCtl(True).
Simple and fast

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

Add a class module - I've called it clsTextBoxEvents.

Add this code to the class: 

Public WithEvents txt As Access.TextBox

Private Sub txt_AfterUpdate()
    MsgBox txt.Name & " has been updated."
End Sub

In your form module add this code: 

Public MyTextBoxes As New Collection

Private Sub Form_Open(Cancel As Integer)

    Dim ctl As Control
    Dim txtBoxEvent As clsTextBoxEvents

    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            Set txtBoxEvent = New clsTextBoxEvents
            Set txtBoxEvent.txt = ctl
            txtBoxEvent.txt.AfterUpdate = "[Event Procedure]"
            MyTextBoxes.Add txtBoxEvent
        End If
    Next ctl

End Sub

The MyTextBoxes declaration must be at the very top of the module.

This just adds the AfterUpdate event to all textboxes on the form. You'll probably want to refine that a bit to textboxes with specific text in the name, or controls that are in a specific frame on the form.

Upvotes: 2

Related Questions