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.

Solutions:
  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.

0 Comments:

Post a Comment

<< Home