CODEWITHSUNDEEP
formsvbams-access
johnny93
johnny93

Reputation: 225

Run a function when any textbox changes in a form? MS Access

Is there a way to run a function in VBA the moment data in any control element changes? I've tried Form_AfterUpdate and Form_DataChange but they seem not to do anything

Upvotes: 4

Views: 4248

Answers (2)

Schalton
Schalton

Reputation: 3104

Step 1: Create a function

Function DoStuff()
    Call RunMySub
End Function

Step 2: Create a Macro (Named RunMyCode)

RunCode
  Function Name DoStuff()

Step 3: Modify the Form_Load() sub

Private Sub Form_Load()
Dim cControl As Control
On Error Resume Next
    For Each cControl In Me.Controls
        if cControl.ControlType = 109 'this is for text boxes
            'Depending on what your code does you can use all or some of these:
            cControl.OnExit = "RunMyCode"
            cControl.OnEnter = "RunMyCode"
            cControl.OnLostFocus = "RunMyCode"
            cControl.OnGotFocus = "RunMyCode"
            If cControl.OnClick = "" Then cControl.OnClick = "RunMyCode"
        end if
    Next cControl
On Error GoTo 0

You can use any of the attributes from the control I find the pairs of 'OnExit/OnEnter' and 'OnLostFocus/OnGotFocus' to be the most effective. I also like 'OnClick' but I use the if statement to not overwrite actions (for buttons and stuff). There are a dozen other methods you can assign the control action to -- I'm sure you'll be able to find one/several that meet your goal.

Note -- I use the on error enclosure because I wrap this code around multiple different types of controls and not all have all of the methods.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91316

You do not have to code After Update/Change event of the controls, check out Key Preview

You can use the KeyPreview property to specify whether the form-level keyboard event procedures are invoked before a control's keyboard event procedures. Read/write Boolean.

Use it carefully.

For example, with KeyPreview on:

 Private Sub Form_KeyPress(KeyAscii As Integer)
      MsgBox "You pressed a key"
 End Sub

key preview

Upvotes: 3

Related Questions