Check Box on form

[Wavequation]

I am trying to change the value in a check box using code. The control is a
check box named "Order Complete", and is bound to a field in a table, also
named "Order Complete", with a field type of yes/no. The control is enabled,
and not locked, and may be changed by clicking in it. The code is triggered
by the "on close" event. On close of the form, I would like the value of the
underlying field to become "yes". The code I am using to do this is as
follows:

Me.Order_Complete = True

When the code runs, it gives an error message:

run-time error '2147352567(80020009)'
You can't assign a value to this object

Why can't the value of this control be changed?
Thanks!

 

[Jeff Boyce]

Can you confirm that it is the checkbox control and not its label that you
are attempting to set the value for?

Have you tried using the "!" instead of the "." ... that is,

Me!Order_Complete

Have you tried giving the checkbox control a name different than the name of
the field (e.g., chkOrder_Complete) and then setting THAT control to True?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Phil Freihofner]

I think, I'm not sure, but by the time the OnClose Event occurs, the
underlying data table is no longer attached to the form. You might try using
the form's "Unload" event as a trigger instead, as this is a bit early in the
sequence of things.

In the opposite direction, I do know that one normally doesn't have access
to the values in the form's controls at the "Open" event, but they are
present at the "Load" event. So maybe it is the same during the closing
process.

Best of luck!

 

[John Spencer]

Normally I would expect to take that type of action in the form's before
update event and not in the Close event.

I would guess that you have a form + subform arrangement and that is the
problem you are trying to overcome. If using the UNLOAD event does not work I
would try running an SQL update query. You would need to store the primary
key of the main form in a variable.

Roughly the code might look like the following UNTESTED code.

DIM PKValue as Long 'Assuming the primary key is a number

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Store the current value of the primary key
PKValue = Me.PkField
End Sub

In the Unload event or the close event try the following.
Private Sub Form_Unload(Cancel As Integer)
'Build and execute an update query to update the field on the
'specified record.
Dim StrSQL as string
strSQL = "UPDATE [OrdersTable] SET [Order Complete] = True" & _
"WHERE [PkField] = " & PkValue
CurrentDb().Execute strSQL
End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County