Can I simulate a "new record" table-level event?

[willknapp]

I have a list box that displays the contents of a back-end table that is has a new record added roughly 45 times per hour. I'd like the listbox to refresh automatically when a new record is added and, ultimately, notify the user that the new record is there. Are there any non-form events I can use to accomplish this? Right now, my workaround is to refresh the box once a minute using the form's timer. It works fine, but it's not pretty. Ideally, I could create some sort of event based on the new record being saved in the table.

Has anyone ever tried this before? Any other work arounds?

 

[Uncle Gizmo]

I don't know is the simple answer. However if I ran into a situation where this this became necessary then I think I would have a look at With Events and Sinking of Events.

I did a quick search on the Internet and found this book and some sample pages which may provide you with more information.

http://books.google.co.uk/books?id=... Events in Stand alone Class Modules"&f=false

 

[way2bord]

I have a list box that displays the contents of a back-end table that is has a new record added roughly 45 times per hour. I'd like the listbox to refresh automatically when a new record is added and, ultimately, notify the user that the new record is there. Are there any non-form events I can use to accomplish this? Right now, my workaround is to refresh the box once a minute using the form's timer. It works fine, but it's not pretty. Ideally, I could create some sort of event based on the new record being saved in the table.

Has anyone ever tried this before? Any other work arounds?

As far as I know, your current process is the most effective.

If you have control over the process which submits data inbound to the table, you could add an event to every send to create a listbox refresh flag.

Alternatively, you could keep an open connection to your table.

I can't imagine either of these suggestions will actually improve performance though.

If you're dealing with huge data tables - I imagine you are if you have 45 additions an hour - you might be able to improve your performance by manipulating your listbox recordset to be static and simply append new data rather than a full requery every minute.

 

[pbaldy]

I'd probably do what you're doing and work on why it "isn't pretty". That said, another option if you have at least 2010 is data macros. You could have a data macro set a value in another table, have your timer process check that table, and when the value is appropriate, requery the form and reset the value.

 

[willknapp]

I'd probably do what you're doing and work on why it "isn't pretty". That said, another option if you have at least 2010 is data macros. You could have a data macro set a value in another table, have your timer process check that table, and when the value is appropriate, requery the form and reset the value.

What makes it "not pretty" is the quick flash that comes with requerying. Not a huge deal, but I'm sure my end user are going to thing there's something else going on. That said, I really like the idea of using the time to first check to see if there are any data changes before refreshing.

Basically, I have about 40 users that have differnet views of the data, so even the table is updated 45 times per hour, they may only see a small fraction of those changes - the screen might only refresh 10 times in an hour, and that's much easier to deal with.

Thanks for the suggestions!

 

[way2bord]

What makes it "not pretty" is the quick flash that comes with requerying. Not a huge deal, but I'm sure my end user are going to thing there's something else going on. That said, I really like the idea of using the time to first check to see if there are any data changes before refreshing.

Basically, I have about 40 users that have differnet views of the data, so even the table is updated 45 times per hour, they may only see a small fraction of those changes - the screen might only refresh 10 times in an hour, and that's much easier to deal with.

Thanks for the suggestions!

I think the screen flash is going to be much easier to resolve than OP question.

There's several .screenupdate = false / true switches (syntax?) you could use and several other posts I've seen addressing screen flicker.

 

[pbaldy]

I suppose you could hold the record count of the table in a public variable, and test for that changing too.