Trapping Click With Calculated Check Box

[Neil]

I have a check box on a form that's bound to a function that returns a
True/False value. When the user clicks on the check box, I run some code
through the MouseDown event. Everything works fine.

Only problem is: when the user clicks on it, there's a beep, and then they
get a status bar message: "Control can't be
edited; it's bound to the expression...." That's very annoying and
disconcerting.

Is there a way to trap the click and avoid the message? The Access online
help says that MouseDown can't be canceled. And OnClick and BeforeUpdate
don't seem to be called when it's a calculated control. Anyone have any
ideas on how to work around this?

I would put a command button on top of the check box; but the users need to
be able to work with it in Datasheet View, and command buttons don't appear
there.

Any ideas?

Thanks!

Neil

 

[Alex Dybenko]

Hi,
once you have a control with function in control source - you can't update
it. instead you can set a default value property to this function, and leave
control source blank (or bind to a updatable field)

command buttons does not appear on datasheet view, but you can use a
continuous form for this

 

[Neil]

Yes, I know you can't update a calculated control. What I wrote was that I
handle any updating through the MouseDown event. What I am trying to do is
trap the message that I noted in my original post so that the user does not
receive it.

And, yes, Continuous Forms would work, but the client insists on Datasheet
View (they like being able to hide and show columns, resize columns, and
change the order of columns).

Neil

 

[Douglas J. Steele]

What's the code that you're running, and what is it supposed to be doing?

 

[Neil]

It basically adds an item to a collection to store the "checked" values, and
removes an item for "unchecked" values. The check box, itself, is bound to
the formula "=IsSelected([ID])" where IsSelected() returns True or False,
based on whether or not the item's in the collection.

Thus, when the user clicks on the check box, the MouseDown event is
triggered, which toggles whether or not the record is represented in the
collection. Unfortunately, it also triggers the message I noted, saying you
can't update the control. If I can just get rid of that message, it would be
fine.

Neil

 

[Douglas J. Steele]

And what's the actual code you're using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It basically adds an item to a collection to store the "checked" values,
and removes an item for "unchecked" values. The check box, itself, is
bound to the formula "=IsSelected([ID])" where IsSelected() returns True
or False, based on whether or not the item's in the collection.

Thus, when the user clicks on the check box, the MouseDown event is
triggered, which toggles whether or not the record is represented in the
collection. Unfortunately, it also triggers the message I noted, saying
you can't update the control. If I can just get rid of that message, it
would be fine.

Neil

What's the code that you're running, and what is it supposed to be doing?

 

[Alex Dybenko]

i am not sure, but i think MouseDown event also cause checkbox update, that
is why you get error message

as for datasheet with buttons - you can do this in Access. but you can try
to use some activex control, for example with janus grid you can do this:
www.janusys.com

 

[Neil]

Below is the code I'm using. It's adapted from Albert Kallal's multi-select
example, except that his uses a command button on top of the check box
(which avoids the problem I'm having, but doesn't work in Datasheet View).
The check box is bound to "=IsChecked([Index])", where Index is the PK of
the record.

Neil

Dim colSelect As New Collection

Private Sub ExportSel_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)

ExportSelCheck

End Sub

Private Sub ExportSel_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = vbKeySpace Then
KeyCode = 0
ExportSelCheck
End If

End Sub

Private Sub ExportSelCheck()

DoCmd.Echo False

If Not IsChecked(Me!Index) Then
colSelect.Add CLng(Me!Index), CStr(Me!Index)
Else
colSelect.Remove (CStr(Me!Index))
End If

Me.ExportSel.Requery

DoCmd.Echo True

End Sub

Public Function IsChecked(vID As Variant) As Boolean

Dim lngID As Long

If Not IsMember(vID) Then Exit Function

lngID = colSelect(CStr(vID))
If lngID <> 0 Then
IsChecked = True
End If

End Function

Private Function IsMember(vID) As Boolean

Dim var As Variant

For Each var In colSelect
If var = vID Then
IsMember = True
Exit For
End If
Next

End Function

And what's the actual code you're using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It basically adds an item to a collection to store the "checked" values,
and removes an item for "unchecked" values. The check box, itself, is
bound to the formula "=IsSelected([ID])" where IsSelected() returns True
or False, based on whether or not the item's in the collection.

Thus, when the user clicks on the check box, the MouseDown event is
triggered, which toggles whether or not the record is represented in the
collection. Unfortunately, it also triggers the message I noted, saying
you can't update the control. If I can just get rid of that message, it
would be fine.

Neil

What's the code that you're running, and what is it supposed to be
doing?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a check box on a form that's bound to a function that returns a
True/False value. When the user clicks on the check box, I run some code
through the MouseDown event. Everything works fine.

Only problem is: when the user clicks on it, there's a beep, and then
they get a status bar message: "Control can't be
edited; it's bound to the expression...." That's very annoying and
disconcerting.

Is there a way to trap the click and avoid the message? The Access
online help says that MouseDown can't be canceled. And OnClick and
BeforeUpdate don't seem to be called when it's a calculated control.
Anyone have any ideas on how to work around this?

I would put a command button on top of the check box; but the users
need to be able to work with it in Datasheet View, and command buttons
don't appear there.

Any ideas?

Thanks!

Neil