New record notification

[David]

Hi all,

I have another question regarding my database, I have posted before to find
out if Access can send an email to certain people when a record is submitted
to the database however it seems this is not possible. It has however become
more apparent when using this database that we need a way of notifying the
database operators when a new record has been submitted.

Is there anyway of popping up a new message in Access using scripting to say
there are new records or any way of notifying the operators of the new data?

Thanks

 

[Arvin Meyer [MVP]]

It is absolutely possible using code in any front-end. I've done it in
Access 2002 and 2003 and with an ASP front-end. It is not possible with a
JET back-end because there are no triggers. If you use a SQL-Server
back-end, you can have the database engine send the email.

 

[John J.]

It is not possible with a JET back-end because there are no triggers.

Can't you use a timer in the front end that checks for new records in the BE
every x minutes?
John

 

[David]

Hi thanks for the reply, I dont use any kind of backend the database is
relatively simple built purely in access 2007

If there is a way of doing it could you point me to any guides or if
possible tell me how to do it as I dont have much experience/knowledge with
access.

Thanks

 

[Arvin Meyer [MVP]]

You can, but since you can use a coded flag to detect changes made to a
field or new records added, there is no reason to waste CPU cycles and
performance by doing so.

Create a public flag in the form module and use the afterupdate event of a
control or controls, or the record itself, to fire off an email. I found
that I had to either use smtp or something like Redemption with Outlook to
avoid annoying email messages. But that was simple to do.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Klatuu]

Split your database. Access applications should always be split,
particularly when there are more than one user.
There a many reasons to do so, but to answer your question.

You could have a form that is always open with a timer event set to check
the recordcount of your table periodically and run a procedure when the
recordcount has increased; however, unless you have some way to identify the
new record, it is pointless.

 

[Arvin Meyer [MVP]]

You should only be using a form for data entry, never succumb to the
supposed ease of entering data directly into table. That will cause untold
problems with good database data integrity. Also, never run the coded
(front-end) part of the database from a server. It will cause performance
problems with a single user, and there's a good chance of corruption if
there are multiple users.

In a form, create a public flag in the form module and use the AfterUpdate
event of a control or controls, or the record itself, to set the flag and
subsequently fire off an email. For instance, if when the data in either of
2 textboxes is either changed or added, you want to send an email, but not
send it if any other control is changed, use the AfterUpdate event of each
of those textboxes to set the value of the flag to true. Then use the
AfterUpdate event of the form to send the email, and reset the flag to
false.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David]

Not entirely sure what you mean as I said I am a novice when it comes to
databases.

Are you perhaps saying that I should convert the database to an SQL database
instead of just running it in Access? I am not entering data directly in to
the table if you mean opening the database in access and then opening the
table and typing in the information, I have an infopath form which is linked
to the database and data is submitted from the form.

I kinda understand what you are saying about public flags and will try your
suggestion to see how I get on.

Thanks

 

[Arvin Meyer [MVP]]

I do not think that you can program InfoPath to the same degree as you can
an Access form. If you can send an email from InfoPath, though, that would
be the way to go. Asking in an InfoPath newsgroup should provide an answer,
and if possible, a method for doing so.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com