VBA code help needed.

[DomFino]

Hi everyone,
I have an Access 2003 database that provided a button on a form that sends email to a list of recipients. This works okay. The next step is to have the code update a YES/NO filed in a table after the email is sent. This is what does not work.

In the code below. I receive the following error on the BLUE lines of code:
Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

Private Sub cmdEmail_Click()
'The following code can be used behind a command button in the On_Click event.  It will
'take the information from the form fields and insert the information into a email to be
'sent by Outlook.
'
'******begin code******
Dim Email As String
Dim ref As String
Dim origin As String
Dim destination As String
Dim notes As String

'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from your form.  this sets the string variable to your fields
Email = "Dominic Fino"
ref = "Sourcer Updated With:" & " " & Me![code] & " Job Title " & " " & Me![desc]
origin = "NEW NEED FROM:" & " " & Me![o_company]
notes = Me![code] & " " & Me!desc

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email

With objEmail
    .To = Email
    .Subject = origin
    .Body = notes
    .Display 'Send 'sends the email in Outlook.  Change to DISPLAY if you want to be able to
             'modify or see what you have created before sending the email
End With

'**closes outlook
'objOutlook.Quit
'Set objEmail = Nothing
[COLOR=blue]CurrentProject.Connection.Execute "UPDATE tblVacancyMaster SET " & _
"tblVacancyMaster.EmailSent = vbYes;"[/color]

Exit Sub
'****end code****
On Error GoTo Err_cmdEmail_Click


    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_cmdEmail_Click:
    Exit Sub

Err_cmdEmail_Click:
    MsgBox Err.Description
    Resume Exit_cmdEmail_Click
    
End Sub

 

[PHV]

CurrentProject.Connection.Execute "UPDATE tblVacancyMaster SET EmailSent=True"

I wonder you don't have any WHERE clause ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PHV,
Thanks for the reply.
You are on to something. When I put the code change in it updated all the records EmailSet fields.

I then tried this code with a WHERE but it also caused the same error I received before.

CurrentProject.Connection.Execute "UPDATE tblVacancyMaster SET EmailSent=True WHERE tblVacnacyMaster.SourcerCode = Me![code]"

Thanks for your help,
Dom

 

[PHV]

CurrentProject.Connection.Execute "UPDATE tblVacancyMaster SET EmailSent=True WHERE SourcerCode=[tt]'"[/tt] & Me!

 & [tt]"'"[/tt]

If SourcerCode is defined as numeric in tblVacancyMaster then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PH,
That did it! Thanks so much for your help!
Dom

The working code is as follows:

CurrentProject.Connection.Execute "UPDATE tblVacancyMaster SET EmailSent=True WHERE SourcerCode='" & Me![code] & "'"