Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 4th, 2006, 07:23 PM
Authorized User
 
Join Date: Mar 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error when trying to use an UPDATE statement

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.

 
Old April 4th, 2006, 07:29 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
 
Old April 4th, 2006, 07:50 PM
Authorized User
 
Join Date: Mar 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL statement causing error with insert/update saf SQL Language 0 September 7th, 2007 04:48 PM
Update Statement some error thas123 ADO.NET 1 January 8th, 2006 10:30 AM
Syntax Error in the update statement annie_t Access ASP 1 February 1st, 2005 12:48 PM
UPDATE statement returns syntax error AviatorTim Classic ASP Databases 2 January 8th, 2004 05:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.