Error #3464

[Taff]

Hi All,

I have the an error coming up "Data Type Mismatch in Criteria Expression".

The cause of this is code i have (Thanks to Mile-o-Phile for the code) behind my command button. It has worked fine up until recently and i'm not sure what the problem is.

The code is below:-

Private Sub Cmddeletelearningprogramme_Click()

    On Error GoTo Err_Cmddeletelearningprogramme_Click

    Const strSQLInsert = "INSERT INTO [Deleted Learning Programmes] " & _
            "SELECT * FROM [Learning Programme Dataset] "
    Const strSQLDelete = "DELETE * FROM [Learning Programme Dataset] "
    Dim strCriteria As String
    Dim strPrompt As String
    
    Const Title = "Warning!"
    Const Buttons = vbYesNo + vbExclamation
    
    If IsNull(Me.List8) Then Exit Sub
    
    strCriteria = _
        "WHERE " & _
        "[learn_id] = """ & Me.List8.Column(0) & """ AND " & _
        "[provi_id] = """ & Me.List8.Column(1) & """ AND " & _
        "[lprog_id] = " & Me.List8.Column(2) & ";"

    
    If Me.List8.Column(7) = vbNullString Then
        strPrompt = "Are you sure you want to Delete this Learning Programme?" & vbCrLf & _
            "" & vbCrLf & _
            "You will not be able to undo this!"
    Else
        strPrompt = "This Learning Programme has been sent to ELWa" & vbCrLf & _
            "" & vbCrLf & _
            "Would you like to send this Learning Programme" & vbCrLf & _
    "to the Deleted Learning Programmes Table?"
    End If

    If Me.List8.ItemsSelected.Count > 0 Then
        If MsgBox(strPrompt, Buttons, Title) = vbYes Then
            With DoCmd
                If Me.List8.Column(7) = vbNullString Then
                    .SetWarnings False
                    .RunSQL strSQLDelete & strCriteria
                    .SetWarnings True
                Else
                    .SetWarnings False
                    .RunSQL strSQLInsert & strCriteria
                    .RunSQL strSQLDelete & strCriteria
                    .SetWarnings True
                End If
            End With
            Me.List8.Requery
        End If
    End If

Exit_Cmddeletelearningprogramme_Click:
    strCriteria = vbNullString
    strPrompt = vbNullString
    Exit Sub
    
Err_Cmddeletelearningprogramme_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Cmddeletelearningprogramme_Click

End Sub

Any Ideas

Thanks

Ant

 

[Mile-O]

"[learn_id] = """ & Me.List8.Column(0) & """ AND " & _
"[provi_id] = """ & Me.List8.Column(1) & """ AND " & _
"[lprog_id] = " & Me.List8.Column(2) & ";"

Are these all text fields?

 

[Taff]

Hi Mil-O-Phile,

Yes they are.

 

[Mile-O]

Then the last line I quoted should be:

"[lprog_id] = """ & Me.List8.Column(2) & """;"

 

[Taff]

Thanks Mile-O-Phile,

Works Great!!!