Listing 8.1[em]The Address Book Application
' Since all fields are in a control array
' I will setup defines for accessing them
Const NAME_FLD = 1
Const STREET_FLD = 2
Const STREET2_FLD = 3
Const CITY_FLD = 4
Const STATE_FLD = 5
Const ZIP_FLD = 6
Const PHONE_FLD = 7
Const WORK_FLD = 8
Const FAX_FLD = 9
Const NOTES_FLD = 10
Const FIRST_FLD = NAME_FLD
Const LAST_FLD = NOTES_FLD

' Objects and variables used in this module
Dim Ws As Workspace
Dim Db As Database
Dim Tbl As Recordset
Dim SearchSet As Recordset
Dim MemoSet As Recordset
Dim AddInfoAdd As Recordset
Dim SrchValue As String

Sub Add_Click ()
    If Not (AddressCtl(NAME_FLD) = "") Then
Tbl.AddNew
     Tbl.Fields("Name").Value = AddressCtl(NAME_FLD)
     Tbl.Fields("Street").Value = AddressCtl(STREET_FLD)
     Tbl.Fields("Street2").Value = AddressCtl(STREET2_FLD)
     Tbl.Fields("City").Value = AddressCtl(CITY_FLD)
     Tbl.Fields("State").Value = AddressCtl(STATE_FLD)
     Tbl.Fields("ZipCode").Value = AddressCtl(ZIP_FLD)
     Tbl.Fields("Phone").Value = AddressCtl(PHONE_FLD)
     Tbl.Fields("WorkPhone").Value = AddressCtl(WORK_FLD)
     Tbl.Fields("Fax").Value = AddressCtl(FAX_FLD)
     Tbl.Update
     'Tbl.Fields("Notes").Value = AddressCtl(NOTES_FLD)
     'Tbl.Update

     AddInfoAdd.AddNew
     AddInfoAdd.Fields("Name").Value = AddressCtl(NAME_FLD)
     AddInfoAdd.Fields("Notes").Value = AddressCtl(NOTES_FLD)
     AddInfoAdd.Update
    End If
End Sub

Sub Command1_Click ()
    Dim i As Integer
    For i = FIRST_FLD To LAST_FLD Step 1
     AddressCtl(i) = ""
    Next i
End Sub

Sub Delete_Click ()
    If Not SearchSet.NoMatch Then
       SearchSet.Delete
       If Not MemoSet.NoMatch Then
     MemoSet.Delete
       End If
       Command1_Click
       FindFirst_Click
    End If
End Sub

Sub FillFormfromImport ()
    If Not SearchSet.NoMatch Then
     AddressCtl(NAME_FLD) = ValidateRecordField("Name")
     AddressCtl(STREET_FLD) = ValidateRecordField("Street")
     AddressCtl(STREET2_FLD) = ValidateRecordField("Street2")
     AddressCtl(CITY_FLD) = ValidateRecordField("City")
     AddressCtl(STATE_FLD) = ValidateRecordField("State")
     AddressCtl(ZIP_FLD) = ValidateRecordField("ZipCode")
     AddressCtl(PHONE_FLD) = ValidateRecordField("Phone")
     AddressCtl(WORK_FLD) = ValidateRecordField("WorkPhone")
     AddressCtl(FAX_FLD) = ValidateRecordField("Fax")
     AddressCtl(NOTES_FLD) = ValidateRecordField("Notes")
    End If

End Sub

Sub FillFormfromRecord ()
    If Not SearchSet.NoMatch Then
     AddressCtl(NAME_FLD) = ValidateRecordField("Name")
     AddressCtl(STREET_FLD) = ValidateRecordField("Street")
     AddressCtl(STREET2_FLD) = ValidateRecordField("Street2")
     AddressCtl(CITY_FLD) = ValidateRecordField("City")
     AddressCtl(STATE_FLD) = ValidateRecordField("State")
     AddressCtl(ZIP_FLD) = ValidateRecordField("ZipCode")
     AddressCtl(PHONE_FLD) = ValidateRecordField("Phone")
     AddressCtl(WORK_FLD) = ValidateRecordField("WorkPhone")
     AddressCtl(FAX_FLD) = ValidateRecordField("Fax")

     Dim AttachStatement As String
     AttachStatement = "SELECT * FROM Addinfo WHERE Name = '" +
      AddressCtl(NAME_FLD) + "'"
     Set MemoSet = Db.OpenRecordset(AttachStatement, dbOpenDynaset)
     If MemoSet.BOF = False Then
         If MemoSet.Fields("Notes").Value > "" Then
          AddressCtl(NOTES_FLD) = MemoSet.Fields("Notes").Value
         Else
          AddressCtl(NOTES_FLD) = ""
         End If
     End If
    End If
End Sub

Sub FindFirst_Click ()
    Dim Statement As String
    Statement = "SELECT * FROM Address WHERE Name >= '" +
     AddressCtl(NAME_FLD) + "'"
    Set SearchSet = Db.OpenRecordset(Statement, dbOpenDynaset)
    FillFormfromRecord
End Sub

Sub FindNext_Click ()
    SearchSet.FindNext "Name > ' '"
    FillFormfromRecord
End Sub

Sub FindPrevious_Click ()
    SearchSet.FindPrevious "Name > ' '"
    FillFormfromRecord
End Sub

Sub Form_Load ()
    Dim TblDef As New TableDef
    ' first create the default workspace
     Set Ws = DBEngine.Workspaces(0)
    ' second open the access database
    Set Db = Ws.OpenDatabase("c:\vb\address.mdb")

    ' now attach the dbase IV table to the open db
    TblDef.Connect = "dBASE IV;DATABASE=C:\VB\ADDINFO"
    TblDef.SourceTableName = "ADDINFO"   ' The name of the file.
    TblDef.Name = "Addinfo"    ' The name in your database.
    Db.TableDefs.Append TblDef  ' Create the link.

    ' now open a table info the main database
    Set Tbl = Db.OpenRecordset("Address", dbOpenTable)

    ' now we need to create a dynaset from the attached table
    Statement = "SELECT * FROM Address WHERE Name >= '" +
     AddressCtl(NAME_FLD) + "'"
Set AddInfoAdd = Db.OpenRecordset("SELECT * FROM Addinfo",
     dbOpenDynaset)
End Sub

Sub Form_Unload (Cancel As Integer)
    Tbl.Close
    AddInfoAdd.Close
    Db.TableDefs.Delete "Addinfo"
End Sub

Sub ImportDbase_Click ()
    Dim dbdb As Database
    Dim DTable As RecordSet
    Dim Statement As String

    Set dbdb = Ws.OpenDatabase("c:\vb", False, False, "dBase IV")
    Set DTable = dbdb.OpenRecordSet("Address", dbOpenTable)

    Statement = "SELECT * FROM Address"
    Set SearchSet = dbdb.OpenRecordset(Statement, dbOpenRecordset)
    While Not SearchSet.EOF
     FillFormfromImport
     Add_Click
     Command1_Click
     SearchSet.MoveNext
    Wend
End Sub

Function ValidateRecordField (Field As String) As String
    If SearchSet.BOF = False Then
     If SearchSet.Fields(Field).Value > "" Then
         ValidateRecordField = SearchSet.Fields(Field).Value
     Else
         ValidateRecordField = ""
     End If
    End If
End Function