Red Light/Green Light Status Icon?

[kestefon]

I'd like to add a status icon to a form that tells the user whether data has been loaded. (i.e. if a table exists) I'm just envisioning something simple, like a red light/green light box. What would be the best way to go about this?

[rpeare]

you can have a text box for which you change the 'background color' if certain conditions are met, I don't believe you can do conditional formatting itself but you can certainly see if a table exists and color the text box accordingly.

[kestefon]

Thanks. What type of event would be ideal for this? The user would interact with the form to import files, so I suppose I would want the lights to update if any tables were added/deleted/changed.

[rpeare]

Where you put the code depends on when you want the user to be able to see the result. You could put it in the ON OPEN event of a form (your main form maybe?), sort of like a warning as soon as they open the database that something is not up to date. Just bear in mind that if you have multiple users all changing data at the same time and you are trying to track any table that was 'added/deleted/changed' in particular you are going to have to build a structure around keeping track of when tables were created/deleted, there is no default mechanism for this. You would literally have to build a table change log to do this kind of tracking and if you are intending to track individual changes to an individual table you are looking at basically a SQL transaction log which can blow up very quickly depending on the amount of activity your database sees.

[kestefon]

There would just be one user. Also, the problem with On Open is that users import while the form is already open. So, they would open the form and the status icons would all be red. Then they would press the Import button, run the macro, and I would like the status to change to green once the imported table has loaded.

My VBA logic for toggling between red and green works, but I just need to figure out an event for whenever in the database is added/deleted.

[rpeare]

You'll have to be more clear.

If you have a button (other other control) where the user triggers an import (you say a button) perform the 'check' in that same on click event. What you haven't said is, what triggers it to go back to red? how do you know when a 'new' file is available? how often is this supposed to run? etc. What is your PROCESS.

[kestefon]

Well, the logic would basically be "If TABLE exists, then green. If TABLE doesn't exist, then red." But if I write that into the form's code, I need some event to trigger the procedure to run, right? So, I'm just trying to figure out an event that would trigger the form to check whether a table exists or not.

[rpeare]

you're being very vague about your PROCESS.

If you have a UI, put the code in the ON OPEN event of the form you are talking about.

If the table exists your color will be green, if it doesn't it will be red.

If the color is red and someone clicks the button to perform the import do the import but as the last step of that button perform the SAME check you're doing in your ON OPEN event.

Basically just write a function to check to see if the table you're interested in exists, if your 'main' form is open, perform the appropriate coloring, otherwise don't do anything.

[kestefon]

Ah, that works. Thanks!

And just to confirm, there's not an event that corresponds to addition/deletion of your database's tables?

[rpeare]

No, there isn't, if you want to monitor additions/deletions from a table you have to create your own log of events.