Access.2007.VBA.Bibl..

The next source object is qryContactIDsPhones(see Figure 11.10). It has only two fields, so

to match the many phone and ID fields in the target table I created a query with many calculated

fields, one for each phone or ID field in tblAccessContacts.

FIGURE 11.10

A calculated field that converts a phone number in tblContactIDsPhones into a value to be written to

tblAccessContacts.

Each calculated field returns a value for a phone number or ID matching one of the standard

Outlook Phone and ID selections; a portion of the code that works with this query is listed below:

Set rstSource = dbs.OpenRecordset(strQueryContactIDs, _

dbOpenDynaset)

Do While Not rstSource.EOF

Search for target record and update Contact ID and phone fields:

strTargetCustomerID = rstSource![CustomerID]

strSearch = “[CustomerID] = “ & Chr$(39) _

& strTargetCustomerID & Chr$(39)

Uncomment the following line to inspect the search string in the Immediate window.

‘Debug.Print “Search string: “ & strSearch

rstTarget.FindFirst strSearch

If rstTarget.NoMatch = False Then

GoTo NextSourceRecord1

End If

rstTarget.Edit

rstTarget![AssistantTelephoneNumber] = _

Nz(rstSource!AssistantTelephoneNumber)

Part II Writing VBA Code to Exchange Data between Office Components