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
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 December 5th, 2007, 10:12 PM
Registered User
 
Join Date: Nov 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update Query Error

I have this SQL statement below to update a table. I Have 2 questions.
If I use:

 DoCmd.RunSQL str

to execute the query it works but if I use:

CurrentProject.Connection.Execute str

I get an error saying "No valuer given for one or more required parameters. Can someone explain please.

Secondly the line:

"DepositAmount = Forms!frmTransaction_continuous!txtDepositAmount " & _

If txtDepositAmount is blank I get the same error:
"No valuer given for one or more required parameters

If I put in a number it works. I get around this by entering zero and this value is stored in the table. I would like the field DepositAmount to be blank instead of it being zero. I tried the NZ function, NZ(Forms!frmTransaction_continuous!txtDepositAmoun t,"") but this does not work.

Thanks for any help

str = "UPDATE tblTransaction SET TransactionType = Forms!frmTransaction_continuous!cboTransactionType , " & _
        "PaymentToWhom = Forms!frmTransaction_continuous!cboPaymentToWhom, " & _
        "Vendor = Forms!frmTransaction_continuous!cboVendor, " & _
        "SSN = Forms!frmTransaction_continuous!SSN, " & _
        "Source = Forms!frmTransaction_continuous!cboSource, " & _
        "TransactionDate = Forms!frmTransaction_continuous!txtDate, " & _
        "CheckNo = Forms!frmTransaction_continuous!txtCheckNo, " & _
        "Category = Forms!frmTransaction_continuous!cboCategory, " & _
        "SubCategory = Forms!frmTransaction_continuous!cboSubCategory, " & _
        "WithdrawlAmount = Forms!frmTransaction_continuous!txtWithdrawlAmount , " & _
        "DepositAmount = Forms!frmTransaction_continuous!txtDepositAmount " & _
        "WHERE TransactionID = " & Me.txtTransactionID
         DoCmd.RunSQL str
        Me.Requery

Reply With Quote
  #2 (permalink)  
Old December 6th, 2007, 02:56 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Where did you get this syntax?

CurrentProject.Connection.Execute str

This looks like a mix of ADO and DAO

CurrentProject.Connection = ADO

Execute str = DAO.

That might be the problem.

As to the other issue, unless you know there will be a value in a field on your form, I would check for a value and supply one this way. It takes a little longer but is more robust code:

Dim str As String
Dim sType As String
Dim iWhom As Assuming
Dim iVendor As Integer 'assuming
Dim sSSN As String
...

If IsNull(Me.cboTransactionType) Or Me.cboTransactionType = "" Then
   MsgBox "Please select a transaction type.", vbCritical
   Exit Sub
Else
   sType = Me.cboTransactionType
End If

If IsNull(Me.cboPaymentToWhom) Then
   MsgBox "Please select a recipient.", vbCritical
   Exit Sub
Else
   iWhom = Me.cboPaymentToWhom
End If
...

str = "UPDATE tblTransaction SET TransactionType = '" & sType & "', " & _
   "PaymentToWhom = " & iWhom & "', " & _
   ...

and so on to deposit amount.

The problem is, that your deposit amount field is probably numeric, and probably currency, so it would take a blank amount, it must take Zero. If it is an Access table, Access will supply the zero if you don't put in a record. You can set the Default Value of the field to be a blank value, and I think this will allow you to post "", or perhaps you do this:

If IsNull(Me.txtDepositAmount) Or Me.txtDepositAmount = "" Then
   str = "... and skip the deposit amount field."
Else
   str = "... and add the deposit field."
End If

If the field is numberic, it won't accept "", just 0. "" is a string data type, not a numeric data type. So modify your Update string to either have the value if it is some number, or skip it if it is empty, since Access won't care if you skip it unless the field is required. The default will be Null, I think, in that case.

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old December 6th, 2007, 02:57 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Man, did I fat finger that post or what.
It won't take "", only 0 if it is numeric, so skip it in your str if the textbox is empty.


mmcdonal

Look it up at: http://wrox.books24x7.com
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
SQL Update query (error message) Neal SQL Server 2000 1 August 3rd, 2007 08:57 PM
UPDATE query throws error in selective MSI tables eelisMX General .NET 0 July 14th, 2005 06:29 AM
update query error in from clause prasath15@yahoo.co.in Classic ASP Databases 1 June 6th, 2005 06:02 AM
error in update query. feras Access ASP 1 May 2nd, 2005 07:05 PM
update query error khautinh SQL Server 2000 2 July 1st, 2004 09:30 PM



All times are GMT -4. The time now is 12:31 AM.


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