Ways to display a "Live" job list

[stildawn]

Hi All



I am new to Access, this post sort of relates to my other one here: Mod Edit: deleted duplicated thread.

Basically I have a massive list of "jobs" that are assigned to different teams at different stages of the "job". I have imported this from Excel into Access and everything looks to be set up and working well. I just have two questions at the moment:

1. Access refresh interval is 60 seconds, yet in my query for a specific team (so that it shows only the "jobs" assigned to that team), if I change the team in the main table, it never updates on the query unless I run the query again? Is there any way to make "Live" queries?

2. I plan to make a bunch of Queries for each team, displaying their "jobs" assigned to them from the main table. These queries will be displayed on big screens that are around the building. Is there a better way to display a "Live" list of jobs, better than just maximising access on the queries table?

Thanks

[June7]

I tested with a form and query both open and referencing the same table source. I did edit in the record on form and saved the record. The edit immediately showed on the open query. So I do not understand your issue.

You will have copies of this db on multiple computers to display different 'jobs' lists?

[stildawn]

Thanks for the interest June7

No I plan to have the DB on the network drive, with (at the moment) two computers running full access displaying two "Live" queries on big screens.

What I mean is that I have a query which criteria is only display Team 4 for examples jobs. This works and in the Query Table only records with Team 4 are displayed. However if I change a record on the main table to say Team 6. Then yes on the Query Table is shows up as Team 6 now, but its still shows up, it should disappear from that table since that table is only ment to show Team 4 records?

Does that make sense?

So if a user assigns a current record to a different team, it needs to be removed from the query (which is displaying a certain teams records on a big screen).

This doesn't need to happen instantly, just like regularly.

Thanks

[June7]

You should review this article http://office.microsoft.com/en-us/ac...010341940.aspx about refresh/requery. Take special note of the second paragraph.

[stildawn]

Thanks for the info. I read that and tried following it.

However I can't manage to get it to work.

It looks like I need to run the Requery method on a time of sorts but I can't even get it to run once.

I'm trying to requery one of my queries called "EXP AFR" but nothing I have tried works.

[June7]

What were you trying from that article?

The only way to requery a query is to open it or if already open, make sure it has focus (click on it) then click the Refresh button on ribbon. Can't have code behind a query.

A form can be programmed to requery automatically at regular intervals without user interaction with its OnTimer event. I've never used it.

[stildawn]

Oh ok that's good to know now have to come up with a better way to display the data then.

Any ideas on how you would display a live list of filters records? Can this be done in a form?

[Dal Jeanis]

1) Display your query on a form.
2) Give the form a timer.
3) When the timer clicks over, requery.

It ain't elegant, but it's simple.

If the query is too big - too machine-intensive - for that, there's a more complicated thing you could do. You can have a separate table where you set the last updated timestamp for the table underlying the big query. Then, your form could query that field, and only requery the big query when the timestamp says something has changed. That solution requires infrastructure, though, pretty much everywhere someone could modify the data.

[June7]

Displaying records on a form is basic Access functionality. One query, one form, use code to filter records. There are several approaches to accomplish. Review this for one http://www.datapigtechnologies.com/f...tomfilter.html.

[Dal Jeanis]

FYI - when I said "Display your query on a form", I implicitly meant a form/subform arrangement where the continuous subform would show all the responses from the query, rather than a form that would show only one record.

[stildawn]

Hi all thanks for the tips I didn't know you can make a form look pretty much exactly like a table.

I made a query, then using the form wizard I made a datasheet form off that, then added a Me.Requery to the code on a 5 minutes timer, seems to work well so far.