Results 1 to 3 of 3

Thread: Data Type Mismatch

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Hastings, UK

    Data Type Mismatch

    I am trying to get this VBA code to work, which allows you to email from Access.
    I am getting a 'Data type mismatch in criteria expression'.

    I can't see anything that looks wrong to me, but I am not very experienced.
    Here is the Code:
    Private Sub btn_Send_Click()
        On Error GoTo Err_btn_Send_Click
        Dim stWhere As String       '-- Criteria for DLookup
        Dim varTo As Variant        '-- Address for SendObject
        Dim Message As String        '-- E-mail text
        Dim DateSent As Variant      '-- Sent date for e-mail text
        Dim Subject As String     '-- Subject line of e-mail
        Dim EID As String    '-- The ticket ID from form
        Dim stWho As String         '-- Reference to Email_ADDR
        Dim From As String    '-- Person who sent the email
        Dim strSQL As String        '-- Create SQL update statement
        Dim errLoop As Error
        '-- Combo of names to assign ticket to
        stWho = Me.cboTo_Key
        stWhere = "Email_ADDR.ID = " & "'" & stWho & "'"
        '-- Looks up email address from Email_ADDR
        varTo = DLookup("[Email]", "Email_ADDR", stWhere)
        stSubject = "Subject"
        'EID = Format(Me.EID, "00000")
        stText = Me.Message
        'Write the e-mail content for sending to assignee
        DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
        'Set the update statement to disable command button
        'once e-mail is sent
        strSQL = "UPDATE Email " & _
                 "SET Email.Sent = -1 " & _
                 "Where Email.EID = " & Me.EID & ";"
        On Error GoTo Err_Execute
        CurrentDb.Execute strSQL, dbFailOnError
        On Error GoTo 0
        'Requery checkbox to show checked
        'after update statement has ran
        'and disable send mail command button
        Me.btn_Send.Enabled = False
        Exit Sub
        ' 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 & _
            Next errLoop
        End If
        Resume Next
        Exit Sub
        MsgBox Err.Description
        Resume Exit_btn_Send_Click
    End Sub
    This is the Email table structure:
    EID: Autonumber
    Subject: Short Text
    Message: Short Text
    Date Sent: Date/Time
    To Key: Number
    Sent: Yes/No

    The SQL query looks OK to me, but perhaps I am missing something!

  2. #2
    Join Date
    Oct 2008
    Midwest Region, United States

    Re: Data Type Mismatch

    What does strSQL contain when you run it?

  3. #3
    Join Date
    Dec 2004

    Re: Data Type Mismatch

    is the email sent before the error occurs?

    i would believe a string for the where criteria should be enclosed in ' '
        strSQL = "UPDATE Email " & _
                 "SET Email.Sent = -1 " & _
                 "Where Email.EID = '" & Me.EID & "';"
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width