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

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

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