| This section demonstrates building a user interface using eMbedded Visual Basic forms. In creating the application, you will create a program that will run on the Pocket PC, which works like a Windows Application project running on a desktop PC. Our sample application is an inventory-tracking program for a warehouse. As truck drivers load and unload products, the warehouse manager enters the data on a Pocket PC he carries at the loading dock. Later, when the manager returns to his office, the information he gathered can be automatically loaded to a central database via his desktop PC. To begin building the sample application, you'll need to start eMbedded Visual Basic and choose to create a New Windows CE for the Pocket PC Project. In this section, we'll write the code that actually runs on the Pocket PC. In the following sections, we will explore how to transfer data from the Pocket PC to a PC database. Designing the User Interface A Pocket PC has a much smaller screen than a standard PC, so the available space for controls on a form is very limited. The Pocket PC pictured in Figure 27.1 has a total screen area of 240 x 320 pixels, which is much smaller than a typical desktop PC. In addition, a Pocket PC does not usually have a keyboard attached, so a "virtual" keyboard appears on the screen for text input. When the keyboard is displayed on the screen, parts of your form may be obscured. For these reasons, you need to put extra thought into user interface design for the Pocket PC. As we will see, the controls provided with eMbedded Visual Basic provide a good number of options for designing user interfaces. Adding the Basic Controls For our sample inventory tracking application, we will design the user interface so that typed data entry is kept to a minimum. To set up the form, perform the following steps: -
Set the Name property of the form to frmMain and the Caption property to Inventory Maintenance. -
Set the Name property of the project to prjInventory. -
Add the controls to the form as specified in Table 27.1, setting their Name and Caption properties appropriately. -
Set the Value property for optReceived to True. -
Set the Enabled property for tmrQuantity to False. -
Set the Text property of cmbDriver and cmbProduct to a blank string.
Table 27.1. Setting Up Controls for the Sample Application| Control Type | Name | Caption |
|---|
| Label | lblDriver | Driver Name | | Label | lblProduct | Product | | Label | lblQuantity | Quantity | | ComboBox | cmbDriver | | | ComboBox | cmbProduct | | | Timer | tmrQuantity | | | TextBox | txtQuantity | 0 | | OptionButton | optShipped | Shipped | | OptionButton | optReceived | Received | | CommandButton | cmdPlus | + | | CommandButton | cmdMinus | - | | CommandButton | cmdConfirm | Confirm |
Adding Additional Controls All the controls listed in Table 27.1 are included in the Toolbox by default. However, several other controls are available by selecting Components from the Project menu. In our sample project, we will be using two of these controls, the ListView control and the FileSystem control. To set up these controls, perform the following steps: -
From the Project menu, choose Components. Select the Microsoft CE ListView Control and the Microsoft CE File System Control. Press OK to close the dialog box. -
Add a ListView control to your form. Set its Name property to lvData. -
Add a FileSystem control to your form. Set its Name property to fsMain. -
From the Project Explorer, right-click frmMain and choose View Code. Add the following lines of code to the form's Load event: lvData.View = lvwReport lvData.ColumnHeaders.Add , , "Entered", 750 lvData.ColumnHeaders.Add , , "Driver", 1000 lvData.ColumnHeaders.Add , , "Qty.", 500 lvData.ColumnHeaders.Add , , "Product", 900 -
Arrange all the controls on the form so that your form appears similar to Figure 27.4. Figure 27.4. You can arrange controls and set their properties in eMbedded Visual Basic using the same techniques that you use in Visual Basic .NET. 
As we continue coding the project, we will see how to use the FileSystem control to check the Pocket PC file system, and the ListView to display data in a tabular fashion. Dynamically Resizing the Form Now that you have added all the controls to your form, you need to write some code so the user interface behaves appropriately in the Pocket PC environment. When browsing through the form's event procedures you may have noticed a couple of CE-specific events: OKClick Notice that the OKClick event procedure is already filled in for you with an App.End statement. Pocket PC applications generally have an OK button at the top of the form, which closes the current screen. SIPChange You can add code to this event to move controls or resize the form when the Pocket PC's onscreen keyboard is displayed.
The keyboard and handwriting recognition capabilities of a Pocket PC are known as the soft input panel, or SIP. Other than the size, this is probably the most obvious difference between using a Pocket PC and a desktop PC. By handling the SIPChange event in your code, you can automatically alter the appearance of the form when the SIP is present. To add this capability to the sample application, perform the following steps: -
Make sure the form's FormResize property is set to vbFormFullSIPResize. This will cause the form to be displayed full-screen, with borders that automatically resize when the SIP is displayed. -
Add the following line of code to the SIPChange event: lvData.Height = frmMain.Height - 1500
This line of code causes the height of the list view to be set to 1,500 pixels less than the form's height whenever the SIPChange event fires. Note Although the user is ultimately in control of the SIP, setting the SIPBehavior property of a form gives the programmer control over whether the SIP is displayed at startup. You can also turn on an automatic mode so that the keyboard will appear when certain types of controls receive focus.
Aiding the User with Text Input In our sample application, the user will need to update the quantity text box with the number of products being shipped or received at the warehouse. The SIP can be used with any text box to enter text. However, trying to type on a miniature keyboard at a busy loading dock may not always be convenient. Therefore, our user interface includes two small command buttons to increment or decrement the quantity in the text box. The simplest way to code the function of these buttons would be to add a line of code to their Click events. However, making large changes to the quantity would require repeated clicks, which is also inconvenient. If you have set a digital clock or timer, you may have noticed that by holding the button down you can vary the speed with which the number changes. By adding a few extra lines of code and a timer control, we can add similar functionality to the UI of our sample application. To accomplish this, add the code from Listing 27.1 to your form: Listing 27.1 PPCDEMO.ZIP Changing the Quantity Quickly Dim m_nIncrement As Integer Private Sub cmdMinus_MouseDown(ByVal Button As Integer, ByVal Shift As Integer,_ ByVal X As Single, ByVal Y As Single) m_nIncrement = -1 tmrQuantity.Interval = 300 tmrQuantity.Enabled = True End Sub Private Sub cmdMinus_MouseUp(ByVal Button As Integer, ByVal Shift As Integer,_ ByVal X As Single, ByVal Y As Single) tmrQuantity.Enabled = False End Sub Private Sub cmdPlus_MouseDown(ByVal Button As Integer, ByVal Shift As Integer,_ ByVal X As Single, ByVal Y As Single) m_nIncrement = 1 tmrQuantity.Interval = 300 tmrQuantity.Enabled = True End Sub Private Sub cmdPlus_MouseUp(ByVal Button As Integer, ByVal Shift As Integer,_ ByVal X As Single, ByVal Y As Single) tmrQuantity.Enabled = False End Sub Private Sub tmrQuantity_Timer() txtQuantity.Text = CLng(txtQuantity.Text) + m_nIncrement If CLng(txtQuantity.Text) < 0 Then txtQuantity.Text = 0 tmrQuantity.Interval = tmrQuantity.Interval - 50 If tmrQuantity.Interval < 50 Then m_nIncrement = m_nIncrement * 5 tmrQuantity.Interval = 400 End If End Sub Listing 27.1 employs a Timer control that is enabled as long as the mouse (or in the case of a Pocket PC, the finger or stylus) is depressed. By decreasing the value of the timer's Interval property and changing the quantity increment variable, the value in the text box changesmore rapidly as the user continues to hold down the button. Testing the User Interface Before continuingwith coding, you should test the basics of your user interface. This will mean running your eMbedded Visual Basic program for the first time. To do so, we will need to set the remote project name and target. This can be accomplished by using the Project Properties dialog box, shown in Figure 27.5. Display the dialog box by selecting Properties from the Project menu. Next, make sure "Pocket PC Emulation" is selected in the Run on Target combo box. Finally, change the Remote Path to \Windows\Start Menu\Inventory.vb. Figure 27.5. You can also change the target directly from the eMbedded Visual Basic toolbar.  After you have changed the settings to match Figure 27.5, press the Start button and your application should appear on the emulator screen, as shown in Figure 27.6. Figure 27.6. When you run an application in embedded VB, you can choose to run it on the emulator or an actual Pocket PC.  Test the UI features we just implemented by clicking on the keyboard icon to display the SIP, as well as clicking and holding the plus and minus buttons to change the quantity value. To end your debugging session, press the OK button on the emulator, or the Stop button in eMbedded Visual Basic. Note Each time you run a Pocket PC project on the device or emulator, the files are copied to the target, so you can execute the project directly from the Start menu of the target device.
Adding Database Capability with ADOCE Now that we have the sample user interface created, we need a way to transfer database information to and from the Pocket PC application. (Keep in mind we're assuming that the Pocket PC does not have a live continuous network connection; if that was the case your application could communicate directly with a Web or database server.) The Windows CE operating system supports a minimal version of ADO known as ADOCE. In this section, you will learn how to use ADOCE to create database tables on your Pocket PC, as well as update information in an ADOCE recordset.  For more on ADO, p.575
To begin using ADOCE, add a reference to it by selecting References from the Project menu. From the references dialog box, shown in Figure 27.7, select Microsoft CE ADO Control. Figure 27.7. Before you can use ADOCE in your eMbedded Visual Basic project, you must add a reference.  The process of installing ADO or other controls on the emulator is handled automatically when you start a debugging session with eMbedded Visual Basic. Creating a Database on the Pocket PC Because our sample database is designed for data gathering and subsequent upload to a central database, the data structure on the Pocket PC will be very simple: just a single table containing a record of each inventory transaction. For ease of installation, we'll add code to automatically create the database on the Pocket PC if it does not exist. After creating the database table, we will store references to the database in form-level variables. All these tasks are accomplished by the InitDatabase custom function, shown in Listing 27.2. Listing 27.2 PPCDEMO.ZIP DDL Statements Using ADOCE Private Sub InitDatabase() 'This function prepares the database and is called at program startup Dim rsTemp As ADOCE.Recordset Const sDBPath = "\My Documents\InventoryDB.cdb" 'If database file does not exist then create it If fsMain.Dir(sDBPath) = "" Then Set rsTemp = CreateObject("adoce.recordset.3.0") rsTemp.Open "CREATE DATABASE '" & sDBPath & "'" End If 'Connect to the database file Set m_cnLocal = CreateObject("ADOCE.Connection.3.0") m_cnLocal.Open (sDBPath) 'If database table does not exist then create it Set rsTemp = m_cnLocal.Execute ("Select * from MSysTables Where TableName = 'InventoryInfo") If rsTemp.RecordCount = 0 Then Call m_cnLocal.Execute("CREATE table InventoryInfo (DateEntered DateTime,  DriverName varchar(30), Quantity Integer, Product varchar(15))") End If Set rsTemp = Nothing 'Open module-level recordset Set m_rsData = CreateObject("ADOCE.Recordset.3.0") Set m_rsData.ActiveConnection = m_cnLocal m_rsData.Open "Select * from InventoryInfo", , adOpenKeyset, adLockOptimistic 'Call function to load data in listview Call DisplayEnteredData End Sub Private Sub Form_OKClick() Set m_rsData = Nothing m_cnLocal.Close Set m_cnLocal = Nothing App.End End Sub The InitDatabase subroutine in Listing 27.2 uses the Dir method of the FileSystem control to check whether a database file named InventoryDB.cdb exists in the current directory. If the file does not exist, then the database and table are created. Also note that the OKClick event procedure has been modified to close the data connection and recordset before the program ends. For more on Dir and other file system functions, p.667 ADOCE's native database file format uses the cdb extension. A Windows CE database is a limited version of the Microsoft Access Database (mdb) format. ADOCE supports the familiar Recordset and Connection objects from ADO, which are used in the example to execute several SQL statements. As you may recall from the Database Basics chapter, you can use SQL statements to create database objects as well as query and update data. For more on SQL statements, p.535 The code in Listing 27.2 uses a CREATE DATABASE statement to create the database file. Next, it executes a query on the MSysTables table to determine whether the InventoryInfo table exists. If the table does not exist, it is created with a CREATE TABLE statement. Finally, a SELECT query is executed to open a module-level Recordset object, m_rsData. By manipulating the m_rsData object, we can read or write to the database table. Note The data types available in ADOCE are slightly different from those in Access or SQL server. For more details, see the eMbedded Visual Basic help topic "Converting Data to a Device."
Note ADOCE can also be used to connect to ODBC or OLEDB data sources on a connected network. There is even a version of SQL Server designed to run on the PocketPC. Visit www.pocketpc.com for more information.
Adding Data to the Recordset The call to the Open method in Listing 27.2 specifies the cursor and lock types so that the returned recordset is updateable. To write the code that adds a new record, simply add the following statements to the Click event of cmdConfirm. Private Sub cmdConfirm_Click() 'Add a new record to the recordset m_rsData.AddNew m_rsData.Fields("DateEntered") = Now m_rsData.Fields("DriverName") = cmbDriver.Text m_rsData.Fields("Quantity") = txtQuantity.Text m_rsData.Fields("Product") = cmbProduct.Text m_rsData.Update 'Call function to load data in listview Call DisplayEnteredData End Sub The preceding code starts the process of adding a new record by calling the AddNew method. Next, it assigns values to the fields. Finally, the Update method is invoked to commit the data to the database. As you can see, ADOCE recordsets work very much like ADO recordsets. Note You can also use SQL statements to update data by executing an INSERT command.
Displaying Data in the ListView Now that we have a means to enter data on the Pocket PC, we need to add code to populate the list view with data from the InventoryInfo table. This will give the user a running list of the data as he enters it. To keep the list current, we will update it at program startup and every time a new record is added. A custom subroutine, DisplayEnteredData, will populate the list view by browsing through the recordset. Listing 27.3 shows this function. Listing 27.3 PPCDEMO.ZIP Using a ListView Private Sub DisplayEnteredData() Dim itm As ListItem lvData.ListItems.Clear If m_rsData.RecordCount <> 0 Then m_rsData.MoveFirst 'Load recordset data in the list view While Not m_rsData.EOF Set itm = lvData.ListItems.Add(, ,_ FormatDateTime("" & m_rsData.Fields("DateEntered"), vbShortTime)) itm.SubItems(1) = m_rsData.Fields("DriverName") itm.SubItems(2) = m_rsData.Fields("Quantity") itm.SubItems(3) = m_rsData.Fields("Product") m_rsData.MoveNext Wend 'Display list of entered drivers, products Call UpdateComboBox(1, cmbDriver) Call UpdateComboBox(3, cmbProduct) 'Sort data by entry time lvData.SortKey = 0 lvData.SortOrder = lvwDescending End If End Sub Private Sub UpdateComboBox(nSortKey As Integer, cmbX As ComboBox) 'Fill up a combo box with distinct listview items '(because we cannot use SELECT DISTINCT with a CDB file) Dim i As Integer Dim sLastItem As String Dim sCurrItem As String lvData.SortKey = nSortKey lvData.SortOrder = lvwAscending lvData.Sorted = True cmbX.Clear For i = 1 To lvData.ListItems.Count sCurrItem = lvData.ListItems(i).SubItems(nSortKey) If sCurrItem <> sLastItem Then cmbX.AddItem sCurrItem sLastItem = sCurrItem End If Next End Sub Private Sub lvData_ColumnClick(ByVal Index As Long) 'Sorts the list view when a column is clicked Dim nTemp As Integer nTemp = Index - 1 If nTemp < 0 Then nTemp = 0 If nTemp >= 0 And nTemp < lvData.ColumnHeaders.Count Then If lvData.SortOrder = lvwAscending Then lvData.SortOrder = lvwDescending Else lvData.SortOrder = lvwAscending End If lvData.SortKey = nTemp lvData.Sorted = True End If End Sub The UpdateComboBox subroutine in Listing 27.3 updates the combo boxes with the list of drivers and products already entered, so the user will not have to retype them. In addition, the ColumnClick event procedure contains code to sort the list view when the user clicks on a column header. Testing the Sample Application Because the data access code is more complicated than the user interface code, you may run into errors when executing your program. eMbedded Visual Basic contains the same types of debugging tools as regular Visual Basic, with some notable limitations: You can set breakpoints and step through code in eMbedded Visual Basic, but you cannot change the code "on the fly." The Set Next Statement function is not available when debugging in eMbedded Visual Basic. Only limited error handling is available (non-structured with On Error Resume Next and checking the Error variable). Breakpoints must be initiated from the desktop PC. In other words, you cannot press Ctrl+Break on the Pocket PC and then return to Visual Basic.
Although these restrictions may seem very limiting at first, keep in mind you are actually running your program on a separate operating system, optimized for pocket computing. If you do encounter errors, set up breakpoints at the beginning of suspected functions and then step through the code to find them. After your sample application is working, run it on the emulator and perform a test of the data entry capabilities: -
Click in the Driver combo box and use the keyboard to enter a driver's name. -
Click in the Product combo box and use the keyboard to enter a product name. -
Click and hold the Plus button to select a quantity. -
Select Received to specify a positive quantity. -
Click Confirm and your information should show up in the list. -
Click the arrow next to the Driver combo box and pick the driver's name you previously entered. -
Click in the Product combo box and use the keyboard to enter a product name. -
Click in the Quantity box and use the keyboard to enter a quantity number. -
Click Confirm and the list should now contain two rows of information. -
Click the Product and Quantity column headers to sort the list.
If the program is working correctly, you should be able to enter data and see it displayed on the screen, as shown in Figure 27.8. Figure 27.8. The sample application updates the grid each time a new record is added.  After you have finished testing your application, close the emulator and connect your real Pocket PC. Change the target device setting to Pocket PC (Default Device) and test the application on the Pocket PC by repeating the preceding steps. The process of running the application on your Pocket PC will create a Start Menu item for the inventory project. The remainder of this chapter assumes you have closed eMbedded Visual Basic and the emulator and are running the finished sample application directly from your Pocket PC Start menu. | 
