GetCustomerInformation procedure

The GetCustomerInformation procedure retrieves information about a single customer from the external database.

First, it will attempt to find a record that matches the customer name provided as input. This record will be found unless:

• The user entered a customer name that is not in the database. (Normally this will not happen because the lookup list is automatically updated.)
• The customer name was changed in the external database.
• The Customer Name property for existing documents in the vault still holds the old value.

The customer name may have been successfully resolved in a earlier call to this task. In that case, the document in the vault will hold a value in CustomerID.

If the customer record cannot be found by name, the task will retry using the value of CustomerID. This will resolve the situation where the name was changed in the database.

When the customer record is found, the procedure will return the field values. If the record is not found, a default value is returned ("<not available>").

' Find the Customer Information in the external database
Public Sub GetCustomerInformation(CustomerID As String, _
Name As String, _
Address As String, _
AccountNumber As String, _
City As String, _
Zipcode As String)
On Error GoTo eh
 
' The CustomerID and/or Name are provided as input
' This method finds the corresponding customer information
 
' In this sample we use a MS Access database to retrieve customer data
' This requires a reference to "Microsoft ActiveX Data Object 2.5" of higher.
Dim Conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Conn.Open ConnectString
' The users selects the customer by name
' So first we try to find the customer record by [Name]
Set rs = Conn.Execute("SELECT * FROM " & CustomerTable & " WHERE " & fldName & "='" & Name & "';")
If rs.EOF Then
' If the [Name] does not exist (has been changed) in the MDB we try to find the [CustomerID]
' and return the new name.
If Len(CustomerID) > 0 Then
Set rs = Conn.Execute("SELECT * FROM " & CustomerTable & " WHERE " & fldCustomerID & " = " & CustomerID)
End If
End If
 
If Not rs.EOF Then
Name = CStr(rs.Fields(fldName))
CustomerID = CStr(rs.Fields(fldCustomerID))
Address = CStr(rs.Fields(fldAddress))
AccountNumber = CStr(rs.Fields(fldAccountNumber))
City = CStr(rs.Fields(fldCity))
Zipcode = CStr(rs.Fields(fldZipcode))
Else
' No record found
Name = Name
CustomerID = ""
Address = "<not available>"
AccountNumber = "<not available>"
City = "<not available>"
Zipcode = "<not available>"
End If
 
rs.Close
Conn.Close
 
Exit Sub
eh:
Err.Raise -1, "GetCustomerInformation", "Failed to get customer data from database" & vbCrLf & Err.Description
End Sub