VBA Error No value given for one or more required parameters

[mickey66]

I'm getting an error in Access 2003 (Error: -2147217904: No value given for one or more required parameters)

I'm trying to get information from the access database and send it to Outlook 2003. But this error keeps coming up and I tried the stepping into. and Once it gets to the rst.Open line it skips down to the MsgBox line and then I get the error.

Function TransferContacts()
'Transfer contact records from Contacts to Outlook.
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim appOutlook As New Outlook.Application
Dim ns As Outlook.Namespace
Dim fldContacts As Outlook.MAPIFolder
Dim itms As Outlook.Items
Dim objContactFolder As Object

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
Set appOutlook = CreateObject("Outlook.Application")
Set ns = appOutlook.GetNamespace("MAPI")
Set fldContacts = ns.GetDefaultFolder(olFolderContacts)
Set itms = fldContacts.Items

'Prevent duplicate contacts in Outlook.
rst.Open "SELECT * FROM Lead_Generation_Contacts WHERE Transfered = 0", cnn, adOpenKeyset, adLockOptimistic

'Prevent error when recordset is empty, meaning there are no
'new contact records to transfer.
If rst.RecordCount = 0 Then
MsgBox "There are no new contact records to transfer", vbOKOnly, "Transfer stopped"
Exit Function
End If

rst.MoveFirst

Do While Not rst.EOF
Set objContactFolder = itms.Add("IPM.Contact")
With objContactFolder
.CustomerID = Nz(rst!ContactNo)
.FullName = Nz(rst!ContactOneFirstName & " " & ContactOneLastName)
.AssistantsName = Nz(rst!ContactTwoFirstName & " " & ContactTwoLastName)
.Notes = Nz(rst!Notes)
.HomePhone = Nz(rst!HomePhone)
.MobilePhone = Nz(rst!CellPhone)
.E -mail = Nz(rst!Email)
.HomeAddress = Nz(rst!Address)
.Managersname = Nz(rst!LenderAssignedTo)
.Created = Nz(rst!InitialDateProspected)
.Close olSave
End With

Set objContactFolder = Nothing
rst.Update "Transferred", -1
rst.MoveNext
Loop

Set cnn = Nothing
Set appOutlook = Nothing
Set ns = Nothing
Set fldContacts = Nothing
Set itms = Nothing
Exit Function
ErrHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Function


What am I doing wrong? I've been going crazy with this code for about a week now.

P.S. Also with the "Notes" field is there a way to append onto that field instead of writing over the code?

 

[mearle]

You've spelt Transfered wrong ?
It should be Transferred - or at least it is further down in the code.

 

[mickey66]

Received new error

Thank you, it is spelled "transferred" in the database field. but now I get another error: 438: Object doesn't support this property or method.

 

[mearle]

itms should be items ?

 

[mickey66]

I just re-stepped through the program and now it's giving the error at

.AssistantsName = Nz(rst!ContactTwoFirstName & " " & ContactTwoLastName)

The AssistantsName is from Outlook 2003 it's one of the fields in the form but the name is Assistant's Name. But I can't get it to work with the " ' " in the name of the field.

 

[georgedwilkinson]

Wouldn't

.AssistantsName = Trim(Nz(rst!ContactTwoFirstName,"") & " " & Nz(rst!ContactTwoLastName,""))

be more appropriate?

In your example, you don't dereference "ContactTwoLastName" and you're using Nz on a calculated value that will never be null. The Trim() will get rid of the " " if either name is null.

Let us know.

 

[mickey66]

I'm trying to just concatinate the two fields, not calculate anything.

But let me try your code and see what happens, thanks.

Will let you know.......**Crosses fingers**

 

[mickey66]

I just put in your code instead of mine, George. I received the same error message - 438: Object doesn't support this property or method. **Frowns**

That line is just to put the first name and last name in Outlook's Contact field named Assistant's Name. With a space in between the first and last name. ~If that helps~

 

[georgedwilkinson]

Simplify and single step. Make .AssistantsName = "" and see if it works, then make it more complex (.AssistantsName = rst!ContactTwoFirstName) and see if it works, then add Nz, see if it works, then add & " " and see if it works, etc. etc. Keep making incremental changes until you get an error, then post back with that if you can't figure it out. Make baby steps.

 

[mickey66]

Thanks George, but the first part didn't even work. .AssistantsName = "" ~~Same error

 

[georgedwilkinson]

That should tell you that .AssistantsName is not a member of whatever object you're working "with" or that it cannot accept a text value. I think that's the first thing you need to correct, don't you?

 

[mickey66]

How do I find out what object it belongs with? With is for Outlook 2003, I found where the .Notes are they are olFolderNotes, I can't find where .AssistantsName. It's doing the same thing to

 

[mickey66]

Neverminde, the name is .AssistantName (Without the s) does belong to the ContactsFolder.

Thanks for your help, George.

Is there anyway to add onto the Notes instead of overwriting them?

 

[georgedwilkinson]

Good!

You can do something like this:

.Notes = .Notes & vbcrlf & strMyNewStuffIWantToAddToTheNotes

If that doesn't work, try:

Dim strTempNotes as String
strTempNotes = .Notes
.Notes = strTempNotes & vbcrlf & strMyNewStuffIWantToAddToTheNotes

HTH.

Oh, and thank you for clearly stating your problem. Many people don't do that then get mad when you answer wrong.

 

[mickey66]

Thank you for the quick replies and all the help you've giving me. A lot of people here I noticed are newbies and don't know how to clearly state their problems.

I got the VBA script that I wrote to work completely well. Now I have to try to figure out how to get rid of the extra row ("Transferred") in my code and add some error handling. Because once it goes through it puts a check mark in the box under the field "Transferred" and then it skips that whole contact information, so if any new information (i.e. Notes, change of phone numbers, etc...,) is put into the database then it won't get updated. Does that make sense?

Here's the code I have now:

Function TransferContacts()
'Transfer contact records from Contacts to Outlook.
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim appOutlook As New Outlook.Application
Dim ns As Outlook.Namespace
Dim fldContacts As Outlook.MAPIFolder
Dim itms As Outlook.Items
Dim objContactFolder As Object

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
Set appOutlook = CreateObject("Outlook.Application")
Set ns = appOutlook.GetNamespace("MAPI")
Set fldContacts = ns.GetDefaultFolder(olFolderContacts)
Set itms = fldContacts.Items

'Prevent duplicate contacts in Outlook.
rst.Open "SELECT * FROM Lead_Generation_Contacts WHERE Transferred = 0", cnn, adOpenKeyset, adLockOptimistic

'Prevent error when recordset is empty, meaning there are no
'new contact records to transfer.
If rst.RecordCount = 0 Then
MsgBox "There are no new contact records to transfer", vbOKOnly, "Transfer stopped"
Exit Function
End If

rst.MoveFirst

Do While Not rst.EOF
Set objContactFolder = itms.Add("IPM.Contact")
With objContactFolder
.CustomerID = Nz(rst!ContactNo)
.FullName = Trim(Nz(rst!ContactOneFirstName, "") & " " & Nz(rst!ContactOneLastName, ""))
.AssistantName = Trim(Nz(rst!ContactTwoFirstName, "") & " " & Nz(rst!ContactTwoLastname, ""))
.HomeTelephoneNumber = Nz(rst!HomePhone)
.MobileTelephoneNumber = Nz(rst!CellPhone)
.Body = Nz(rst!Notes)
.Email1Address = Nz(rst!Email)
.HomeAddress = Trim(Nz(rst!Address, "") & " " & Nz(rst!City, "") & ", " & Nz(rst!State, "") & " " & Nz(rst!Zip, ""))
.ManagerName = Nz(rst!LenderAssignedTo)
' .Created = Nz(rst!InitialDateProspected)
.Close olSave
End With

Set objContactFolder = Nothing
rst.Update "transferred", -1
rst.MoveNext
Loop

Set cnn = Nothing
Set appOutlook = Nothing
Set ns = Nothing
Set fldContacts = Nothing
Set itms = Nothing
Exit Function
ErrHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Function

Thanks,
Mickey