Results 1 to 10 of 10
  1. #1
    kestefon is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    108

    Red Light/Green Light Status Icon?


    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?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    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.

  3. #3
    kestefon is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    108
    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.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    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.

  5. #5
    kestefon is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    108
    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.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    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.

  7. #7
    kestefon is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    108
    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.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    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.

  9. #9
    kestefon is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    108
    Ah, that works. Thanks!

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

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    No, there isn't, if you want to monitor additions/deletions from a table you have to create your own log of events.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-20-2013, 10:26 PM
  2. Query Code to Hi-light Date Triggers
    By sbrem00 in forum Queries
    Replies: 1
    Last Post: 11-17-2011, 08:59 AM
  3. Flash green or red
    By elmister in forum Access
    Replies: 8
    Last Post: 08-30-2011, 03:37 PM
  4. Help, I get a green thing when i put a label.
    By Starstruck00 in forum Forms
    Replies: 2
    Last Post: 06-02-2011, 10:47 PM
  5. Replies: 9
    Last Post: 11-23-2009, 09:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums