Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 4th, 2006, 07:23 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
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.

Reply With Quote
  #2 (permalink)  
Old April 4th, 2006, 07:29 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
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
Reply With Quote
  #3 (permalink)  
Old April 4th, 2006, 07:50 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:51 PM.


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