1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Question

Discussion in 'Business Applications' started by Dragn73, Jan 31, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Dragn73

    Dragn73 Thread Starter

    Joined:
    May 23, 2002
    Messages:
    29
    I have built a basic issues database for our tech team. I have an issue date field which automatically puts in the date and time when the issue is created. I also have a status field that can be Active, Open , or Closed.

    I have a request to add another auto date/time field that will populate only when the status field has been changed to Closed. From this, they would like a report built to find the difference between the 2 time fields to calculate time spent on issues by the team. I cannot figure out how to add a date/time field to trigger off of the Closed value in my status field. Does anyone have a suggestion for me?

    Once I get that, i may be back to ask about the report, but I figure one step at a time.

    Thanks a ton!

    Dave
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Dave, make the Active/Open/Closed field a text field in the table called "Status". Create a simple table and query to hold the three values Active, Open, Closed.
    Now on your form create a Combo Box based on your new query on youe new table.
    When creating the Combo make it "Bound" to the new Status field.
    In the Combo Box's After Update Event Procedure put the following VBA code
    me.ClosedDate = Date()
    where ClosedDate is the name of the Date field to hold the date that the issue was closed.

    You type in the Active/Open/Closed as the row source of the Combo but that does not allow for easy changes.
     
  3. Dragn73

    Dragn73 Thread Starter

    Joined:
    May 23, 2002
    Messages:
    29
    Thanks for the help. I believe I did what you said and however the new closed date field is populating if i make any change to that Status field and not only if i select Closed. The default is blank on that field, but when i select Active or Open, the date is populating. Am I missing something?

    Thanks
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Dave, sorry, that is my fault.
    The code for the Combo box after update procedure should be

    if me.Combo0 = "Closed" then
    me.ClosedDate = Date()
    else
    me.ClosedDate = ""
    end if

    If your Combo is not combo zero then you have to put your number in.
    This code will put the Date in when "Closed" is chosen or set it back to nothing when the other 2 are chosen, just in case it gets re-opened.
     
  5. Dragn73

    Dragn73 Thread Starter

    Joined:
    May 23, 2002
    Messages:
    29
    No problem at all, but your code did work for me. Thanks alot, I appreciate it.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/539908

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice