Send Pop-up message or email if a new record.

[mahenkj2]

Please help me for the below issue:

I have made a database for multi users. The working is such that 2 or 3 user will generate some requests through this dB and remaining user should know whenever new request are made. Since number of daily requests are few (2-3) and concerning department can come only to know once they open or refresh the dB.

What should be the best way to intimate when ever a new record is being added. Some thing like a popup or email may work! Please advise.

best regards.

 

[alsoascientist]

You could use the on timer event to refresh periodically using a form instead of a pop up - that way you can set it as modal and use the 'is open' property of the form to stop the automatic refresh until the user goes back in?

 

[mahenkj2]

Thanks for the support.
So, should I make a new form for this purpose? In any case, i know about on timer event location in form properties but hot to set it. I think some code might be needed.

May I request to kindly advise me in little bit detail.

best regards.

 

[mahenkj2]

I could not this job done yet. Can somebody help me out in this get through.

What should be the best way to intimate when ever a new record is being added. Some thing like a popup or email may work! Please advise.

 

[alsoascientist]

So assuming that you have a field for when each record is created [=Now()] then you should be able to use this:

Private Sub Form_Timer()
Static oldRecord As Date
Dim newRecord As Date
  dtmLastRecord = DMax("DateAdded", "TableName")
  If oldRecord > newRecord Then
    MsgBox "There was a new record added at " & Format(oldRecord, "hh:mm:ss")
  End If
  newRecord = oldRecord
End Sub

I believe this should work for you - I don't think you actually need to have a form as a pop up like I suggested - I think I was probab;y over complicating things! The only caveat with this is that it will only pick up one record (as in if a second record was added while the msg box is showing then this will not be checked until the msg box has been closed and the next timer event has fired.)

 

[mahenkj2]

I have Now() on my form. I have also changed DateAdded and TableName to my particulars. What about oldRecord and newRecord. Should they be kept like as it is or should be changed? I have autonumber ID which changes on new record.

 

[alsoascientist]

Sorry - should read as below - when I went through to check I realised I had it all the wrong way around!!

The newRecord/oldRecord are exclusive to the code so don't need to relate to anything in your table. As long as DateAdded is where you are keeping your =Now() inforamtion and the table name is correct this should work for you.

And yes, I guess you could adapt this to work off the ID by changing the DateAdded column to the ID column?

Private Sub Form_Timer()
'Static ensures that the previous value is carried over
Static oldRecord As Date
Dim newRecord As Date
 
'Sets the new record as the most recent value
  newRecord = DMax("DateAdded", "TableName")
 
'Checks if the new record is more recent
  If newRecord > oldRecord Then
    MsgBox "There was a new record added at " & Format(newRecord, "hh:mm:ss")
  End If
 
'Sets the new value as the old value as static
  oldRecord = newRecord

End Sub

 

[mahenkj2]

Many thanks. The code is working absolutely fine.

 

[alsoascientist]

Happy to help!

 

[mahenkj2]

With above help, I could make a code that works fine when a particular form is open. Whenever a new record is added, a pop up message appears.

My users now report that the pop up message appears only when the access application is open (active, and should not be minimized). If a new record is added, user does not know untill she clicks on access icon at taskbar and restored the access window.

What I need is that this message should appear on above whatever app/program is active. I hope I am able to explain with correct terminology. Also that particular form must be open, can this also be relaxed.

Kindly advise.

best regards,

 

[mahenkj2]

any thoughts on this please!

best regards.