Access 2003 - Notification of new record created

Hi,

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.

(°v°)

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

Select allOpen 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

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?

thx...Bob

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.

Cheers!
(°v°)

" 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.

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.
thx...Bob

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

Welcome!