View Full Version : Error in VBA automation of MapPoint when PO BOX is found

01-08-2003, 09:59 AM
Hello everyone,
Okay, I have a quick question for all of you. I'm using VBA in access to create maps... and my maps will appear fine with pushpins in the location of each record... however, if the automation process comes upon a single PO BOX in the address field of a record- Access stops the mapping process and delivers an error message... I was wondering if any of you know a solution to this problem.

I had considered writing an algorithm that checks the first 6 chars of the address string and sees if it's "PO BOX" or "PO BIN", but unfortunately I'm a much better java programmer than a VBA programer, and I'm not very good at manipulating the string object class in VBA. I could write the code in java in less than a minute so it can't be too tough to write in VBA. Has anyone ever done something like this and could throw the snippit of code my way? Otherwise I'm going to start hittin the books and read up on VBA strings.

I'm hoping that there is an easier way to do this... Considering only 15 of the records in about 5000 have po box in the address field, this is a pain for such little benefit, but it has to be done. Maybe I can write some sort of a query, I don't know... I just need a good shove in the right direction. Thanks much.


01-09-2003, 09:37 AM
Fixxed it ... well, this is a simpler fix for my direct problem since I'm only fixing 15 records and any records post this date will not allow po boxes to be entered in the address field... so this works for me. But I might suggest a more extensive solution for someone with a more elaborate problem. Perhaps scan the entire string for the word Box and if it exists create an input box popup for the user to view the address and ask him whether to include it or not, if his answer is yes, then add the record, else move next ... Instruct your users to not allow po boxes, and you can even include this in the popup ... and then if the user accidentally clicks yes on a po box, you can have some error control in your module. I hope this helps someone in the future.

If Left(MapRST!Address, 4) <> "PO B" Then 'Catches Bin or Box
Mapping comands here...
End If


01-09-2003, 09:40 AM
Might i add that you'll want to convert the entire address string to capital letters to avoid case sensitive problems.... I did in my solution, just forgot to type that code in. It's easy to do. And the reason why we search for BOX is to eliminate the error of P.O. versus PO.

Just some ideas, maybe it'll help someone somday..