Command Button to Check Checkbox

[Kate]

Trying to create a command button to check certain boxes.
Table is Addresses
Married is checkbox
PrintLabel is checkbox

Want to check printlabel on all forms that married is not checked.
The If statement works on the form I am on only,
so I tried an SQL but says syntax error.


Private Sub cmdCheckAllPrintLabel_Click()
On Error GoTo Err_cmdCheckAllPrintLabel_Click
'If Me.Married = False Then
'Me.[Print Label] = True
'End If
CurrentDb.Execute "UPDATE Addresses Where [Married] = 0 Set [Print Label] =
-1", dbFailOnError
Me.Requery

Exit_cmdCheckAllPrintLabel_Click:
Exit Sub

Err_cmdCheckAllPrintLabel_Click:
MsgBox Err.Description
Resume Exit_cmdCheckAllPrintLabel_Click

End Sub

Thanks in advance.

 

[Dirk Goldgar]

Trying to create a command button to check certain boxes.
Table is Addresses
Married is checkbox
PrintLabel is checkbox

Want to check printlabel on all forms that married is not checked.
The If statement works on the form I am on only,
so I tried an SQL but says syntax error.


Private Sub cmdCheckAllPrintLabel_Click()
On Error GoTo Err_cmdCheckAllPrintLabel_Click
'If Me.Married = False Then
'Me.[Print Label] = True
'End If
CurrentDb.Execute "UPDATE Addresses Where [Married] = 0 Set [Print Label]
=
-1", dbFailOnError

You've got the order of clauses wrong in the SQL statement. Did you try
looking in the online help, in the "Microsoft Jet SQL Reference" section?
See if this works better:


CurrentDb.Execute _
"UPDATE Addresses Set [Print Label] = -1 Where [Married] = 0", _
dbFailOnError

 

[Rob Parker]

Hi Kate,

You've got the WHERE clause before the SET clause in your SQL. Try:
...
CurrentDb.Execute "UPDATE Addresses SET [Print Label] = -1 WHERE
[Married] = 0;", dbFailOnError
...

Note: I've also added the closing semicolon to the SQL string. I don't
think it will cause an error if it's missing, but it should be there for a
valid SQL string. Also note that you could use the constants True and False
in your SQL string, thus:
"UPDATE Addresses SET [Print Label] = True WHERE [Married] = False;"

HTH,

Rob

 

[Dirk Goldgar]

Hi Kate,

You've got the WHERE clause before the SET clause in your SQL. Try:
...
CurrentDb.Execute "UPDATE Addresses SET [Print Label] = -1 WHERE
[Married] = 0;", dbFailOnError
...

Note: I've also added the closing semicolon to the SQL string. I don't
think it will cause an error if it's missing, but it should be there for a
valid SQL string.

The semicolon is not required, and its absence won't cause an error. It's
supposed to be used to separate multiple SQL statements, but Jet SQL
supports only one SQL statement in a query, aside from the optional
PARAMETERS statement for a parameter query. That't the only place I can
think of where a semicolon is acrtually required, because it's the only
place where it is needed as a statement delimiter.

 

[Kate]

Thanks Dirk,
Things work if they are done right.
Thanks again for the help.

--
Kate
Just learning to use access

Trying to create a command button to check certain boxes.
Table is Addresses
Married is checkbox
PrintLabel is checkbox

Want to check printlabel on all forms that married is not checked.
The If statement works on the form I am on only,
so I tried an SQL but says syntax error.


Private Sub cmdCheckAllPrintLabel_Click()
On Error GoTo Err_cmdCheckAllPrintLabel_Click
'If Me.Married = False Then
'Me.[Print Label] = True
'End If
CurrentDb.Execute "UPDATE Addresses Where [Married] = 0 Set [Print Label]
=
-1", dbFailOnError

You've got the order of clauses wrong in the SQL statement. Did you try
looking in the online help, in the "Microsoft Jet SQL Reference" section?
See if this works better:


CurrentDb.Execute _
"UPDATE Addresses Set [Print Label] = -1 Where [Married] = 0", _
dbFailOnError



--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Rob Parker]

Thanks for that, Dirk.

Always something to learn in these newsgroups

Rob

Hi Kate,

You've got the WHERE clause before the SET clause in your SQL. Try:
...
CurrentDb.Execute "UPDATE Addresses SET [Print Label] = -1 WHERE
[Married] = 0;", dbFailOnError
...

Note: I've also added the closing semicolon to the SQL string. I don't
think it will cause an error if it's missing, but it should be there for
a valid SQL string.

The semicolon is not required, and its absence won't cause an error. It's
supposed to be used to separate multiple SQL statements, but Jet SQL
supports only one SQL statement in a query, aside from the optional
PARAMETERS statement for a parameter query. That't the only place I can
think of where a semicolon is acrtually required, because it's the only
place where it is needed as a statement delimiter.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)