[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1183
  • Last Modified:

Get VIN decode data to put in Access fields

I am looking for some way to get VIN decode data placed into fields in my form. I want the user to be able to put in the VIN number and have it look up on the internet via software or ActiveX or something, then have the data flow into the fields for year, make, model, color.

Ideas? Thanks!
0
gaynes
Asked:
gaynes
  • 7
  • 6
  • 3
1 Solution
 
gaynesAuthor Commented:
That dumps the whole page in. Is there a more elegant way to pull the data in? Also, I am not sure how to land it to get the Vin entered in the right spot on the web page.
0
 
ragoranCommented:
There seems to be many provider of Web Services to decode VIN.  I am not in the automative industry, so I can't compare them.  But from an application point of view, using a web service that return the description in an XML document is simple.

have a look at this site that I found using Google:
http://www.dataonesoftware.com/page_Overview_3704

Again, there are other similar sites.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gaynesAuthor Commented:
I saw that as well. How would i get the XML data to populate my form from this quickly? That is why I was hoping for an activeX or some easy way to pull this data.
0
 
Rey Obrero (Capricorn1)Commented:
0
 
Rey Obrero (Capricorn1)Commented:
also, you may not be able to find one that is tailored fit for your requirement.
the examples suggested are mere examples to give you an idea how to do the things you want done. In short, you need to do some tweaking....
0
 
gaynesAuthor Commented:
I realize that. I am playing with them. Since the automotive industry is so huge, I am surprised there isn't something already out there I can buy or use to interface. There are lots of decode VIN free sites.
0
 
ragoranCommented:
When you subscribe to those services, then will tell you the structure of the XML document.

This is a portion of an example from the site above:

<vin_number value="3D7MX48C16G266205">
      <vin_errors>
<error_message/>
<resolution_message/>
</vin_errors>
      <common_info>
      <common_basic_data>
<common_year>2006</common_year>
<common_make>Dodge</common_make>
<common_model>Ram Pickup 3500</common_model>
<common_country_of_manufacture>Mexico</common_country_of_manufacture>
</common_basic_data>


It may seems gibberish, but with the XML library from MS (now include in Windows), you can parse this information and query it directly as easily as the value of a recordset (well very close anyway).


It does involved some code, but it is more reliable than scan the text of a web page which layout could change at any time.

It is possible that the companies who provide the web services could have VB or VBA examples.
0
 
gaynesAuthor Commented:
I got the XML to come in, now how do I put it in my form fields? Can you point me to some examples?
0
 
ragoranCommented:
I will based my example on the above XML.  You will have to adapt it to the actual format you received.  For this code to work, you will need a reference to Microsoft XML library (use the latest version you have)

Lets say that wReplyXML is string variable that contains the XML you reveived from the web site and me.txtCarMake is a textbox on your form, then:

dim oDOM as new As MSXML2.DOMDocument30
dim oNode As MSXML2.IXMLDOMNode

odom.loadXML wResult
If oDOM.parseError <> 0 Then
    msgbox  "Could not interpret result: " & oDOM.parseError.reason
else
   Set oNode = oDOM.selectSingleNode("vin_number\common_info\common_basic_data\common_make")
   If oNode Is Nothing Then
      msgbox "Node is not found: " & "vin_number\common_info\common_basic_data\common_make"
   Else
      me.txtCarMake.value  = oNode.nodeTypedValue
   End If

   ' ....
end if



0
 
gaynesAuthor Commented:
This is the code I am starting from. It displays, then I can't get the xml data to work how you said. I did add the Microsoft XML object. It doesn't like your dim odom statement.

Sub TestIt()
Dim xmlDoc As New DOMDocument
Dim currNode As IXMLDOMNode
Dim rootNode As IXMLDOMNode
Dim oNodeList As IXMLDOMNodeList
Dim NewNode As IXMLDOMNode
Dim strFullXML, xmlstring As String
Dim v
v = "2hnyd18265h504875"
Dim strVINqueryURL As String
strVINqueryURL = "http://www.vinquery.com/ws_POQCXTYNO1D/NX2PD7QK.aspx?accessCode=85035b5e-44ce-4d1a-bfd5-1de0cd77d022&vin=" & v & "&reportType=2"
xmlDoc.async = False

With xmlDoc
    If Not xmlDoc.Load(strVINqueryURL) Then
        MsgBox "Line: " & .parseError.Line & vbCrLf & "Char: " & .parseError.linepos & vbCrLf & _
                "Text: ...'" & Mid(.parseError.srcText, 57, 20) & "...'" & vbCrLf & "Reason: " & .parseError.Reason
        Exit Sub
    End If
End With
Set rootNode = xmlDoc.documentElement
Set currNode = rootNode.firstChild
MsgBox currNode.XML
End Sub

I also tried:
Public Function ImportXML(x)
  Application.Import x, acStructureAndData
End Function

putting this on a button with importxml(currNode.XML) to try to have it build a table. It says "object required". I am so frustrated! Thanks.
0
 
ragoranCommented:
Which version of the Microsoft XML library are you using ?

With this URL, I will try to give you a more specific example.



0
 
gaynesAuthor Commented:
6.0, thanks
0
 
gaynesAuthor Commented:
Change in the url string to reportType=0. They just changed my subscription. Thanks!
0
 
ragoranCommented:
I built this example with version 5, but it should work fine with version 6.
A comment before I go to the example.  In your code, you had a line :

Dim strFullXML, xmlstring As String

This is a common misunderstanding of the DIM statement.  In this line, strFullXMl is declare as a variant, not a string.  When putting many variables in the same DIM statement, each needs it AS type portion, if not, they will be declare as variant.  Best practices suggests to put only one variable per Statement.

Here is my example.  I added Error processing and cleanup. I will let you look at it, you can then get back with questions:


Public Sub TestCode()

   'Debug.Print FetchVIN_XML("275")
   Debug.Print FetchVIN_XML("2hnyd18265h504875")
   
End Sub


Public Function FetchVIN_XML(pVin As String) As Boolean

Const BASE_URL = "http://www.vinquery.com/ws_POQCXTYNO1D/NX2PD7QK.aspx?accessCode=85035b5e-44ce-4d1a-bfd5-1de0cd77d022&vin=[%VIN_HERE%]&reportType=2"

Dim oXmlDoc As New DOMDocument
Dim oVehNode As IXMLDOMNode
Dim oRootNode As IXMLDOMNode
Dim strVIN As String
Dim strVINqueryURL As String
Dim wResult As Boolean

On Error GoTo Proc_Error

strVIN = UCase(pVin)
strVINqueryURL = Replace(BASE_URL, "[%VIN_HERE%]", strVIN)

oXmlDoc.async = False

With oXmlDoc
    If Not .Load(strVINqueryURL) Then
        MsgBox "Line: " & .parseError.Line & vbCrLf & "Char: " & .parseError.linepos & vbCrLf & _
                "Text: ...'" & Mid(.parseError.srcText, 57, 20) & "...'" & vbCrLf & "Reason: " & .parseError.reason
        wResult = False
        GoTo Proc_exit
    End If
End With

Set oRootNode = oXmlDoc.documentElement.SelectSingleNode("VIN[@Number=""" & strVIN & """]")
If oRootNode Is Nothing Then
   MsgBox "Wrong format ???"
   wResult = False
   GoTo Proc_exit
End If

If oRootNode.Attributes.getNamedItem("Status").Text <> "SUCCESS" Then
   MsgBox "error in XML query: " & oRootNode.Attributes.getNamedItem("Status").Text
Else
   Set oVehNode = oRootNode.SelectSingleNode("Vehicle")
   
   'load your form controls here (replace debug.print with me.txt... = )
   ' Item names are CASE SENSITIVE
   Debug.Print GetItem(oVehNode, "Make")
   Debug.Print GetItem(oVehNode, "Model")
   Debug.Print GetItem(oVehNode, "Model Year")
   Debug.Print GetItem(oVehNode, "Manufactured in")
   Debug.Print GetItem(oVehNode, "Fuel Economy-city")
   
   wResult = True
End If

Proc_exit:
   On Error Resume Next
   'cleanup
   
   Set oXmlDoc = Nothing
   Set oRootNode = Nothing
   Set oVehNode = Nothing
   
   FetchVIN_XML = wResult
   Exit Function
   

Proc_Error:

   MsgBox "Unhandled error: " & Err.Description
   Resume Proc_exit
   
End Function

Private Function GetItem(pVehNode As IXMLDOMNode, pItemName As String) As String

   Dim oItemNode As IXMLDOMNode
   Dim wValue As String
   Dim wUnit As String
   
   On Error GoTo Proc_err
   
   Set oItemNode = pVehNode.SelectSingleNode("Item[@Key=""" & pItemName & """]")
   If oItemNode Is Nothing Then
      wValue = "?"
   Else
      wValue = oItemNode.Attributes.getNamedItem("Value").Text
      wUnit = oItemNode.Attributes.getNamedItem("Unit").Text
      If Len(wUnit) > 0 Then
         wValue = wValue & " " & wUnit
      End If
   End If
   
Proc_exit:
   Set oItemNode = Nothing
   GetItem = wValue
   Exit Function
   
Proc_err:

   Err.Raise vbError, "GetItem()", "Unhandled error in GetItem: " & Err.Description
   Resume Proc_exit

End Function
0
 
ragoranCommented:
>> Change in the url string to reportType=0. They just changed my subscription. Thanks!

Sorry. I did not see this before I post.

You can change this in the const declaration :

Const BASE_URL = "http://www.vinquery.com/ws_POQCXTYNO1D/NX2PD7QK.aspx?accessCode=85035b5e-44ce-4d1a-bfd5-1de0cd77d022&vin=[%VIN_HERE%]&reportType=0"

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now