UPDATE tbl with vba

[emtenzer]

I'm trying to "blank out" a field in a table on update of a field on a form. My code is:

db.Execute "UPDATE tblClients SET DeniedCreditDate = Null, & " WHERE ClientID = " & Me.ClientID, dbFailOnError

I've tried updating the field to "", but that doesn't work either. I'm getting an "Expected end of statement" error message and it goes to the word "WHERE".

What am I missing?
Thanks.

 

[randy700]

How about...

db.Execute "UPDATE tblClients SET DeniedCreditDate = Null WHERE ClientID = " & Me.ClientID, dbFailOnError

Randy

 

[lespaul]

just so you get what it's trying to tell you.....
[tt]
db.Execute "UPDATE tblClients SET DeniedCreditDate = Null, & " WHERE ClientID = " & Me.ClientID, dbFailOnError[/tt]

the two blue quotes are what you need, the extra red quote is what is causing your error and why the code stopped on the WHERE portion of your query....

Leslie

 

[emtenzer]

Thank you. No error message, but the date field is still as it was. It was not cleared out.

 

[dhookom]

There is an extra comma in some of the previous statements. This should cause an error. I generally create a strSQL variable of the SQL statement and then execute it. You can use debug.Print strSQL to get the statement into the debug window where you can find out what's going on.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

db.Execute "UPDATE tblClients SET DeniedCreditDate = Null WHERE ClientID = " & Me.ClientID, dbFailOnError

This should work IF ClientID is a number field.

If ClientID is a TEXT field, try this...

db.Execute "UPDATE tblClients SET DeniedCreditDate = Null WHERE ClientID = [b][COLOR=red]'[/color][/b]" & Me.ClientID [b][COLOR=red]& "'"[/color][/b], dbFailOnError

Randy

 

[emtenzer]

The ClientID is a number field but that code didn't work.

 

[randy700]

It works perfectly in mine. What error are you getting?
Please post your updated code.


Randy

 

[emtenzer]

this is my code:

Dim intAnswer As Integer
Dim db As DAO.Database

If Me.DeniedCredit = False Then
intAnswer = MsgBox("By changing this option, Denied Credit, the client will now be able to get a new loan. Do you want to continue?", vbYesNo, "Allow Credit?")
End If

If intAnswer = vbYes Then
OpenVerifyPopup ("CanRemoveDeniedCreditLimitation")
If gVerifyReturnVal Then
'Will change value as normal
Else
Me.DeniedCredit.Undo
Cancel = True 'Undo the change to the value
'db.Execute "UPDATE tblClients SET DeniedCreditDate = Null, & WHERE ClientID = " & Me.ClientID, dbFailOnError
db.Execute "UPDATE tblClients SET DeniedCreditDate = Null WHERE ClientID = " & Me.ClientID, dbFailOnError
End If
Else 'They answered no to the question about continuing... don't perform update
Me.DeniedCredit.Undo
Cancel = True
End If


End Sub

 

[dhookom]

I don't see where you are setting the db object to anything. Try:

Dim intAnswer As Integer
Dim db As DAO.Database
Dim strSQL as String  
[b]SET db = Currentdb[/b]
If Me.DeniedCredit = False Then
    intAnswer = MsgBox("By changing this option, Denied Credit, the client will now be able to get a new loan. Do you want to continue?", vbYesNo, "Allow Credit?")   
End If  
If intAnswer = vbYes Then
    OpenVerifyPopup ("CanRemoveDeniedCreditLimitation")
    If gVerifyReturnVal Then      'Will change value as normal
    Else
      Me.DeniedCredit.Undo
      Cancel = True   'Undo the change to the value
      strSQL ="UPDATE tblClients " & _
         "SET DeniedCreditDate = Null " & _
         "WHERE ClientID = " & Me.ClientID
      db.Execute strSQL, dbFailOnError
   End If
   Else    'They answered no to the question about continuing _ 
       don't perform update
    Me.DeniedCredit.Undo
    Cancel = True
  End If
SET db= Nothing

Duane
Hook'D on Access
MS Access MVP

 

[emtenzer]

It still kept the date in the field!

 

[dhookom]

Add a line prior to the Execute

Debug.Print strSQL

Then view the SQL to see what your issue is.

Duane
Hook'D on Access
MS Access MVP