Need my database to notify me

[warmunger]

I've got a form that i need to notify me everytime a new record is added through this form. Is this possible? How do i do it?

 

[Gazer44]

I've done something similar which works.

Set the Timer interval of the form to 10,000 (10 seconds) or whatever

& Put the following code into the 'On Timer' event.

DoCmd.Requery
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenDynaset)
rs.MoveLast
If vTableRecordCount <> rs.RecordCount Then
vTableRecordCount = rs.RecordCount
MsgBox &quot;New Record Added&quot;
End If
rs.Close
db.Close

Every 10 secs the form will check the Table to see if the record count has changed and throw up the mssg. (p.s. the mssg appears if any records are deleted as well, which for me, works OK)

cheers

 

[Maquis]

Do you mean notify you as the database designer, or do you mean notify whoever is currently using the form?

If it's the former then you can use something similiar to the following in the AfterUpdate event of the form. It sends you an e-mail.


Dim eTo As String, eSubject As String, eBody As String

eTo = &quot;youremailhere&quot;
eSubject = &quot;Form Update
eBody = CurrentDb.Name & &quot; form has been updated.&quot;
DoCmd.SendObject acSendNoObject, &quot;&quot;, acFormatTXT, eTo, , , eSubject, eBody, False
Maq
<insert witty signature here>

 

[warmunger]

Thanks a lot guys. However I still have a problem. Most properly me being stupid again. It gives me a error in the line

Set rs = db.OpenRecordset

Gives me error &quot;method or datamember not found&quot;

 

[Gazer44]

I gather you've put in the code exactly as i gave with the below line having the relevant TableName in it:-

Set rs = db.OpenRecordset(&quot;TableName&quot;, dbOpenDynaset)

and you've got the line - Dim rs As Recordset - in as well.

should be OK ??
(p.s. you shouldn't need the line - DoCmd.Requery - that was for my own database)

 

[warmunger]

I did insert my own table name in the line. I struggeld with the &quot;dim db as database&quot; line to but i did not have the right references selected. So after selecting the sql references that came right.

 

[warmunger]

I did insert my own table name in the line. It looks like vb don't reconize the OpenRecordset as a valid command or something. When I try to run the code it highlits that specific line. After you type db. vb gives you a drop down list with commands and the OpenRecordset command is not available.

 

[Gazer44]

At the very top of your VB code after the lines :-

Option Compare Database
Option Explicit

add this line in:-

Dim vTableRecordCount As Long

(sorry, forgot about this in first answer)

 

[MichaelRed]

I'm guessing that there is some discrepancy between DAO.DAtabase (& Recordset) and ADO.Database (& Recordset) MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over