Hi Everyone,
I'm very new to access/vba, but I'm trying to utilise some code I've come across to email details in a form to an employee. I've taken the original code and tried to modify it for my own use (without much sucess).
When running the code below I get the error 'Data type mismatch in criteria expression'. I've done as much as I can to try and resolve the issue but my lack of experience is showing!
The database contains two tables tblpersonnel and tbloverpaments, both joined one to one with a primary key in the form of employeeID (both integers)
Any help to review the code would be appreciated.
Thanks
David
PS as I'm a new poster it doesn't seem to allow me to post the word e m a i l, so I've had to change each instance to e*ail.
Private Sub CmdNotify_Click()
On Error GoTo Err_cmdNotify_Click
Dim StWhere As String 'Criteria for DLookup
Dim Varto As Variant 'Address for SendObject
Dim stText As String 'E*ail Text
Dim stSubject As String 'Subject line of e*ail
Dim stWho As String 'Reference to tblPersonnel
Dim strSQL As String 'Create SQL statement to update
Dim errLoop As Error
'-- Combo of Employee Names
stWho = Me.cboEmpID
StWhere = "tblPersonnel.SAPID = " & "'" & stWho & "'"
'Looks up e*ail address from table
Varto = DLookup("[E*ail]", "tblPersonnel", StWhere)
stSubject = "::Test::"
stText = "Test"
'Write the e*ail contect for sending to employee
DoCmd.SendObject , , acFormatTXT, Varto, , , stSubject, stText, -1
On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0
Exit Sub
Err_Execute:
'Notify user of any errors that result from
'executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume Next
Exit_cmdNotify_Click:
Exit Sub
Err_cmdNotify_Click:
MsgBox Err.Description
Resume Exit_cmdNotify_Click
End Sub
I'm very new to access/vba, but I'm trying to utilise some code I've come across to email details in a form to an employee. I've taken the original code and tried to modify it for my own use (without much sucess).
When running the code below I get the error 'Data type mismatch in criteria expression'. I've done as much as I can to try and resolve the issue but my lack of experience is showing!
The database contains two tables tblpersonnel and tbloverpaments, both joined one to one with a primary key in the form of employeeID (both integers)
Any help to review the code would be appreciated.
Thanks
David
PS as I'm a new poster it doesn't seem to allow me to post the word e m a i l, so I've had to change each instance to e*ail.
Private Sub CmdNotify_Click()
On Error GoTo Err_cmdNotify_Click
Dim StWhere As String 'Criteria for DLookup
Dim Varto As Variant 'Address for SendObject
Dim stText As String 'E*ail Text
Dim stSubject As String 'Subject line of e*ail
Dim stWho As String 'Reference to tblPersonnel
Dim strSQL As String 'Create SQL statement to update
Dim errLoop As Error
'-- Combo of Employee Names
stWho = Me.cboEmpID
StWhere = "tblPersonnel.SAPID = " & "'" & stWho & "'"
'Looks up e*ail address from table
Varto = DLookup("[E*ail]", "tblPersonnel", StWhere)
stSubject = "::Test::"
stText = "Test"
'Write the e*ail contect for sending to employee
DoCmd.SendObject , , acFormatTXT, Varto, , , stSubject, stText, -1
On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0
Exit Sub
Err_Execute:
'Notify user of any errors that result from
'executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume Next
Exit_cmdNotify_Click:
Exit Sub
Err_cmdNotify_Click:
MsgBox Err.Description
Resume Exit_cmdNotify_Click
End Sub
