Results 1 to 5 of 5

Thread: Timer Interval

  1. #1
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76

    Unanswered: Timer Interval

    I am writing an application where sales orders are tracked through our company. Once an order is processed, it needs credit checking, then printing, then sewing etc.

    My users want a real time list to show on the mainform of which orders they need to deal with.

    So once the order is processed, accounts will see this order appear on the main form.

    I am doing this by requerying a sub form on the Mainform every 60 seconds.

    Is this the best way? Something doesn't feel right and I think that re querying a form every 60 seconds is going to cause problems down the road?

    Any advice?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I might be concerned if the requery was taking a lot of resources. I have reservation/dispatch applications that requery themselves about that often (plus every time the users take actions). It hasn't caused any problems.
    Paul

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Querying a form once per minutes isn't too scary. How have you split your front and back ends?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    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 21:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Thank you for all the advice

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •