User Defined Function with predefined parameters

[MrHans]

Hi all,

I'm trying to create a custom function to return vehicle information, using an webservice. When calling the function, I want to provide 2 parameters: 1 for the vehicle plate number and 1 for the dataset type.
The online service provides different datasets for different kind of vehicle information (general info, fuel info, axes info, etc)

What I would like to know is this. Is it possible to let the function display the possible options? Showing, or even selecting, the dataset that I want to use?

I have the following code right now:

Function GetVehicleDetails(strType As String, strPlate As String) As String

    'Create required variables
    Dim xmldoc As MSXML2.DOMDocument60
    Dim xmlNodeList As MSXML2.IXMLDOMNodeList
    Dim xmlNode As MSXML2.IXMLDOMNode
    Dim myNode As MSXML2.IXMLDOMNode

    'Determine which dataset to be used
    Dim strDataset As String
    Select Case strType
    Case "Voertuig informatie"
        strDataset = "m9d7-ebf2.xml"
    Case "Assen"
        strDataset = "3huj-srit.xml"
    Case "Brandstof"
        strDataset = "8ys7-d773.xml"
    Case "Carrosserie"
        strDataset = "vezc-m2t6.xml"
    Case "Carrosserie_specifiek"
        strDataset = "jhie-znh9.xml"
    Case "Voertuigklasse"
        strDataset = "kmfi-hrps.xml"
    End Select

    'Assemble the query string
    Dim strQuery As String
    strQuery = "https://opendata.rdw.nl/resource/"
    strQuery = strQuery & strDataset
    strQuery = strQuery & "?kenteken=" & Replace(UCase(strPlate), "-", "")

    'define XML and HTTP components
    Dim xmlService As New MSXML2.XMLHTTP60
    xmlService.Open "GET", strQuery, False
    xmlService.Send

    Set xmldoc = New MSXML2.DOMDocument60
    xmldoc.async = False
    xmldoc.LoadXML (xmlService.responseText)

    Set xmlNodeList = xmldoc.getElementsByTagName("*")
    For Each xmlNode In xmlNodeList
        For Each myNode In xmlNode.childNodes
          If myNode.NodeType = NODE_TEXT Then
            Debug.Print xmlNode.nodeName & "=" & xmlNode.Text
          End If
        Next myNode
    Next xmlNode
    Set xmldoc = Nothing

End Function

The strType parameter should display the possible dataset options as defined in the Select Case statement. Allowing me to simply select which dataset to use instead of having to type the exact naming... Is this possible?

 

[arnelgp]

you can create a pop-up, modal form with listbox and a command button.
the command button is there to launch which dataset to use from the listbox.
the listbox should contain two columns (the other column, the dataset name, hidden).
listbox rowsource type should be value list, with values of:

"Voertuig informatie"," "m9d7-ebf2.xml",""Assen","3huj-srit.xml","Brandstof","8ys7-d773.xml","Carrosserie","vezc-m2t6.xml","Carrosserie_specifiek","jhie-znh9.xml","Voertuigklasse","kmfi-hrps.xml"

on the load event of this form, set the value of the listbox to the first item in the list:

private sub form_load()
me.list0 = me.list.itemdata(0)
end sub

 

[MrHans]

Thank you Arnel, that's indeed another approach.
But if possible in any way, I would like to include it in the function call.
In the end I want to enter 1 license plate and find different bits and pieces of information from several datasets.

Combining the vehicle information, with fuel information and axes information in 1 result.

Do you know how the built-in functions from Access are made?
For example the message box function, it gives you the option to choose from predefined button (vbYesNo,vbOkOnly etc) and predefined types (vbQuestion, vbExclamation, vbCritical)

 

[arnelgp]

you need to create a public enumeration inside your function:

Public Enum vehInfo
vehVoertuig_informatie
vehAssen
vehBrandstof
vehCarrosserie
vehCarrosserie_specifiek
vehVoertuigklasse
End Enum

now you must change your function declaration:

Function GetVehicleDetails(Type As vehInfo, strPlate As String) As String

and you must explicitly process (extract) the Type of info from your code:

If Type And veInfo.vehVoertuig_informatie then
'call the function to extract that info
End If
If Type And vehInfo.vehAssen Then
'call the function again with this info
End If
... and so on...

 

[MrHans]

Brilliant, thank you Arnel.
Customized it a bit, but this is working perfectly!

Public Enum RdwDataset

    rdwVoertuig_informatie
    rdwAssen
    rdwBrandstof
    rdwCarrosserie
    rdwCarrosserie_specifiek
    rdwVoertuigklasse

End Enum

Function GetVehicleDetails(strPlate As String, dataset As RdwDataset) As String

    'Create required variables
    Dim xmldoc As MSXML2.DOMDocument60
    Dim xmlNodeList As MSXML2.IXMLDOMNodeList
    Dim xmlNode As MSXML2.IXMLDOMNode
    Dim myNode As MSXML2.IXMLDOMNode

    'Determine which dataset to be used
    Dim strDataset As String
    Select Case dataset
        Case RdwDataset.rdwVoertuig_informatie
            strDataset = "m9d7-ebf2.xml"
        Case RdwDataset.rdwAssen
            strDataset = "3huj-srit.xml"
        Case RdwDataset.rdwBrandstof
            strDataset = "8ys7-d773.xml"
        Case RdwDataset.rdwCarrosserie
            strDataset = "vezc-m2t6.xml"
        Case RdwDataset.rdwCarrosserie_specifiek
            strDataset = "jhie-znh9.xml"
        Case RdwDataset.rdwVoertuigklasse
            strDataset = "kmfi-hrps.xml"
    End Select

    'Assemble the query string
    Dim strQuery As String
    strQuery = "https://opendata.rdw.nl/resource/"
    strQuery = strQuery & strDataset
    strQuery = strQuery & "?kenteken=" & Replace(UCase(strPlate), "-", "")

    'define XML and HTTP components
    Dim xmlService As New MSXML2.XMLHTTP60
    xmlService.Open "GET", strQuery, False
    xmlService.Send

    Set xmldoc = New MSXML2.DOMDocument60
    xmldoc.async = False
    xmldoc.LoadXML (xmlService.responseText)

    Set xmlNodeList = xmldoc.getElementsByTagName("*")
    For Each xmlNode In xmlNodeList
        For Each myNode In xmlNode.childNodes
            If myNode.NodeType = NODE_TEXT Then
                Debug.Print xmlNode.nodeName & "=" & xmlNode.Text
            End If
        Next myNode
    Next xmlNode
    Set xmldoc = Nothing

End Function

 

[MarkK]

I would put this data in a table . . .

    Select Case dataset
        Case RdwDataset.rdwVoertuig_informatie
            strDataset = "m9d7-ebf2.xml"
        Case RdwDataset.rdwAssen
            strDataset = "3huj-srit.xml"
        Case RdwDataset.rdwBrandstof
            strDataset = "8ys7-d773.xml"
        Case RdwDataset.rdwCarrosserie
            strDataset = "vezc-m2t6.xml"
        Case RdwDataset.rdwCarrosserie_specifiek
            strDataset = "jhie-znh9.xml"
        Case RdwDataset.rdwVoertuigklasse
            strDataset = "kmfi-hrps.xml"
    End Select

. . . not in my code.

 

[Galaxiom]

Public Enum RdwDataset

    rdwVoertuig_informatie
    rdwAssen
    rdwBrandstof
    rdwCarrosserie
    rdwCarrosserie_specifiek
    rdwVoertuigklasse

End Enum

That is not how Enums are normally used and misses half the point of having them.

Enums assign a value (typically a Long) to the members.

Public Enum RdwDataset

    rdwVoertuig_informatie = 1
    rdwAssen = 2
    rdwBrandstof = 3
    rdwCarrosserie = 4
    rdwCarrosserie_specifiek = 5
    rdwVoertuigklasse = 6

End Enum

Then they are used in the function like this

    Dim strDataset As String
    Select Case dataset
        Case 1
            strDataset = "m9d7-ebf2.xml"
        Case 2
            strDataset = "3huj-srit.xml"
        Case 3
            strDataset = "8ys7-d773.xml"
        Case 4
            strDataset = "vezc-m2t6.xml"
        Case 5
            strDataset = "jhie-znh9.xml"
        Case 6
            strDataset = "kmfi-hrps.xml"
    End Select

Alternatively the values of the Enum members could have the strings assigned directly to them and used in your function with the single line.

strDataset = dataset

However I agree with Mark. The filenames are better held in a table than hardcoded. This allows new values to be added easily added.

 

[arnelgp]

That is not how Enums are normally used and misses half the point of having them.

if did not assign the type and value, the default is integer and the first element is 0 and exceedingly increment by 1.

https://msdn.microsoft.com/en-us/library/y94acxy2(v=vs.90).aspx