The COM Course - Part 2, Page 7
So, we've created our COM component and now we need to test it. Let's shoot:
- Close your Northwind project
- Create a new 'Standard EXE'
This program will access our class.
- Add a Label and List Box to Form1, like this:
That List Box will hold a list of our customers and their IDs. Here, I want the user to be able to click the Find Customer button and have another screen popup with the editable customer details. Let's design that screen now:
- Click 'Project', 'Add Form'
- Add seven Labels, seven Text Boxes and a Command Button to your Form
- Organise your controls like this:
Brilliant! That's our application designed. Now let's get coding.
Before working with our Northwind project, we need to add a reference to it:
- Click 'Project', 'References'
Have a quick scroll up-and-down the list. This is where you find other COM components such as the ADO library and Excel automation widgets.
- Find and check 'Northwind', then click 'OK'
Adding this reference means your application can now use the power of Northwind, the program you created just a few minutes ago.
- Add the following code behind Form1:
Private Sub Form_Load() Dim Cust As Customer Set Cust = New Customer Do Until Cust.EOF = True List1.AddItem (Cust.CustomerID & " " & _ Cust.CompanyName) Cust.MoveNext Loop Set Cust = Nothing End SubPrivate Sub lstCustomers_Click() Dim strCustomerID As String ' Grab the CustomerID from the List Box entry strCustomerID = Mid(lstCustomers.Text, 1, 5) ' Load the Customer Form2.LoadCustomer (strCustomerID)End Sub
Hopefully you should find this code amazingly easy to follow. During Form_Load, the program creates a new instance of our Customer object. It then loops round, adding customer IDs and company names to our List Box, until the EOF property is True.
Top Tip: EOF stands for End Of File. The EOF property of a recordset is equal to True when it has reached the 'end' of the records. For more information, take our database tutorial here.
Finally, the code sets our Customer object equal to nothing.
The code behind our List Box simply shows Form2 then runs the LoadCustomer method. The LoadCustomer method? Oh yes... we've not programmed that one yet. We'll get round to it in just a few minutes, but all it will do is accept the Customer ID and look up our customer. And that's as easy as your local 80s music station thanks to your new COM component.
Our code here actually chops the first five characters from the item selected in our List Box, then passes it to the LoadCustomer method. And coincidentally enough, those first five characters are the customers ID.
Let's add the code for Form2 now:
- Add the following code behind Form2:
Dim Cust As CustomerPublic Sub LoadCustomer(CustomerID As String) Set Cust = New Customer Cust.FindByCustomerID (CustomerID) With Cust Text1.Text = .CustomerID Text2.Text = .CompanyName Text3.Text = .ContactName Text4.Text = .Address Text5.Text = .City Text6.Text = .Country Text7.Text = .Phone End With End SubPrivate Sub Command1_Click() With Cust .CustomerID = Text1.Text .CompanyName = Text2.Text .ContactName = Text3.Text .Address = Text4.Text .City = Text5.Text .Country = Text6.Text .Phone = Text7.Text .Update End With Unload MeEnd Sub
Here, we have a Customer object declared as Cust right at the top of the code.
Top Tip: If you declare an object behind, say, a Command Button as in Form1 only the code behind that Command Button can see and use it. But if you declare an object at the top of your code in General Declarations any chunk of code in your Form will be able to access it.
First off, our LoadCustomer method. This starts off by creating a new Customer object, then runs the FindCustomerByID method to locate the customer. Once located, various Customer properties are then dumped straight into our seven Text Boxes.
After any editing, our user will then click the 'Save + Close' Command Button. Here, our code simply takes the information from the Text Boxes and slots it straight back into the Customer object. Finally, our code runs an Update then unloads our Form.