Me + Access = badness

[Dymetrie]

So I'm working on a little project at work at the moment. A nice happy little database to track Covert Camera installations.

I open Access and create a table.. Lovely, I write a macro to automatically calculate the Cancellation due date (installations can only go in for 3 months) and review dates for the installation. After some hitting things with hammers I get all that to work.

Now comes the fun part.

I want this database to automatically send out e-mails to remind Housing Officers of review dates. No problem, I get a nice little macro running which, when run, sends out an e-mail (the content at the moment is a load of tosh, I just want to get it working first of all).

Then I wrote a new Macro.

The Macro of EVIL!

If the review date of the installation is the same as today's date run e-mail macro... Simple...

It managed to spam out 97 e-mails before I managed to pull the plug :D Methinks the exchange server at work is possibly a mite peeved at me now :p

Now comes a fun afternoon of trying to figure out what I ballsed up (I refuse to run that macro again without some kind of safeguard, like yanking the network cable out first) and to get it to run properly...

If anyone has any advice then it'd be more than welcome as my Access guru appears to be asleep today. (can't access forums when I'm meant to be working but e-mail works)

*runs off to spam some more e-mails*

[Garp]

Debug mode dude... bring up the macro and step through it (F8 I think?). Put a few watches on for various variables in the code, and then step through watching what values they are. You should soon spot why its looping.

[kaiowas]

And most importantly comment out the line in the email macro that actually sends the mails whilst you're still playing!!

[William]

I wrote a Macro of evil today as well, it causes 10 seconds of excel flickering and spasming until it comes up with the answer. ;D

[Garp]

I wrote a Macro of evil today as well, it causes 10 seconds of excel flickering and spasming until it comes up with the answer. ;D

Turn off screen updates. Added bonus, it runs quicker.

[Dymetrie]

Turn off screen updates. Added bonus, it runs quicker.

I'll be having a chat with you this weekend youngun :p

[kaiowas]

Turn off screen updates. Added bonus, it runs quicker.

Of course, if you want to code it properly you could just get rid of all the unnecessary "worksheet.activate"s and "range.select"s as well.

Is this a bad time to admit doing Excel VBA for a living? :p

[William]

Turn off screen updates. Added bonus, it runs quicker.

No I prefer it this way, it makes people at work think I have done somthing really clever and complicated then they leave me alone so I can play minesweeper all day.

[Garp]

Is this a bad time to admit doing Excel VBA for a living? :p

*bows to the expert*

I write crappy Excel VBA macros only when forced to :)

I'm not a programmer despite what people in previous jobs may think. I don't enjoy it, I don't have the right kind of mindset for it. The fact that I can get the macro written to do what they wan't does mean either a) I enjoy doing it, b) I want to do it, or c) that it'll be anywhere close to efficient.