Tuesday, March 15, 2005

Finally got my close button!

What a pain in the neck! Hours spent on the silliest thing.
Issue: wanted to have a button that would automatically set the close date to getDate() when tech claims ticket is resolved.

Why not just allow user to do it? Because I don't want them cheating and saying their turnaround time was better than it is. I also wanted the tech not to be able to modify the field.

  1. Views in SQL? No. Would never refresh after the initial form was opened in Access
  2. Stored procedure? No...well probably, but I don't know enough about SQL to do it right. I got it to work in that Access would pull up the stored procedure's results but not plug the data where I wanted it. The only way I could think of would be creating a parameter and passing the row number to modify only one cell, and then it went pear shaped.
  3. Fix: created a macro, using the Access expression Now() and told it to fill in the close_date field and now I get a matching format to the dateTime values (I already set my SQL table to automatically use getDate() whenever a new record is created for the open_date) in the open_date.

Also, I added some new reports that look better than the user just looking at the raw query results.

In addition I got a reasonably advance query to work showing number of tickets assigned to the tech, and his/her average turn around. Somewhat tricky because I didn't want the numbers skewed by unassigned tickets, or tickets that weren't closed yet.


Post a Comment

<< Home