We help IT Professionals succeed at work.

HELP!  VBA Novice and address verification

callstate
callstate asked
on
In an effort to add address verification to my AC10 contact database, I found this service via Google....

http://wiki.cdyne.com/index.php/Postal_Address_Verification

"CDYNE Postal Address Verification integrates with your website, database, or system using SOAP (Simple Object Access Protocol) or REST (Representational State Transfer) protocols. You can interact with the server in multiple ways, enabling ease of data communication. CDYNE Postal Address Verification is CASS Certified® by the United States Post Office and offers the following key features:
Comprehensive DPV return code system that confirms the existence of a specific address (down to the apartment or suite number) and the fact that it can be delivered to
 Avoid residential delivery surcharges by verifying delivery type status prior to shipping with Residential Delivery Indicator (RDI)
Qualify for additional USPS discounts with Intelligent Mail Barcode (IMB)
Checks for Puerto Rican Urbanization to distinguish similar addresses in the same zip code
Optional data returns include: Proper case formatting
Census data – CMSA, FIPS, Census block and Census track number
Geo location data – latitude/longitude, area code, time zone, and daylight savings
Legislative data - Congressional district number and State Legislative Upper/Lower

All of CDYNE's Web Services are built on XML standards and are delivered via a secure Internet connection. This real-time Web Service dynamically delivers value-added information at the point of customer interaction or to the client database for true enterprise intelligence, positively affecting the quality of resultant analytics, customer relationships and the bottom line."



They tell me that their API is compatible with VB, and therefore VBA?  but I can't seem to understand how to use the SOAP or REST protocols.

Can anyone tell me if I can use this via a VBA funtion to pass my data through their service?
Comment
Watch Question

President / Owner
Fellow 2019
Most Valuable Expert 2017
Commented:

 I've never done this, but have always been curious, so I poked around for a few minutes and found this:

http://metrix.fcny.org/wiki/display/tips/How+to+Create+a+Microsoft+Access+Client+for+a+SOAP-based+Web+Service+API

 which outlines the steps.  There may be other ways of doing it.

  VBA is about 99% equivlent to VB 6.0 (there are some minor differences), so if you have a VB code sample, it will for the most part drop straight into Access.

Jim.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

 I looked at their code sample and it will drop straight in.  Nothing unusal there.

Jim.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
<They tell me that their API is compatible with VB, and therefore VBA?>
Yes and No

VBA is a subset of VB, it is not 100% compatible.


<Can anyone tell me if I can use this via a VBA function to pass my data through their service?>
Without knowing anything about your data, ...or your experience with VBA, SOAP, or REST,  ...this is near impossible to determine.

But in just glancing at the article and links, this will be a LOT more complex than just calling a function in VBA, or installing an add-in...
:-O

Is this really needed...?
Unless your address data is totally unreadable, the Post office will get the parcel to the correct address more than 95% of the time, for the other 5%, they should just mail the parcel back to you...

Besides, are you quite sure that this even works the way you need it to?
Contacting them (CDYNE) directly may be your best option here...

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I looked at their code sample and it will drop straight in.  Nothing unusal there.>


Jim, perhaps I did not look into the code as deeply as you did...
:-(

Jeff


Author

Commented:
I guess I didn't understand the answer ....

This is the code I have tried, but I get:
  Run-time error '-2146697211 (800c0005):
  The system cannot locate the resource specified.

 
Private Sub cmdVerify_Click()   Dim xmlHttp          As Object   Dim strURL           As String   Dim xmlRequest       As String      Set xmlHttp = CreateObject("Microsoft.XMLHTTP")      strURL = "http://pav3.cydne.com/PavService.svc/VerifyAddress?"   xmlRequest = "<VerifyAddress><FirmOrRecipient>CYDNE</FirmOrRecipient><PrimaryAddressLine>2125 Smith Ave</PrimaryAddressLine><SecondaryAddressLine></SecondaryAddressLine><Urbanization></Urbanization><ZipCode>23320</ZipCode><CityName>Chesapeake</CityName><State>VA</State><LicenseKey>51496248-7E64-420A-844A-5B0F5EB706A9</LicenseKey></VerifyAddress>"      xmlHttp.Open "GET", strURL, False   xmlHttp.send (xmlRequest)    MsgBox xmlHttp.responseTextEnd Sub

Open in new window

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
Where did you get the error?

 and I can't read that code you pasted in.  Copy/paste out of the VBA module.

Jim.

Author

Commented:
I have figured this out ....  will post code soon for others who may be interested.

Will award points when I close the question.  Thanks Jim & Jeff for your help!

Author

Commented:
Jim/Jeff, Thanks for the help.  Your info pointed me in the right direction.

Here is the code I used to solve this issue ....  



Option Compare Database
Public Type DPVaddress
    Name As String
    Address1 As String
    Address2 As String
    City As String
    State As String
    Zip As String
    FirmOrRecipient As String
    PrimaryDeliveryLine As String
    SecondaryDeliveryLine As String
    CityName As String
    StateAbbreviation As String
    ZipCode As String
    ReturnCode As String
    ReturnText As String
End Type

Public Function getXMLkey(xmlstr As String, key As String) As String
    'create strings to match the starting and ending tags in the XML document.  <Name>VALUE</Name>
    starttag = "<" & key & ">"
    endtag = "</" & key & ">"
    
    'Find the position of the starttag and endtag in the XML string
    X = InStr(1, xmlstr, starttag) + Len(starttag)
    y = InStr(1, xmlstr, endtag)
    If y = 0 Then y = X
    'set the value of the function to the string in between the tags
    getXMLkey = Mid(xmlstr, X, y - X)
End Function

Public Function DPVconfirm(chkAddress As DPVaddress, Optional propercase As Boolean) As DPVaddress
Dim objXML
Set objXML = CreateObject("Microsoft.XMLHTTP")
Dim xmlrequest, strProper As String

'CDNYE API requires lowercase, format boolean variable as lowercase string
If propercase = False Then
    strProper = "false"
    chkAddress.Name = StrConv(chkAddress.Name, vbUpperCase)
Else
    strProper = "true"
    chkAddress.Name = StrConv(chkAddress.Name, vbProperCase)

End If

'build XML request string from UDT variable passed
xmlrequest = "<PavRequest xmlns=" & Chr$(34) & "pav3.cdyne.com" & Chr$(34) & ">" & _
                   "<CityName>" & chkAddress.City & "</CityName>" & _
                   "<FirmOrRecipient></FirmOrRecipient>" & _
                   "<LicenseKey>5d14340b-221e-4871-be09-9746b1090c55</LicenseKey>" & _
                   "<PrimaryAddressLine>" & chkAddress.Address1 & "</PrimaryAddressLine>" & _
                   "<ReturnCaseSensitive>" & strProper & "</ReturnCaseSensitive>" & _
                   "<ReturnCensusInfo>false</ReturnCensusInfo>" & _
                   "<ReturnCityAbbreviation>false</ReturnCityAbbreviation>" & _
                   "<ReturnGeoLocation>false</ReturnGeoLocation>" & _
                   "<ReturnLegislativeInfo>false</ReturnLegislativeInfo>" & _
                   "<ReturnMailingIndustryInfo>false</ReturnMailingIndustryInfo>" & _
                   "<ReturnResidentialIndicator>false</ReturnResidentialIndicator>" & _
                   "<ReturnStreetAbbreviated>false</ReturnStreetAbbreviated>" & _
                   "<SecondaryAddressLine>" & chkAddress.Address2 & "</SecondaryAddressLine>" & _
                   "<State>" & chkAddress.State & "</State>" & _
                   "<Urbanization></Urbanization>" & _
                   "<ZipCode>" & chkAddress.Zip & "</ZipCode>" & _
            "</PavRequest>"

'make webservice request
Call objXML.Open("POST", "http://pav3.cdyne.com/PavService.svc/VerifyAddressAdvanced", False)
Call objXML.setRequestHeader("Content-Type", "text/xml")
Call objXML.send(xmlrequest)

'push results from responsetext into UDT variable
DPVconfirm.Name = chkAddress.Name
DPVconfirm.Address1 = chkAddress.Address1
DPVconfirm.Address2 = chkAddress.Address2
DPVconfirm.City = chkAddress.City
DPVconfirm.State = chkAddress.State
DPVconfirm.Zip = chkAddress.Zip
DPVconfirm.FirmOrRecipient = getXMLkey(objXML.responseText, "FirmOrRecipient")
DPVconfirm.PrimaryDeliveryLine = getXMLkey(objXML.responseText, "PrimaryDeliveryLine")
DPVconfirm.SecondaryDeliveryLine = getXMLkey(objXML.responseText, "SecondaryDeliveryLine")
DPVconfirm.CityName = getXMLkey(objXML.responseText, "CityName")
DPVconfirm.StateAbbreviation = getXMLkey(objXML.responseText, "StateAbbreviation")
DPVconfirm.ZipCode = getXMLkey(objXML.responseText, "ZipCode")
DPVconfirm.ReturnCode = getXMLkey(objXML.responseText, "ReturnCode")

Select Case DPVconfirm.ReturnCode
    Case 1
        DPVconfirm.ReturnText = "Invalid input."
    Case 2
        DPVconfirm.ReturnText = "Invalid license key."
    Case 10
        DPVconfirm.ReturnText = "Input address not found."
    Case 100
        DPVconfirm.ReturnText = "Input address is DPV confirmed for all components."
    Case 101
        DPVconfirm.ReturnText = "Input address found, but not DPV confirmed."
    Case 102
        DPVconfirm.ReturnText = "Input address primary number is DPV confirmed."
    Case 103
        DPVconfirm.ReturnText = "Input address primary number is DPV confirmed, secondary number is missing."
    Case 200
        DPVconfirm.ReturnText = "Canadian address on input.  Verified on City level only."
End Select

End Function

Function DPVconfirmContacts()
On Error GoTo DisplayError
Dim db As Database
Dim rst As Recordset
Dim myAddress As DPVaddress

'Open database and tables
Set db = CurrentDb
Set rst = db.OpenRecordset("Contacts", dbOpenDynaset)

'Verfiy recordset isn't empty
If Not (rst.BOF And rst.EOF) Then rst.MoveFirst

With rst
    Do While Not rst.EOF
        
    'turn off error handling for update routine
    On Error Resume Next
    
    'load UDT variable with info from record
    myAddress.Address1 = rst("Address")
    myAddress.Address2 = rst("Address2")
    myAddress.City = rst("City")
    myAddress.State = rst("State")
    myAddress.Zip = rst("Zip")
    
    'call webservice and record results in UDT variable
    myAddress = DPVconfirm(myAddress, True)
    
    'update records in table
    rst.Edit
        rst("Address") = myAddress.PrimaryDeliveryLine
        rst("Address2") = myAddress.SecondaryDeliveryLine
        rst("City") = myAddress.CityName
        rst("State") = myAddress.StateAbbreviation
        rst("Zip") = myAddress.ZipCode
        rst("DPVcode") = myAddress.ReturnCode
    rst.Update
    'Display record
    StatusBar "Validating " & rst("First Name") & " " & rst("Last Name") & " ...."
    DoEvents
    
    'turn error handling back on
    On Error GoTo DisplayError

NextRecord:
    rst.MoveNext
    Loop
End With

'Close open tables & database
rst.Close
db.Close

Done:
    StatusBar
    Exit Function

DisplayError:
    MsgBox Error
    GoTo Done
End Function

Function DPVconfirmContact(strID As String) As DPVaddress
'On Error GoTo DisplayError
Dim db As Database
Dim rst As Recordset

'Open database and tables
Set db = CurrentDb
Set rst = db.OpenRecordset("Contacts", dbOpenDynaset)

'Verfiy recordset isn't empty
If Not (rst.BOF And rst.EOF) Then rst.MoveFirst

'Lookup ImportHist("PHONE") in CUSTOMERS table to see if it exists
Dim strSearch As String
strSearch = "[ID] = " & strID '& "'"
rst.FindFirst strSearch
    If Not rst.NoMatch Then
    'Found existing record in CUSTOMERS table and prompt to update it
        
    'turn off error handling for update routine
    On Error Resume Next
    
    'load UDT variable with info from record
    DPVconfirmContact.Address1 = rst("Address")
    DPVconfirmContact.Address2 = rst("Address2")
    DPVconfirmContact.City = rst("City")
    DPVconfirmContact.State = rst("State")
    DPVconfirmContact.Zip = rst("Zip")
    
    'call webservice and record results in UDT variable
    DPVconfirmContact = DPVconfirm(DPVconfirmContact, True)
    
    'update records in table
    rst.Edit
        rst("Address") = DPVconfirmContact.PrimaryDeliveryLine
        rst("Address2") = DPVconfirmContact.SecondaryDeliveryLine
        rst("City") = DPVconfirmContact.CityName
        rst("State") = DPVconfirmContact.StateAbbreviation
        rst("Zip") = DPVconfirmContact.ZipCode
        rst("DPVcode") = DPVconfirmContact.ReturnCode
    rst.Update
    'Display record
    StatusBar "Validating " & rst("First Name") & " " & rst("Last Name") & " ...."
    DoEvents
    End If
    'turn error handling back on
    On Error GoTo DisplayError

'Close open tables & database
rst.Close
db.Close

Done:
    StatusBar
    Exit Function

DisplayError:
    MsgBox Error
    GoTo Done
End Function

Public Function StatusBar(Optional Msg As Variant)
Dim Temp As Variant

' if the Msg variable is omitted or is empty, return the control of the status bar to Access
If Not IsMissing(Msg) Then
    If Msg <> "" Then
            Temp = SysCmd(acSysCmdSetStatus, Msg)
        Else
            Temp = SysCmd(acSysCmdClearStatus)
    End If
    Else
        Temp = SysCmd(acSysCmdClearStatus)
    End If
End Function

Open in new window