Actively Checking for new table records.

Question

Hi all,

I am wondering if there is a way to monitor a table for new entries and at the time that a new entry hits the table to requery a form. I am creating an email system for the employees at my company who use generic accounts and do not have Outlook. My thought is that when a message gets added to the table via the compose email screen it will automatically get to the users "Access Inbox" when sent. I am not sure if i want a requery to happen every so many minutes. I have searched around but cant seem to find anything on the topic. Any ideas? Thanks ahead for any help you can offer.

Thanks,

Sam

Answer 1

Hi Sam,

Have you looked into maybe using a data macro (if you're using Access 2010 or later)?

Answer 2

Hi DB guy,

I did not look into doing that. Does the option exist in Access 2007? I had thought of somehow using a recordset to monitor the table but i dont know how to go about such a thing or if it is even possible.

Thanks,

Sam

Answer 3

Hi Sam. Unfortunately, data macros are only available starting with version 2010. Maybe you could change the Refresh Interval setting in Client Setting Options to a faster rate. Just a thought...

Answer 4

>>>I did not look into doing that. Does the option exist in Access 2007? I had thought of somehow using a recordset to monitor the table but i dont know how to go about such a thing or if it is even possible.

According to your description, this is a workaround that you could use Current and BeforeUpdate events of the form to track data changes.

For more information, click here to refer about Form Events (Access)

Answer 5

If the users' "Access Inbox" is a form, you'll probably want to take some action using the form's OnTimer event. As the DB guy states, the form will refresh periodically on its own as per the refresh rate interval but if you want to make it obvious that new records have come through within a specified interval then you'll probably want to use the form's OnTimer event to display a 'new records loaded' message or some such, possibly by comparing the record count to a previously stored value.

-Bruce

Answer 6

Hi All,

Thanks for the suggestions. The monitoring a table would have been a fancy function that i was unsure about. What i ended up doing was having a requery every time the "Inbox" navigation pane (subForm) changed records, or basically the user wanted to view a different email, it would requery. To make this look somewhat cleaner I just turned the subform's paint property off while it did the requery and reselected the email they just clicked on. I didn't want to requery on a timer because i thought that could be overkill and bog down the computers. My hope was to have something that refreshed semi-often though. So what I learned...table monitoring doesnt really work but there is always another way! :) Thanks again all.

Thanks,

Sam

Answer 7

Hi Sam. Congratulations! Glad to hear you found a solution that works for you. Good luck with your project.