We help IT Professionals succeed at work.

Access 2003 - Notification of new record created

bobrossi56 asked
Looking for ideas experts. Working on a DB that is used by a company that owns 300 school buses. The main office is responsible for the repair and maint of all these busses. The busses are parked in 8 different locations across the state.When a bus breaks,  each manager from the 8 offices creates a record using the Defect Report form I developed, stating the bus#, mileage, and problem.

The fleet manager sitting in the main office is at any one time juggling repairs of 15-20 busses. He says this DB structures all this better for him, but he asked me if there was a way for Access to notify him every time a newDdefect record is created. Currently he has to keep running a report of NEW defects, but he wanted an easier, on-screen visual way to see when a new record is created.

Is this even possible?
thx experts...
Watch Question


You could have a form with a timer event, either sitting somewhere or even hidden, that will run a quick DLookup every five minutes to find the latest ID number of the DefectReport table. When the number is higher that the one remembered, it flashes something, or simply becomes visible.

Option Explicit

Dim lngKnownDefect As Long

Private Sub Form_Timer()

    Dim lngNew As Long
    lngNew = DMax("ID", "DefectReport")
    If lngNew > lngKnownDefect Then
        ' do something, e.g.
        Me.Visible = True
        lngKnownDefect = lngNew
    End If
End Sub

Open in new window


OK, that would work, but I am not a VBA guy so I don't completely understand what I would have to edit in your code example to make it work with my DB. The current form the managers fil out is called DefectReport and it does have a field called ID (autonumber field). I see that mentioned in your code, I assume I just leave that as is?

Does it matter what I call the form that will pop-up with the timer event?

thx, Bob
Hmm. If you are not a VB guy, let's no go into hidden forms and stuff like that.

I prepared a small demo. Open it, the monitoring form should appear and remain visible throughout the session. Open the table and add a record (it must be saved, naturally). After at most five seconds (timer setting: 5000 milliseconds), the monitoring form should capture that event. Pressing OK clears the message, but continues to monitor.

I hope you can adapt this to your needs.



This is a cool demo harfang. I think this would work. Just 2 questions...
1. I noticed when I created a form in your demo to enter a defect (vs the table entry) I had to put a SAVE button on the form for it to work. I was always under the impression that as soon as a user clicked the ADD RECORD button, the record was created, AND saved. What does the SAVE button do and why do I need it for this, but don't usually need it when a new record is entered?

2. The fleet manager is VERY busy, and does not stare at his monitor, so 5 new records could be created since the last time he looked at the monitor and clicked OK. is there a simple way to capture ALL the new records since the last time he clicked OK, vs. just showing the latest one?

In the Access interface (tables, queries, forms) a record is saved when: the cursor leaves the record (navigates to another record), the record selector is clicked, on Shift+Enter, when closing the object, etc.

To see it happen, make sure the record selector is visible. It will display a pencil for a record that has been modified or created but not yet saved.

As for 2., you need another global variable. Besides "known defect", add "seen defect", which gets updated only when OK is pressed. The message can then show a range: "defects 34-37 detected".

At the moment, the signal is more like a "you got mail" indicator. You know you have to open something, look somewhere else, to see the details.

Hamed NasrRetired IT Professional

" but I am not a VBA guy"..
I like harfang's approach.
I am sending a sample database with a different approach.
Check if it helps.
Retired IT Professional
Form to enter, and after update it displays the new records in a subform. You click monitor, or accept all.



hnasr, appreciate your efforts, but I can't get it to work. Actually both forms look the same to me. When I open the monitor form, and then open the entry form, and I add a new record, the monitor form does nothing. I am sure I am doing something wrong, but not sure what. What does the accept all button do, and what is the checkbox monitored for? I am confused.
Hamed NasrRetired IT Professional

No problem and no confusion!

A new field monitored is added to the table with value No (0) that determines the record is newly created. When looked at you can change it to -1 (Yes) and it becomes old. You can change all the records by clicking Accept all button.

Database has 3 objects.
1 table: a_h1
   aID      adesc      monitored
   1      11      Yes
   monitored is a Yes/No field that is 0 by default, and it shows in the subform as a new record. When you click monitored checkbox  the record disappears. Accept all modifies the monitored field to -1 (Yes) and all records disappear from new (monitored records in subform)
2 main form: frm_a_h1_entry: This includes the subform to display new added records.
3 subform: frm_a_h1_monitor ( you don't need to open this subform because it is included in the main form)


thx much..BobR
Hamed NasrRetired IT Professional