Error when trying to use an UPDATE statement

wayne62682 · April 4th, 2006, 07:23 PM

Round three :)

I'm finally at the point where I can code the functionality to let my users enter and update information in the application. However, I get the "No value entered for one or more required parameters" error when I try to execute an UPDATE command from an ADODB.Command object.

Code:

Code:

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strSQL As String

    strSQL = "UPDATE tblResident SET " & _
        "FirstName = '" & Me.txtFirstName & "', " & _
        "LastName = '" & Me.txtLastName & "', " & _
        "SupportCoordinator = '" & Me.txtSupportCoordinator & "', " & _
        "CFSWorker = '" & Me.txtCFSWorker & "', " & _
        "Address = '" & Me.txtAddress & "', " & _
        "SocialSecurityNo = '" & Me.txtSSN & "', " & _
        "Age = '" & Me.txtAge & "', " & _
        "DateOfBirth = '" & Me.txtDateOfBirth & "', " & _
        "MakeHealthCareDecision = '" & Me.chkMakeHealthcareDecision & "', " & _
        "MotherName = '" & Me.txtMotherName & "', " & _
        "MotherAddress = '" & Me.txtMotherAddress & "', " & _
        "MotherPhone = '" & Me.txtMotherPhone & "', " & _
        "FatherName = '" & Me.txtFatherName & "', " & _
        "FatherAddress = '" & Me.txtFatherAddress & "', " & _
        "FatherPhone = '" & Me.txtFatherPhone & "', " & _
        "OtherRelatives = '" & Me.txtOtherRelatives & "', " & _
        "ArrivalDate = '" & Me.txtArrivalDate & "', " & _
        "LastUpdate = '" & Me.txtDateUpdated & "' " & _
        "WHERE ResidentID = " & Me.cboSelectResident.value

    Set cnn = CurrentProject.Connection
    Set cmd = New ADODB.Command

    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = strSQL
        .Execute
    End With

The fields in the UPDATE statement are in the exact order that they appear in the table (tblResident), and all the values appear. Not sure if that even matters... the debugger is flagging the .Execute method as being the troublemaker. However, I must be missing something because I can't see where the problem lies. Any ideas what I'm missing and how to fix it?

Thanks yet again for the help.. I really appreciate it as I learn VBA.

echovue · April 4th, 2006, 07:29 PM

I don't know exactly what the problem is, but this is how I would approach it.

In the VBA editor, put a break point on the With cmd line (You do this by click in the margin, and you should get a brown circle with the line highlighted.

Next run the code. It will stop on the break point.

On the View Menu, enable the immediate window. This should appear below your coding window.

In the immediate window, type ?strSQL, and push enter.

It should print out the value of your SQL String.

Copy this string, open up a new Query, change it to SQL View, Paste it, and try and run it. If there is an error, your cursor will be at that point, and you can troubleshoot it from there. Be sure to mirror any change you make back in your SQL code.

Hope that helps,

Mike

Mike
EchoVue.com

wayne62682 · April 4th, 2006, 07:50 PM

Hmm.. okay I seem to have figured it out... Apparantly the error was because the True/False value of the checkbox (True/False in the database) needed to be evaluated as an Integer and not Boolean for the SQL statement (i.e. 1 for True, 0 for False). I guess Access doesn't evaluate it like that atuomatically... however, a simple If.. Then statement solved the problem.