Access Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

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.
 

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
 

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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top