How to Get Form to Update on Load, and Update Table at the Same Time?

[RJTurneR]

Hello there!

I have recently created a database that allows us to monitor the disk space on our servers. The database consits of a table, a query, and two forms. The table holds all the servers names, and information (Free space, used space, total space), and the disk space query takes the latest information from this and displays it.

To do this, I run a script when the database is not open(Not made by me but a colleague), and once the database is opened, the table will show when it was last updated (e.g. Today, 25/4/12).

I have then created two forms which display bars for each sever. These bars represent how much space is used and is left. One form displays all the servers, one form displays our more important servers. The forms were pretty much created using VBA.

So that's a quick description of how it works. What I am actually here for is to ask how could I get the form to update on load and then update the table at the same time (This is what my colleague wants anyway, any other suggestions are welcome). As of right now, the only way to update the database is to run a seperate VB Script, which although works, it is much more convient to have the database to this for us.

If any other information or better description is needed, feel free to ask. I do have a great deal of knowledge when it comes to this, but everything in I.T is a learning process.

Thanks!

 

[Pat Hartman]

You can run VBScript within Access provided you set a reference to the scripting library. So you would just copy the code into a new module, set the reference and then run the code when the database opens or the form loads or whenever you want.

 

[RJTurneR]

This worked, thanks! I just copied the VBScript code into one of the forms, then closed the database, opened it back up, and opened the form and this updates all the forms and the table.

Thank you!

 

[RJTurneR]

Sorry for the double post, but just wanted to ask a couple more questions.

One. Is it possible for the database (Not the table, but Access itself) to update on load and on a timer? I've made a lot of progress since my last post, as I've now made it so the either one of the forms will update everything when loaded, and I have also added a Refresh button with same code, so it is possible to update the forms on demand.

So say when I open up the database file (e.g. database.mdb), it will update everything, rather then having to open up one of the forms for it to update. And is it possible to have it so the database will update say.. every 20 or 30 minutes.

Two. When I use the Refresh button to update the table, query, etc.. It does not update the actual page. If one of the bars that represents a server says 10GB remaining of 100GB, that does not change when the Refresh button is used. All it's doing is updating the data. So how do I get the form to refresh as if I was refreshing a web page?

Thanks again!

 

[Pat Hartman]

I would use a hidden form for this purpose. Let this form be the one opened first when the database opens. The form should run the requery code when it loads and also from a timer event. In the load event, after the requery is run, the code should open your normal startup form and then hide itself (Me.Visible = False).

Just because this update code runs in the background doesn't mean that it will requery the recordsets of open forms. When a form opens, Access loads the recordset selected by the RecordSource query into memory - where it stays as long as the form is open. The form has a refresh interval that specifies how frequently that recordset in memory will be "refreshed". Refresh will pull in any updates or deletes but it will not "see" any additions. You need to actually requery the form to force the RecordSource query to run again to fetch all new rows into memory.

I'm not sure what your application is used for but it is generally not good practice to open forms to large recordsets at all. Let alone leave them open for long periods of time. If you are trying to monitor activity, you would have a timer on the open form that requeries (refresh doesn't pull in new records) each metric at some interval.