Use the MoveNext method to move to the next record in the recordset. After the routine finishes processing the recordset, close the routine using the Close method. Then, complete the If statement:
envNorthwind.rscmdCustomer.Close
End If
Finally, set the return value of the function to the sCustomerList array:
LoadCustomer = sCustomerList
Now you can create the function that looks up the customer details. The function uses CustomerID as a parameter and returns the detail records in an array. You can define this function, GetCustomer, using this code:
Function GetCustomer(CustomerID As String)
As Variant
Dim sCustomer(5) As String
Dim i As Integer, j As Integer
Next, make sure the sCustomer array contains empty records.
For i = 0 To 4
sCustomer(i) = ""
Next
The cmdCustomerByID data command uses a parameter that contains CustomerID. You can use one of two methods to set the parameter and execute the command. I recommend you perform these actions in one step using this syntax:
envNorthwind.cmdCustomerByID CustomerID
This command executes cmdGetCustomerByID and passes the parameter in on the command line, which makes the code easy to follow and reduces the number of lines required to perform these actions.
Next, you can retrieve the number of records in the recordset as demonstrated earlier. The current function doesn't return more than one record because you're performing the lookup by CustomerID, which is a unique field. The code to retrieve the record count and check for a valid record is:
i = envNorthwind.rscmdCustomerByID.RecordCount
If i > 0 Then
Next, you can use a block of IF statements and assignment statements to retrieve field values. The IF statement uses the IsNull function to make sure the recordset field returns a valid value. If you don't check for a null value, VB will generate a run-time error when it encounters one. The following code prevents run-time errors. The If statement retrieves the value from the recordset and stores it in the correct position in the array, Listing 2, page 69 shows. Finally, close the recordset, and set the return value from the function:
envNorthwind.rscmdCustomerByID.Close
GetCustomer = sCustomer
Listing 1 shows the code to wire these functions into the events behind the interface.
The Data Environment is a useful addition to the database features in VB. Although the Data Environment adds overhead to the application's execution speed, it simplifies application development and maintenance. This simplification can result in significant savings in resources during the application's life. You can use the data commands in this article as an example to create database objects and reuse them in your code. The data command makes application maintenance easy: If you change the data command, you'll affect everything in your application that uses the command.
End of Article
Prev. page
1
2
[3]
next page -->
