Runtime Error 80040e10 Excel VBA MS Access

[cnerurkar]

Hi Guys,

I am trying to update specific fields of table in Ms Access, through Excel VBA. My select and add new record queries work, but the update query always throws the RUN TIME ERROR 80040e10 - "No Value Given To One or More Required Parameters"

I have checked my table name and the other field names over and over again, but I see nothing wrong with the quesry. I am trying to update a few fields from the database, for a existing record number (autonumber).

Function CheckDB(Serial_No_New_Part As String, Serial_No_Failed_Part As String, ConStr As String) _ 
    As Boolean 
    Dim MsgBox_Answer As Integer 
     
    ' Variable stores ADO Connection Object to Ms Access DB
    Dim cnt As New ADODB.Connection 
     
    ' Variable for Recordset Object
    Dim rst As New ADODB.Recordset 
     
    ' Variable stores Database path
    Dim strDB As String 
     
     
    ' Variable stores SQL 'SELECT' Query
    Dim SQLSelectNewPart As String 
    Dim SQLSelectFailedPart As String 
    Dim SQLUpdate As String 
     
    Dim Primary_No_Failed_Part As Long 
    Dim Run_Time_Hrs_Failed_Part As Long 
    Dim Part_Status_Failed_part As String 
    Dim Date_Failed_Failed_part As Date 
    Dim Failed_Location_Failed_Part As String 
    Dim Failure_History_Failed_Part As String 
    Dim Description_Failed_Part As String 
     
    ' Set the string to the path of your database
    strDB = ConStr 
     
    ' Open connection to the database
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strDB & ";" 
     
    rst.Open SQLSelectNewPart, cnt, adOpenDynamic, adLockBatchOptimistic, adCmdText 
     
     
    ' In simple words, look whether any records in the DB contain the same "Part_Serial_No"
    ' Check whether recordset is empty,implies no records found
    ' BOF = Begining of File
    ' EOF = End of File
    ' IF ((Not BOF = TRUE) AND (Not EOF = TRUE)), Implies Records Have Been Found
    If (rst.BOF = False And rst.EOF = False) Then 
         'Primary_Key = rst.Fields("Primary_No").Value
         
        MsgBox_Answer = MsgBox("A part with the matching 'Serial No' already exists in the Database" _ 
        & vbCrLf & "Click on the 'OK BUTTON' to display the 'ADD REPAIRED PART FORM'" & vbCrLf & _ 
        "or click on the 'CANCEL BUTTON' to edit the 'ADD NEW/REPLACE OLD FORM'", vbOKCancel, _ 
        "Existing Part in Database") 
         
        If (MsgBox_Answer = 1) Then 
             
            'Temporary Storage - Retrieve Existing Failed Tage Info from Database
            'Store this info temporarily on Sheet7 - Row(2-3), Column 5
            Sheet7.Cells(2, 5).Value = CLng(rst.Fields("Primary_No").Value) 
            
            'Failed Part Row No on Sheet1 retrieved from UserForm6
            Sheet7.Cells(3, 5).Value = CLng(UserForm6.TextBox17.Value) 
            
            'Write DB Path
            Sheet7.Cells(4, 5).Value = UCase(CStr(strDB)) 
             
             
            'Close Connection Objects and Recordset Objects.
            ' Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
             
             
            CheckDB = False 
             
            Unload UserForm6 
            UserForm1.Show 
             
        ElseIf (MsgBox_Answer = 2) Then 
            CheckDB = False 
             
            'Close Connection Objects and Recordset Objects.
            'Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
            Exit Function 
        Else 
            CheckDB = False 
             
            'CloseConnection Objects and Recordset Objects.
            'Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
            Exit Function 
        End If 
         
    'Else no records have been found - Implies New Part is New and is not a Failed part that has been repaired
    'Check whether Failed Part exists in DB
    'If Failed Part Exists, Append existing record
    'Else Add New Record for Failed Part in DB
    Else 
        rst.Close 
        'Now check whether Failed Part exists in DB
        SQLSelectFailedPart = "Select * FROM Failed Where Serial_No= '" _ 
        & UCase(CStr(Serial_No_Failed_Part)) & "'" 
         
        'Open recordset based on Trial table
        rst.Open SQLSelectFailedPart, cnt, adOpenDynamic, adLockBatchOptimistic, adCmdText 
         
        If (rst.BOF = True And rst.EOF = True) Then 
             
            'Add Failed Part Data as a new Record
            'Close Open Recordset
            rst.Close 
             
            '*********START Procedure to Add New Record********
            rst.Open "Failed", cnt, adOpenKeyset, adLockOptimistic, adCmdTable 
            rst.AddNew 
            rst!Part = UCase(CStr(UserForm6.TextBox14.Value)) 
            rst!Serial_No = UCase(CStr(UserForm6.ComboBox3.Value)) 
            rst!Material_Type = UCase(CStr(UserForm6.TextBox9.Value)) 
            rst!Run_Time_Hours = CLng(UserForm6.TextBox10.Value) 
            rst!Current_Part_Status = UCase(CStr(UserForm6.ComboBox4.Value)) 
            rst!Date_Failed = Format(CDate(UserForm6.TextBox12.Value), "mm/dd/yyyy") 
            rst!Failed_Location = UCase(CStr(UserForm6.TextBox15.Value)) 
            rst!Vendor_Name = UCase(CStr(UserForm6.TextBox13.Value)) 
             
            rst!Description = Replace(UCase(CStr(UserForm6.TextBox19.Value)), Chr(34), "''") 
            rst.Update 
             
            CheckDB = True 
             
            'close Connection Objects and Recordset Objects.
            'Close ADO objects
            rst.Close 
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
             
             
        'If existing record found, then Append and Update existing record
        Else 
            Primary_No_Failed_Part = rst.Fields("Primary_No").Value 
             
            Decription_Failed_Part = Replace(UCase(CStr(rst.Fields("Description").Value)), _ 
            Chr(34), "'") 
             
            Part_Status_Failed_part = UCase(CStr("Failed")) 
             
            Run_Time_Hrs_Failed_Part = CLng(rst.Fields("Run_Time_Hours")) 
             
            Date_Failed_Failed_part = Format(rst.Fields("Date_Failed"), "mm/dd/yyyy") 
             
            Failed_Location_Failed_Part = UCase(CStr(rst.Fields("Failed_Location"))) 
             
            'Close open RecordSet
            rst.Close 
             
            Failure_History_Failed_Part = UCase(CStr("Run Time Hours = " & Run_Time_Hrs_Failed_Part & _ 
            vbCrLf & "Date_Failed = " & Format(CDate(Date_Failed_Failed_part), "dd,mmm,yyyy") & _ 
            vbCrLf & "Failure_Location = " & Failed_Location_Failed_Part & vbCrLf & _ 
            "---------------------------------------" & vbCrLf)) 
             
            Description_Failed_Part = UCase(CStr(vbCrLf & "-----------------------------------" & vbCrLf & _ 
            UCase(CStr(UserForm6.TextBox19.Value)))) 
             
            Description_Failed_Part = Replace(Description_Failed_Part, Chr(34), "''") 
             
             
            'SQL UPDATE QUERY
            SQLUpdate = "UPDATE Failed SET [Run_Time_Hours] = " & CLng(UserForm6.TextBox10.Value) & _ 
            ", [Current_Part_Status] = '" & UCase(CStr(Part_Status_Failed_part)) & _ 
            "', [Date_Failed] = " & CDate(UserForm6.TextBox12.Value) _ 
            & ", [Failed_Location] = '" & UCase(CStr(UserForm6.TextBox15.Value)) & _ 
            "', [Failure_History] = '" & Failure_History_Failed_Part & _ 
            "', [Description] = '" & Description_Failed_Part & _ 
            "' WHERE Failure.Primary_No = " & CLng(Primary_No_Failed_Part) 
             
             
            'ERROR on this statement
            cnt.open SQLUpdate             
             
            CheckDB = True 
             
            'close Connection Objects and Recordset Objects.
            'Close ADO objects
            cnt.Close 
            Set rst = Nothing 
            Set cnt = Nothing 
             
            Unload UserForm6 
        End If 
         
    End If 
End Function

Could someone point our what I am doing wrong. The error occurs on the statement
VBA:
cnt.open SQLUpdate

ERROR: RUN TIME ERROR: 80040e10 - "No Value Given To One or More Required Parameters"


Thanks.

Regards,

Cnerurkar

 

[Norie]

Have you tried enclosing the data in #'s just like you've enclosed the text in single quotes?

 

[cnerurkar]

Thank you Norie. I appreciate your comments and your help. I had the "#" in front of the date, but that didn;t work, or may be I had something else wrong then. I will try your suggestion, and let you know how it worked.

In the mean time, could someone re-write the SQLUpdate Query, with the proper syntax if I am missing anything. The problem is definitely with this peice of Code

 'SQL UPDATE QUERY
SQLUpdate = "UPDATE Failed SET [Run_Time_Hours] = " & CLng(UserForm6.TextBox10.Value) & _ 
", [Current_Part_Status] = '" & UCase(CStr(Part_Status_Failed_part)) & _ 
"', [Date_Failed] = " & "#" & CDate(UserForm6.TextBox12.Value) _ 
& "#"  ", [Failed_Location] = '" & UCase(CStr(UserForm6.TextBox15.Value)) &  _ 
"', [Failure_History] = '" & Failure_History_Failed_Part & _ 
"', [Description] = '" & Description_Failed_Part & _ 
"' WHERE Failure.Primary_No = " & CLng(Primary_No_Failed_Part) 
 
 
 'ERROR on this statement
cnt.open SQLUpdate

What is particularly confusing, is that my Field Names in the Database contain underscores. I read somewhere that if the field names are reserved words or have underscores, then they should be enclosed with brackets, example [Field_Name].

Also, I need the proper syntax for a query, when I am passing data as text, date, and a number, as a variable, in the SQLUpdate Query.

Thank you for your patience and support guys.

Regards,

Cnerurkar