Access.2007.VBA.Bibl..

& “; Description: “ & Err.Description

Resume ErrorHandlerExit

End If

End Sub

The UpdateAllAccessContactsprocedure has the more complex task of copying updated con-

tact data from tblAccessContacts back to the linked contact tables. This procedure does the reverse of

the CreateDenormalizedContactsTableprocedure; using tblAccessContacts as a data source,

it updates the linked contact data in tblCompanyInfo, tblContactInfo, tblCompanyIDsPhones,

tblContactAddresses, and tblContactIDsAndPhones, creating new records as needed:

Public Sub UpdateAllAccessContacts()

‘Called from cmdUpdateContactInfo_Click() on fmnuMain

On Error GoTo ErrorHandler

Dim lngContactID As Long

Dim lngCompanyID As Long

Dim strSourceTable As String

Dim strTarget As String

Dim strAddressType As String

Dim strDescription As String

Set dbs = CurrentDb

strSourceTable = “tblAccessContacts”

Set rstSource = dbs.OpenRecordset(strSourceTable, _

dbOpenDynaset)

UpdateCompanyInfo:

Do While Not rstSource.EOF

Debug.Print “Processing Target ID: “ _

& rstSource![TargetID]

Search for matching Company record in target table, and update it if found; otherwise, create new

company record, and write company data to it.

strTarget = “tblCompanyInfo”

Set rstTarget = dbs.OpenRecordset(strTarget, _

dbOpenDynaset)

blnDelete = rstSource![Delete]

If blnDelete = True Then

To avoid problems with deleting records in a table on the “one” side of a one-to-many relationship,

before updating the tables, the procedure runs three delete queries to delete records linked to con-

tacts marked for deletion:

Part II Writing VBA Code to Exchange Data between Office Components