run-time error '13' type mismatch

[teebumble]

Hi,

Can you please review my code and let me know what I am doing wrong?

I have this code in the beforeupdate sub

String (text) = firstname, lastname, empemail, docID, revision, docdesc, LenDays, JobFunc, Managers, VPMgrs, ECONum, Trainer, comments
Number = revnum
Date = DateAssigned, DateCompleted


True/False = Annual, TrainReq

 
Private Sub DateCompleted_BeforeUpdate(Cancel As Integer)
    If Me.Annual = False Then
        Exit Sub
    Else
        Dim AnnualTraining As String
        Set rs = Nothing
        AnnualTraining = vbNullString
        Set db = CurrentDb
        Set rs = Me.RecordsetClone
        AnnualTraining = "INSERT INTO EmpDocStatus (FirstName, LastName, EmpEmail," & _
                    "DocID, Revision, RevNum, DocDesc, DateAssigned, DateCompleted," & _
                    "LenDays, JobFunc, Annual, Managers, VPMgrs, TrainReq, ECONum, Trainer, Comments)" & _
            " VALUES ('" & _
            rs!FirstName + "','" & _
            rs!LastName + "','" & _
            rs!EmpEmail + "','" & _
            rs!DocID + "','" & _
            rs!Revision + "','" & _
            rs!RevNum + "','" & _
            rs!DocDesc + "', '" + Date + "', Null, '" & _
            rs!LenDays + "','" & _
            rs!JobFunc + "', '" & _
            rs!Annual + "', '" & _
            rs!Managers + "', '" & _
            rs!VPMgrs + "', '" & -1 & "', Null, Null, Null)"
        db.Execute (AnnualTraining)
        Me.UpdatelblStatus.Caption = "Updating training record(s). Please wait...."
        Me.Repaint
        MsgBox "Annual Training assigned."
        Me.UpdatelblStatus.Caption = "Status: "
    End If
    
    Set rs = Nothing
    AnnualTraining = vbNullString
    
Exit_AssignTrain_Click:
    Exit Sub
    
Err_AnnualTrain:
    MsgBox Err.Description
    Resume Exit_AssignTrain_Click
End Sub

[pbaldy]

This may help:

http://www.baldyweb.com/ImmediateWindow.htm

along with the fact that numeric data types should not have the ' surrounding the values and date/time data types should have # instead of '.

[teebumble]

thanks for the help. I figured out what I did wrong. I should not have used openrecordset. i didn't need it. I just needed to use me.___

[pbaldy]

I can't see that affecting the type mismatch, but I'm glad you found a solution.

[uronmapu]

The same this error message with code below

at line Set rst = db.OpenRecordset("tblLuuVetChuongTrinh")

Function CapNhatLuuVet(fUserName, fOldValue, fNewValue, fControlName, fFormName)
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblLuuVetChuongTrinh")

rst.AddNew
    rst!TenUser = fUserName
    rst!GiaTriCu = fOldValue
    rst!GiatriMoi = fNewValue
    rst!TenControl = fControlName
    rst!TenForm = fFormName
    rst!NgayCapNhat = Date
    rst!GioCapNhat = Time()
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing
End Function

Please advices
Thanks

[pbaldy]

Disambiguate the declaration to

Dim rst As DAO.Recordset

[uronmapu]

Many thanks for your help

It's ok now

[pbaldy]

No problem.

[TLoi]

I encountered the same error when open a form to run the following VB code to transfer a excel spreadsheet to the access table. The error occurred when I open the form. The error message box cleared after click on the OK button and the transfer was transfered. Can't figure out what the error about. I would appreciated very much for the fix of the error.

Thanks.

Private Sub Form_Open(Cancel As Integer)
Dim strCurrFileName As String, strHighFileName As String
Dim lngRowCount As Long, lngRecordCount As Long
On Error GoTo ErrorHandler
Me.lstAvailImports.RowSource = ""
strCurrFileName = Dir("path of the directory")
strHighFileName = DLookup("CIP212Filename", "table name1", "Entered = #" & _
DMax("Entered", "table name1") & "#")

lngRowCount = 0
Do While strCurrFileName <> ""
If strCurrFileName > strHighFileName Then
DoCmd.TransferSpreadsheet acLink, _
"temp table", _
"directory path\" & strCurrFileName, vbFalse
lngRecordCount = DCount("*", "tzimpAccountStatusCheckTemp")
Me.lstAvailImports.AddItem strCurrFileName & ";" & lngRecordCount
End If
strCurrFileName = Dir()
Loop
Exit Sub
ErrorHandler:
MsgBox "Error Encountered: " & Err.Number & " - " & Err.DESCRIPTION
Resume Next
End Sub