New Record to Automatically Run a Macro

[vhacodavism]

I have created a macro in Access 2003 that will send a report of the last full record added to a table via e-mail. The table is actually being populated from an InfoPath form. What I would like to do is to have my macro run automatically when ever a new record is added to the table. Can anyone tell me how to do that?

[JConsulting]

I have created a macro in Access 2003 that will send a report of the last full record added to a table via e-mail. The table is actually being populated from an InfoPath form. What I would like to do is to have my macro run automatically when ever a new record is added to the table. Can anyone tell me how to do that?

Since tables don't have "events" that might pop some code or your macro to run when a record is added...there ARE a couple of options. But keep in mind Access is event driven. That means a click, doubleclick, mousedown, etc...triggers things to happen.

One other option that doesn't necessarily require one of the above is a simple solution.

If you create a small form you can set up an On_Timer event that can check the Count of records in your table every few minutes or so. If the number is greater than the last time you checked, then you can trigger your macro.

A form like this can be set to invisible ("acHidden") so that it's not intrusive to the rest of your application.

 
 
Option Compare Database
 
Dim oldcount As Long
Dim newcount As Long
 
Private Sub Form_Open(Cancel As Integer)
oldcount = DCount("*", "one")
End Sub
 
Private Sub Form_Timer()
newcount = DCount("*", "one")
If newcount > oldcount Then
     DoCmd.RunMacro "yourmacro"
End If
oldcount = newcount
End Sub