Command Button to Check Checkbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kate - 14 Jan 2008 03:28 GMT

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.

Signature

Kate
Just learning to use access

Reply to this Message

Dirk Goldgar - 14 Jan 2008 03:39 GMT

> Trying to create a command button to check certain boxes.
> Table is Addresses
[quoted text clipped - 13 lines]
> =
> -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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Kate - 14 Jan 2008 04:02 GMT

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

Signature

Kate
Just learning to use access

> > Trying to create a command button to check certain boxes.
> > Table is Addresses
[quoted text clipped - 21 lines]
> "UPDATE Addresses Set [Print Label] = -1 Where [Married] = 0", _
> dbFailOnError

Reply to this Message

Rob Parker - 14 Jan 2008 03:46 GMT

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

> Trying to create a command button to check certain boxes.
> Table is Addresses
[quoted text clipped - 25 lines]
>
> Thanks in advance.

Reply to this Message

Dirk Goldgar - 14 Jan 2008 03:58 GMT

> Hi Kate,
>
[quoted text clipped - 7 lines]
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Rob Parker - 14 Jan 2008 07:19 GMT

Thanks for that, Dirk.

Always something to learn in these newsgroups :-)

Rob

>> Hi Kate,
>>
[quoted text clipped - 14 lines]
> think of where a semicolon is acrtually required, because it's the only
> place where it is needed as a statement delimiter.

Reply to this Message