Syntax Error (Missing Operator) 1

[cneill]

Where is the error?, I can't find it

DoCmd.RunSQL ("UPDATE tblAccountSchemes SET tblAccountSchemes.DelOrInstAttached = -1" & "WHERE ((tblAccountSchemes.ActivityID)=([Forms]![FrmSchemes].ActivityID)));")

 

[RoyVidar]

One hint would be to assign the string to a variable, then print it to the immediate pane (ctrl+g)

[tt]Dim SQL As String
SQL = "UPDATE tblAccountSchemes SET tblAccountSchemes.DelOrInstAttached = -1" & "WHERE ((tblAccountSchemes.ActivityID)=([Forms]![FrmSchemes].ActivityID)));"
Debug.Print SQL[/tt]

The SQL you pick up, you should be able to run by dumping it into the SQL view of the query builder...

Here, there is at least two errors
1 - lacking space between -1 and WHERE
2 - though it might work sometimes, what you enter into the string, is the reference, not the value

[tt]SQL = "UPDATE tblAccountSchemes SET " & _
"DelOrInstAttached = -1 " & _
"WHERE ActivityID = " & Forms!FrmSchemes!ActivityID[/tt]

Should the code be on the current form, you could use

Me!ActivityID in stead of the complete form reference.

Roy-Vidar

 

[HarleyQuinn]

There's a couple of problems with the actual SQL string, first one is you have an extra bracket on the end, you open three but close four.

Another problem is you have no space between your -1 and your WHERE clause.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[TheAceMan1]

How are ya cneill . . .


Perhaps this:

[blue]   Dim SQL As String
   
   SQL = "UPDATE tblAccountSchemes " & _
         "SET tblAccountSchemes.DelOrInstAttached = -1 " & _
         "WHERE (tblAccountSchemes.ActivityID=[Forms]![FrmSchemes].[ActivityID];"
   DoCmd.RunSQL SQL[/blue]

You do know [blue]FrmSchemes[/blue] has to be open!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HarleyQuinn]

*cough* closing parentheses AceMan

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[cneill]

Hi Roy,

Okay now no syntax error (great start)

so now used this
Dim SQL As String
SQL = "UPDATE tblAccountSchemes SET " & _
"tblAccountSchemes.DelOrInstAttached = -1 " & _
"WHERE tblAccountSchemes.ActivityID = " & Forms!FrmSchemes!ActivityID
Debug.Print SQL

immediate pane shows this
UPDATE tblAccountSchemes SET tblAccountSchemes.DelOrInstAttached = -1 WHERE tblAccountSchemes.ActivityID = 3393

The sql is picking up the correct ActivityID, the field DelOrInstAttached is a yes/no field which is not being updated to Yes.
Any other thoughts?

 

[cneill]

Srry people
forgot to add DoCmd.RunSQL SQL to run the SQL

Now working fine, thanks for everyones help

 

[RoyVidar]

Better, would probably be to use the .execute method of the database, say

[tt]CurrentDB.Execute SQL, dbFailOnError[/tt]

which, among other things, means you don't have to put up with those N records being updated thingies.

Roy-Vidar