Error: Item not found in this collection

[nharrison]

I'm attempting to use code written by the Microsoft Access Team that maps fields from a custom Contacts table to the fields used by Microsoft Outlook, to facilitate use of the AddFromOutlook command that comes with Access 2007.

I have all of the code in a module, and then I call the sub MakeContacts() from a button in a blank form. It all runs right up until the last section.

The code is here:

Option Compare Database

' -----------------------------------------------
' READ THIS
' Make edits to the string values in the
' MakeContacts Sub below to match up
' with your table and field names.
'
' You shouldn't need to change the rest of
' this code.
' -----------------------------------------------

' CI is for ContactInfo
Enum CI
First
Last
Email
Company
JobTitle
WorkPhone
HomePhone
CellPhone
WorkFax
WorkAddress
WorkCity
WorkState
WorkZip
WorkCountry
WebPage
Comments
End Enum

' Each field map item maps between a field
' name in the Access table and a property
' name Access maps to Outlook and SharePoint
' contact field info.
Type FieldMap
prop As String
Field As String
End Type

' This Varible lists all the proeprties that
' can be mapped to Contact info. You fill
' it with the corresponding field names from
' your table.
Dim Map(CI.Comments) As FieldMap

Public Sub MakeContacts()

Dim strTable As String
Dim fm As FieldMap
Dim td As TableDef
Dim db As Database
Dim i As Integer

' -----------------------------------------------
' UPDATE THESE STRINGS
' to match the table and field names in
' your app. It is okay not to set some if you
' don't have an equivalent.

' Set this to your table name
strTable = "Contacts"

' Set these to your field names
Map(CI.First).Field = "First Name"
Map(CI.Last).Field = "Last Name"
Map(CI.Email).Field = "Email"
Map(CI.Company).Field = "Company_temp"
Map(CI.JobTitle).Field = "Job Title"
Map(CI.WorkPhone).Field = "Business Phone"
Map(CI.HomePhone).Field = "Home Phone"
Map(CI.CellPhone).Field = "Cell Phone"
Map(CI.WorkFax).Field = "Fax Number"
Map(CI.WorkAddress).Field = "Address 1_temp"
Map(CI.WorkCity).Field = "City_temp"
Map(CI.WorkState).Field = "State_temp"
Map(CI.WorkZip).Field = "ZIP_temp"
Map(CI.WorkCountry).Field = "Country"
Map(CI.WebPage).Field = "Web Page_temp"
Map(CI.Comments).Field = "Notes"

' END OF STRINGS TO UPDATE
' -----------------------------------------------

' This the code to mark fields in
' your local table with the correct
' Outlook and SharePoint field names.
'
' You shouldn't need to change this.
SetupContactProps
Set db = CurrentDb
Set td = db.TableDefs(strTable)

' Set the table level property that tells Access
' this is a Contact table.
SetProp td, "WSSTemplateID", dbInteger, 105

' For each mapped field, set the correct
' contacts property.
For i = 0 To CI.Comments
fm = Map(i)
If Len(fm.Field) > 0 Then
SetProp td.Fields(fm.Field), "WSSFieldID", dbText, fm.prop
End If
Next

End Sub

' This code initializes the contact property
' names that Access uses to map contact info
' to SharePoint or Outlook.
'
' You shouldn't need to change this.
Sub SetupContactProps()

Map(CI.First).prop = "FirstName"
Map(CI.Last).prop = "Title"
Map(CI.Email).prop = "Email"
Map(CI.Company).prop = "Company"
Map(CI.JobTitle).prop = "JobTitle"
Map(CI.WorkPhone).prop = "WorkPhone"
Map(CI.HomePhone).prop = "HomePhone"
Map(CI.CellPhone).prop = "CellPhone"
Map(CI.WorkFax).prop = "WorkFax"
Map(CI.WorkAddress).prop = "WorkAddress"
Map(CI.WorkCity).prop = "WorkCity"
Map(CI.WorkState).prop = "WorkState"
Map(CI.WorkZip).prop = "WorkZip"
Map(CI.WorkCountry).prop = "WorkCountry"
Map(CI.WebPage).prop = "WebPage"
Map(CI.Comments).prop = "Comments"

End Sub

' This is a helper routine which sets a property
' value first checking to see whether one already
' exists.
Sub SetProp(o As Object, strProp As String, dbType As DataTypeEnum, oValue As Variant)

Dim p As DAO.Property

On Error GoTo NotFound
Set p = o.Properties(strProp)
GoTo Found

NotFound:
Set p = CurrentDb.CreateProperty(strProp, dbType, oValue)
o.Properties.Append p

Found:
If p.Type = dbType Then
p.Value = oValue
Else
o.Properties.Delete (strProp)
Set p = CurrentDb.CreateProperty(strProp, dbType, oValue)
End If

End Sub

The line where I am having issues is at the bottom of the MakeContacts() sub:

SetProp td.Fields(fm.Field), "WSSFieldID", dbText, fm.prop

and I get the Run-time error '3265': Item not found in this collection.



Any ideas?

 

[RuralGuy]

Did you modify the following line:
' Set this to your table name
strTable = "Contacts"
...to match your table, or do you have a Contacts table?

 

[nharrison]

I did, the table is called "Contacts". Haha that was a DIFFERENT set of errors, which i finally solved and kicked myself for lol

 

[RuralGuy]

I have been unable to locate the DataTypeEnum function. Is that an intrinsic VBA datatype?

 

[nharrison]

Yes it is.

The line you're referring to (I believe) is

Sub SetProp(o As Object, strProp As String, dbType As DataTypeEnum, oValue As Variant)

What I believe happens is that in the MakeContacts() sub, the line

SetProp td.Fields(fm.Field), "WSSFieldID", dbText, fm.prop

passes in dbText (which is a data type) into the sub SetProp(), which then enumerates dbText using the built-in function DataTypeEnum, which, if you're familiar with enumeration, means that it returns a specific numeric value. In the case of dbText, the value is 10.

You can read about it at

http://msdn.microsoft.com/en-us/library/bb242635.aspx

 

[nharrison]

so....does nobody know what my issue is? :/

 

[RuralGuy]

I installed the code on one of my test beds and do not get the error. Any chance you are dealing with some corruption? Try importing your db into another blank db and see if the problem follows the import.

 

[nharrison]

I created a new database, exported just the contacts table and the code and no luck. I really wish this would work, the AddFromOutlook is a such a cool feature

 

[RuralGuy]

Did you Export OR Import? You should be Importing while in the new db. Can you post your db so we can look at it?

 

[boblarson]

Okay, quick question

you have

SetProp td.Fields(fm.Field),

what is the value of fm.Field? It needs to be either a string or a number value for td.Fields. Normally you would pass td.Fields("FieldName") or td.Fields(1) etc. Perhaps fm.Fields is passing a number higher than would be in td.fields. So, you are getting that error because the item is not in that collection. Step through the code after setting a breakpoint and check the value of fm.Field at that point and watch to see as you go through until you get the error.