AdSense Top


MS Access 2007 and VBA

Today was an interesting day at work.  My boss is on a camping trip for today and tomorrow, so I'm the only IT person here, which means I get to answer everyone's stupid questions!  It wasn't that bad, I only had 1 stupid question, and spent the rest of my day helping out a coworker with the Access Database he's building for Project Management.  His first request was for me to come up with a method for Access to automatically schedule a task in Outlook.  Given that I haven't done much VBA  I reluctantly agreed to help, and spent the first hour of my day figuring out how to turn on the Outlook reference in the Access VBA environment.  After that it was all trial and error as I tried and failed and tried and failed to successfully scrape the information I needed out of an Access form.  I finally got it, and about a half hour after I sent the results over to him he comes back wondering why he can't use strings to set the body and subject of his tasks.

I walked him through step by step and worked through it on my copy of his database with him guiding em through what he did.  As soon as we got to the part where he was calling his strings in the NewTask.Subject line, his problem became crystal clear.  He preceded every string call with str, which he should if his strings start with str, which they should if he followed common programming/scripting practices, but he didn't, and as a result they didn't and his code broke because he was calling strings that weren't there.

I thought that would be the end of my troubles with him, but it turns out I was wrong of course.  He returned a third time, asking about why he couldn't assign a task to himself.  This one, like the first one, was actually a problem.  Since I wasn't listed as a user or contact in his database, I never was able to thoroughly test whether or not I could assign a task or not (I used NewTask.Display, which pops up the Outlook dialogue and allows you to adjust any of the imported options before assigning a task.  It also let me cancel before I assigned because I have enough tasks as it is).  Sure enough, you cannot 'assign' yourself a task, outlook has a different dialogue for that.  So now he had a 2 part task for me.  1) find a way to get the logged-in users username and compare to their name in the database, and 2) use the appropriate task dialogue depending on if you're assigning to yourself or not.

Part 1 was fairly easy, I found a chunk of code <a href="">here that works beautifully for this</a>, and just made a module for it.  Part 1.5 involved some string functions, because the username scheme we use being first-initial-last-name means that Full names and usernames are totally different.  Conversion to lowercase, grabbing the first character then searching for the position of a [space] and grabbing the characters after that fixed it so they could be compared to one another.  Now to figure out the difference between Assigned task and a regular self-assigned task.  After some playing around it was apparent that the NewTask.Assign call was at fault for converting the tasks to an assigned task, so moving that along with the contact lookup into the If/Then/Else statement solved that problem.

All in all, I'd say today was a rather productive day for me.

No comments:

Post a Comment