SQL update

[tommeekers]

I have a very weird problem with a very simple SQL query.

My main form has one subform and one button on it. In the subform I have a table in datasheet view. This table contains a few text fields and a Yes/No field called selected. The user can update this field directly on the datasheet by clicking the checkbox (marking the record as selected).
The button on my main form is used for selecting all records at once. I tried to do this with a simple SQL query.

DoCmd.RunSQL "UPDATE [i]MyTable[/i] SET [i]selected[/i] = True"

This returns an error Syntax error in UPDATE command, but to my best knowledge there is not syntax error in this line. Any idea what could prevent the update from working ?

 

[MajP]

Selected is a bad name for a field. Maybe "boolSelected" would be better. "Selected" is a property in VBA of a control, I think at a minimum you need [selected].

 

[Golom]

You might also try

DoCmd.RunSQL "UPDATE MyTable SET [selected] = " & True

or

DoCmd.RunSQL "UPDATE MyTable SET [selected] = -1"

 

[tommeekers]

I changed the name of the field to recordselect. I tried all possible combinations listed here, but still the same error keeps popping up. Any other ideas ?

DoCmd.RunSQL "UPDATE MyTable SET recordselect = True"

DoCmd.RunSQL "UPDATE [i]MyTable[/i] SET recordselect = -1"

DoCmd.RunSQL "UPDATE [i]MyTable[/i] SET [recordselect] = True"

DoCmd.RunSQL "UPDATE [i]MyTable[/i] SET [recordselect] = -1"

DoCmd.RunSQL "UPDATE [i]MyTable[/i] SET [recordselect] = " & True

 

[hneal98]

Have you tried this...

DoCmd.RunSQL "UPDATE MyTable SET selected = True;"