Data type mismatch in criteria expression - Code Posted

[nixonmg]

When the Command Button "Notify" is clicked, I am wanting to send out an email to the user with appropriate information in the email (works great), check the "Notified" check box (does not work), and disable the "Notify" button (works).

After the email is sent off, i get a Data type mismatch in criteria expression error. I am completely stumped. Any help would be greatly appreciated! Here is my code (I removed the email portion as it is working fine)... Thanks in advance!

I am working with Access 2003

Private Sub Notify_Click()
    On Error GoTo Err_Notify_Click
 
    Dim stWhere As String       '-- Criteria for DLookup
    Dim varTo As Variant        '-- Address for SendObject
    Dim stText As String        '-- E-mail text
    Dim RecDate As Variant      '-- Rec date for e-mail text
    Dim stSubject As String     '-- Subject line of e-mail
    Dim stTicketID As String    '-- The ticket ID from form
    Dim Method As String        '-- Method of Delivery Information
    Dim stWho As String         '-- Reference to tblUsers
    Dim strSQL As String        '-- Create SQL update statement
    Dim errLoop As Error
 
    'Set the update statement to disable command button
    'once e-mail is sent
    strSQL = "UPDATE Requests SET Requests.Notified = -1 " & _
             "Where Requests.CauseNo = " & Me.CauseNo & ";"
 
    Debug.Print strSQL
    On Error GoTo Err_Execute
    CurrentDb.Execute (strSQL)
    On Error GoTo 0
 
    'Requery checkbox to show checked
    'after update statement has ran
    'and disable send mail command button
    Me.Notified.Requery
    Me.Notified.SetFocus
    Me.Notify.Enabled = False
 
    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_Notify_Click:
    Exit Sub
 
Err_Notify_Click:
    MsgBox Err.Description
    Resume Exit_Notify_Click
 
End Sub
 

[ChipR]

You should be able to use Notified.Refresh rather than .Requery, since you haven't added or deleted any records. Have you commented out the error handling to see on what line your error occurs?

[nixonmg]

@ChipR
When commenting out the error handling, the error occurs on line 22

CurrentDB.Execute (strSQL)

[ChipR]

Well, that narrows it down well. What types are the table fields for Notified and CauseNo?

[nixonmg]

@ChipR
Notified is a yes/no check box

CauseNo is a text field

[ChipR]

Here's a very handy reference article on
Quotes (') and Double-Quotes (") - Where and When to use them.

Short story is, since CauseNo is a text field, you need something like:

  ...
  "Where Requests.CauseNo = '" & Me.CauseNo & "';"

[NeoPa]

@ChipR
If CauseNo is a text field then you probably need to change your lines #17 to #18 to :

strSQL = "UPDATE Requests SET [Notified]=TRUE " & _
         "Where [CauseNo]='" & Me.CauseNo & "';"

PS. Good call Chip. I should have remembered to add the link too.

[nixonmg]

ChipR and NeoPa,

Thanks for all the good info. On campus right now, but will be giving all this a whirl in a little bit. I'll chime back in when I get the chance to review the info in the link and try the code modifications. Thanks again.

[nixonmg]

@NeoPa
Did the trick! Thanks for the help! I guess its time to get back to relearning the small things!

[NeoPa]

I'm very pleased to hear that we were able to help.

Welcome to Bytes!