1. Before starting, make a copy of your phone database file using the Windows
Explorer. That way, in case we mess up, you still have a good copy. And, create
a data link to the database. Here, we develop a simple DBMS for our phone number
database. We will be able to display individual records and edit them. And, we
will be able to add or delete records. Note this is a simple system and many of
the fancy ‘bells and whistles’ (for example, asking if you really
want to delete a record) that should really be here are not. Adding such amenities
is left as an exercise to the student.
2. Load your last Books Database application (Example 8-2
- the one with the ‘Rolodex’ search). We will modify this application
to fit the phone number DBMS. Resave your form and project with different names.
Add three command buttons to the upper right corner of the form. Modify/set the
following properties for each tool. For the data control and text boxes, make
sure you follow the order shown.
*frmBooks (this is the old name):
Caption - Phone List
Name - frmPhone
*dtaTitles (this is the old name):
Caption - Phone Numbers
ConnectionString - [your phone database data link] (select, don’t type)
RecordSource - SELECT * FROM PhoneList ORDER BY Name (the ORDER keyword sorts
the database by the given field)
Name - dtaPhone
LockType - adLockOptimistic
*Label1:
Caption - Description
*Label2:
Caption - Phone
*Label3:
Caption - Name
*txtAuthor (this is the old name):
DataSource - dtaPhone (select, don’t type)
DataField - Name (select, don’t type)
Locked - False
Name - txtName
MaxLength - 40
TabIndex - 1
*txtISBN (this is the old name):
DataSource - dtaPhone (select, don’t type)
DataField - Phone (select, don’t type)
Locked - False
Name - txtPhone
MaxLength - 15
TabIndex - 3
*txtTitle (this is the old name):
DataSource - dtaPhone (select, don’t type)
DataField - Description (select, don’t type)
Locked - False
Name - txtDesc
MaxLength - 40
TabIndex - 2
*Command1:
Caption - &Add
Name - cmdAdd
*Command2:
Caption - &Save
Enabled - False
Name - cmdSave
*Command3:
Caption - &Delete
Name - cmdDelete
When done, my form looked like this:
At this point, you can run your application and you should be able to navigate
through your phone database using the data control. Don’t try any other
options, though. We need to do some coding.
3. In Form_Load, replace the word frmBooks
with frmPhone. This will allow the letter keys to be displayed
properly.
4. In the cmdLetter_Click procedure, replace
all occurrences of the word dtaTitles with dtaPhone.
Replace all occurrences of Author with Name. The modified code will be:
Private Sub cmdLetter_Click(Index
As Integer)
Dim BookMark1 As Variant
'Mark your place in case no match is found
BookMark1 = dtaPhone.Recordset.Bookmark
dtaPhone.Recordset.MoveFirst
dtaPhone.Recordset.Find "Name >= '" + cmdLetter(Index).Caption +
"'"
If dtaPhone.Recordset.EOF = True Then
dtaPhone.Recordset.Bookmark = BookMark1
End If
txtName.SetFocus
End Sub
5. Attach this code to the cmdAdd_Click procedure.
This code invokes the code needed to add a record to the database. The Add
and Delete buttons are disabled. Click the Save
button when done adding a new record.
Private Sub cmdAdd_Click()
cmdAdd.Enabled = False
cmdSave.Enabled = True
cmdDelete.Enabled = False
dtaPhone.Recordset.AddNew
txtName.SetFocus
End Sub
6. Add this code to the cmdSave_Click procedure.
When done entering a new record, the command button status’s are toggled,
the Recordset updated, and the data control Refresh method invoked
to insure proper record sorting.
Private Sub cmdSave_Click()
dtaPhone.Recordset.Update
dtaPhone.Refresh
cmdAdd.Enabled = True
cmdSave.Enabled = False
cmdDelete.Enabled = True
txtName.SetFocus
End Sub
7. Attach this code to the cmdDelete_Click
procedure. This deletes the current record and moves to the next record. If we
bump into the end of file, we need to check if there are no records remaining.
If no records remain in the table, we display a message box. If records remain,
we move around to the first record.
Private Sub cmdDelete_Click()
dtaPhone.Recordset.Delete
dtaPhone.Recordset.MoveNext
If dtaPhone.Recordset.EOF = True Then
dtaPhone.Refresh
If dtaPhone.Recordset.BOF = True Then
MsgBox "You must add a record.", vbOKOnly + vbInformation, "Empty
file"
Call cmdAdd_Click
Else
dtaPhone.Recordset.MoveFirst
End If
End If
txtName.SetFocus
End Sub
8. Save the application. Try running it. Add
records, delete records, edit records. If you’re
really adventurous, you could add a button that dials your phone (via modem) for
you! Look at the custom communications control.
