I'm not sure I'd go the route of setting a timer interval to requery a subform every X seconds. This seems like a work-around for the real problem and setting a timer to do this can sometimes have undesirable affects (ie. the user is editing the data on the subform and it suddenly requeries causing data issues.)
If your users constantly have a "Main Form" open that is bound to a recordsource, you can eventually have 'locked mdb' issues (where the *.ldb file becomes corrupt and when another user opens the same mdb, they get a 'locked by another user' error.)
Instead I might setup the mdb so that the specific form which the user 'always' keeps open is not bound to a specific table. Although this may not be ideal if the user(s) 'must' always keep the data form open and see updates immediately, it prevents other problems.
Personally I'd recommend a backend server such as SQL Server for the data tables (and then just link the SQL Server tables into the mdb). SQL Server does a much better job of handling real-time data updates as you've described.
Another option is to create an unbound field on the form (such as a checkbox or true/false field) to indicate the data has been updated. You could use your timer interval and a bit of vba code to update this unbound field if that specific data is updated with a button for the user to then requery the subform (which to me would be a better option versus always requerying the subform every x seconds.) If you don't already have one, you may also want to consider a field in the actual data table indicating when the data is updated. How you utilize this field on the form could be done many different ways (ie. if a record is updated, then requery the subform.)
I usually utilize unbound fields on a form with timer events versus actually requering data on a form with a timer. Users tend to get unhappy if they are editing data and it suddenly requeries the form.
Also consider using a listbox versus an actual subform if the data is not being updated on the subform. Listboxes tend to be less toll-taking versus a subform if you absolutely need to requery every x seconds, especially if you make the underlying rowsource query of the listbox a 'Snapshot' versus a 'Dynaset'.
Last edited by pkstormy; 03-19-10 at 20:56.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)