ch35.htm

MAPI__SAPI__TAPI

FACE="Courier">Edit</font></tt>, <tt><font FACE="Courier">Delete</font></tt>, <tt><font
FACE="Courier">Dial</font></tt>, and <tt><font FACE="Courier">Find</font></tt>). All these 
list commands refer to the <tt><font FACE="Courier">Name</font></tt> list. This list will 
be filled in at run-time. </p>

<p>Now add the routine that will fill in the name list for the voice commands. Create a 
new subroutine called <tt><font FACE="Courier">LoadNameList</font></tt> and enter the code 
you see in Listing 35.10. </p>

<hr>

<blockquote>
  <b><p>Listing 35.10. Adding the <tt><font FACE="Courier">LoadNameList</font></tt> routine.<br>
  </b></p>
</blockquote>

<blockquote>
  <tt><font FACE="Courier"><p>Public Sub LoadNameList()<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;' fill list of names for vCmd<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim cNameList As String<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim iNameCount As String<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cNameList = &quot;&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;iNameCount = 0<br>
  &nbsp;&nbsp;&nbsp;&nbsp;rsPhone.MoveFirst<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Do Until rsPhone.EOF<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cNameList = cNameList &amp; 
  rsPhone.Fields(&quot;Name&quot;) &amp; Chr(0)<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;iNameCount = iNameCount + 1<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rsPhone.MoveNext <br>
  &nbsp;&nbsp;&nbsp;&nbsp;Loop<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;objVMenu.ListSet &quot;Name&quot;, iNameCount, cNameList<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  End Sub</font></tt> </p>
</blockquote>

<hr>

<p>As you can see, the <tt><font FACE="Courier">LoadNameList</font></tt> routine reads 
each record in the open database table and adds the names, separated by <tt><font
FACE="Courier">chr(0)</font></tt>, to a single string. This string is the list that is 
registered with the menu object using the <tt><font FACE="Courier">ListSet</font></tt> 
method. </p>

<h3><a NAME="AddingtheVoiceTextRoutines"><b>Adding the Voice Text Routines</b></a></h3>

<p>Now you're ready to add the routines that will initialize the Voice Text object for TTS 
services. First, add a new subroutine called <tt><font FACE="Courier">InitVTxt</font></tt> 
and enter the code shown in Listing 35.11. </p>

<hr>

<blockquote>
  <b><p>Listing 35.11. Adding the <tt><font FACE="Courier">InitVTxt</font></tt> routine.<br>
  </b></p>
</blockquote>

<blockquote>
  <tt><font FACE="Courier"><p>Public Sub InitVTxt()<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;' init voice text<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set objVText = CreateObject(&quot;Speech.VoiceText&quot;) <br>
  &nbsp;&nbsp;&nbsp;&nbsp;objVText.Register &quot;&quot;, App.EXEName <br>
  &nbsp;&nbsp;&nbsp;&nbsp;objVText.Enabled = True<br>
  &nbsp;&nbsp;&nbsp;&nbsp;objVText.Callback = App.EXEName &amp; &quot;.TTSCallBack&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  End Sub</font></tt> </p>
</blockquote>

<hr>

<p>This looks a lot like the <tt><font FACE="Courier">InitVCmd</font></tt> routine. Notice 
the callback registration here, too. Again, it is very important that the <tt><font
FACE="Courier">Project Title</font></tt> property (<tt><font FACE="Courier">Tools | 
Options | Project | Project Title</font></tt>) is set to the same value as the application 
executable filename. If not, you won't be able to use TTS services in your application. </p>

<p>The only other TTS support routine you'll need is the one that builds a set of messages 
that will be spoken by the TTS engine at different times in the program. Add a new 
subroutine called <tt><font FACE="Courier">LoadMsgs</font></tt> to the module and enter 
the code from Listing 35.12. </p>

<hr>

<blockquote>
  <b><p>Listing 35.12. Adding the <tt><font FACE="Courier">LoadMsgs</font></tt> routine.<br>
  </b></p>
</blockquote>

<blockquote>
  <tt><font FACE="Courier"><p>Public Sub LoadMsgs()<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;' build a list of user messages<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;ReDim cUserMsgs(5)<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cUserMsgs(0) = &quot;Hello. Welcome to Voice Phone.&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cUserMsgs(1) = &quot;Press New, Edit, or Delete to modify the 
  phone list.&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cUserMsgs(2) = &quot;Select a name from the list and press Place 
  Call to dial the <font FACE="ZAPFDINGBATS">&Acirc;</font>phone.&quot; <br>
  &nbsp;&nbsp;&nbsp;&nbsp;cUserMsgs(3) = &quot;Press Exit to end this program.&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cUserMsgs(4) = &quot;Enter name and phone number, then press OK or 
  cancel.&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  End Sub</font></tt> </p>
</blockquote>

<hr>

<p>The public constants declared at the top of this module will be used to point to each 
of these messages. This will make it easier to read the code. </p>

<h3><a NAME="AddingtheDatabaseEngineRoutines"><b>Adding the Database Engine Routines</b></a></h3>

<p>Next you need to add several routines to initialize and support database services. 
First, add the <tt><font FACE="Courier">InitDB</font></tt> subroutine to your project and 
enter the code shown in Listing 35.13. </p>

<hr>

<blockquote>
  <b><p>Listing 35.13. Adding the <tt><font FACE="Courier">InitDB</font></tt> routine.<br>
  </b></p>
</blockquote>

<blockquote>
  <tt><font FACE="Courier"><p>Public Sub InitDB()<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;' set up db stuff<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim cDBName As String<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cDBName = App.Path &amp; &quot;\VPHONE.MDB&quot; <br>
  &nbsp;&nbsp;&nbsp;&nbsp;On Error Resume Next<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Open cDBName For Input As 1<br>
  &nbsp;&nbsp;&nbsp;&nbsp;If Err &lt;&gt; 0 Then<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Close 1<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BuildDatabase <br>
  &nbsp;&nbsp;&nbsp;&nbsp;Else<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Close 1<br>
  &nbsp;&nbsp;&nbsp;&nbsp;End If<br>
  &nbsp;&nbsp;&nbsp;&nbsp;On Error GoTo 0<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;OpenDatabase<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  End Sub</font></tt> </p>
</blockquote>

<hr>
<div align="center"><center>

<table BORDERCOLOR="#000000" BORDER="1" WIDTH="80%">
  <tr>
    <td><b>Warning</b> </td>
  </tr>
  <tr>
    <td><blockquote>
      <p>Using the <tt><font FACE="Courier">App.Path</font></tt> property to set the location of 
      the MDB file assumes that you have created a project directory. If you attempt to place 
      the project and the MDB files in the root directory of a drive, you'll receive error 
      messages. It's recommended that you create a project directory and store the MDB in that 
      directory. </p>
    </blockquote>
    </td>
  </tr>
</table>
</center></div>

<p>The only real purpose of this routine is to check for the existence of the database 
file. If it is not found, the <tt><font FACE="Courier">BuildDatabase</font></tt> routine 
is called before the <tt><font FACE="Courier">OpenDatabase</font></tt> routine. Now add 
the <tt><font FACE="Courier">BuildDatabase</font></tt> subroutine to your project as shown 
in Listing 35.14. </p>

<hr>

<blockquote>
  <b><p>Listing 35.14. Adding the <tt><font FACE="Courier">BuildDatabase</font></tt> 
  routine.<br>
  </b></p>
</blockquote>

<blockquote>
  <tt><font FACE="Courier"><p>Public Sub BuildDatabase()<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;' build new database<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;On Error GoTo LocalErr<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim ws As Workspace<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim cSQL(3) As String<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim x As Integer<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cSQL(1) = &quot;CREATE TABLE VPHONE (Name TEXT(20),Phone 
  TEXT(20));&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cSQL(2) = &quot;INSERT INTO VPHONE VALUES 
  ('Information','1-555-1212');&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cSQL(3) = &quot;INSERT INTO VPHONE VALUES('SAMS 
  Publishing','1-800-428-5331');&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set ws = DBEngine.Workspaces(0)<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set db = CreateDatabase(App.Path &amp; &quot;\VPHONE.MDB&quot;, 
  dbLangGeneral)<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;For x = 1 To 3<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db.Execute cSQL(x), dbFailOnError<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Next x<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;db.Close<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set db = Nothing<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set ws = Nothing<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  LocalErr:<br>
  &nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description &amp; Chr(13) &amp; Err.Source, vbCritical, 
  &quot;BuildDatabase&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  End Sub</font></tt> </p>
</blockquote>

<hr>

<p>The code here is quite handy. First, the database file is created. Then, three SQL 
statements are executed. The first one creates the new <tt><font FACE="Courier">VPHONE</font></tt> 
table. The second two statements add two records to the new table. </p>
<div align="center"><center>

<table BORDERCOLOR="#000000" BORDER="1" WIDTH="80%">
  <tr>
    <td><b>Tip</b></td>
  </tr>
  <tr>
    <td><blockquote>
      <p>This is a great technique for building databases upon installation of a new 
      application. This way, users don't have to worry about copying data files, confusing older 
      versions of the data, and so on. Even better, if you need to start the database from 
      scratch, all you need to do is remove the database file and start the program-it will 
      create the initial database for you!</p>
    </blockquote>
    </td>
  </tr>
</table>
</center></div>

<p>After the <tt><font FACE="Courier">BuildDatabase</font></tt> routine has been added, 
you need to add the code that will open the existing database and select the phone 
records. Create the <tt><font FACE="Courier">OpenDatabase</font></tt> subroutine and enter 
the code from Listing 35.15. </p>

<hr>

<blockquote>
  <b><p>Listing 35.15. Adding the <tt><font FACE="Courier">OpenDatabase</font></tt> routine.<br>
  </b></p>
</blockquote>

<blockquote>
  <tt><font FACE="Courier"><p>Public Sub OpenDatabase()<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;' open phone database<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;On Error GoTo LocalErr<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Dim cSelect As String<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;cSelect = &quot;SELECT * FROM VPHONE&quot; <br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set wsPhone = DBEngine.Workspaces(0)<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set dbPhone = wsPhone.OpenDatabase(App.Path &amp; 
  &quot;\VPHONE.MDB&quot;)<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Set rsPhone = dbPhone.OpenRecordset(cSelect, dbOpenDynaset)<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  LocalErr:<br>
  &nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description &amp; Chr(13) &amp; Err.Source, vbCritical, 
  &quot;OpenDatabase&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  End Sub</font></tt> </p>
</blockquote>

<hr>

<p>Nothing real fancy here. The database is opened and a single SQL <tt><font
FACE="Courier">SELECT</font></tt> statement is executed to create a Dynaset-type recordset 
for use in the program. </p>

<p>There are four remaining database service support routines: 

<ul>
  <li><tt><font FACE="Courier">AddRec</font></tt> </li>
  <li><tt><font FACE="Courier">EditRec</font></tt> </li>
  <li><tt><font FACE="Courier">DeleteRec</font></tt> </li>
  <li><tt><font FACE="Courier">LookUp</font></tt> </li>
</ul>

<p>The <tt><font FACE="Courier">AddRec</font></tt> and <tt><font FACE="Courier">EditRec</font></tt> 
routines use a secondary dialog form (<tt><font FACE="Courier">frmVRec</font></tt>), which 
you'll build in the next section of this chapter. </p>

<p>Create a new subroutine called <tt><font FACE="Courier">AddRec</font></tt> and enter 
the code from Listing 35.16. </p>

<hr>

<blockquote>
  <b><p>Listing 35.16. Adding the <tt><font FACE="Courier">AddRec</font></tt> routine.<br>
  </b></p>
</blockquote>

<blockquote>
  <tt><font FACE="Courier"><p>Public Sub AddRec()<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;' add a new record<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Load frmVRec<br>
  &nbsp;&nbsp;&nbsp;&nbsp;frmVRec.txtName = &quot;&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;frmVRec.txtPhone = &quot;&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;frmVRec.lblaction = &quot;ADD&quot;<br>
  &nbsp;&nbsp;&nbsp;&nbsp;frmVRec.Show vbModal<br>
  &nbsp;&nbsp;&nbsp;&nbsp;'<br>
  End Sub</font></tt> </p>
</blockquote>

<hr>

<p>Now add the <tt><font FACE="Courier">EditRec</font></tt> subroutine and enter the code