How to "update" a check box bound to an expression 1

[ChrisCarroll]

I have the following:

A form showing a purchase order
A subform with a list of books on that purchase order
A checkbox on the subform with controlsource="Iif(Location=2, False, True)" which indicates whether the book is still on order (location=2) or arrived (location=3)

I want: To be able to tick the checkbox to indicate that a book has arrived

Obvious there is the problem that the checkbox is bound to an expression, not a field, so I put code in chkArrived_Click():
If Location=2 Then
Location=3
Elsif Location=3
Location=2
End If
But the Click event doesn't fire when I click on the checkbox. This was a disappointment to me.

I can't use an unbound checkbox (I assume?) because in a list of books on the subform (some arrived, some not) I wouldn't know how to set the value of the checkbox for each row except by binding it.

I even tried putting a transparent box over the checkbox hoping I could get a click event off that, but that didn't register any click events either.

How do I do this?

Thanks,

Chris

 

[PCLewis]

Hi,

You are wrong I think. I cannot see any reason for the click event not to fire. If you have double and triple checked that it does not fire (breakpoint on the first line, or debug.print on the first line - whatever) then I'm stumped on that part.

You have a checkbox on a subform with a list of books. So I can visualise 10 checkboxes if there are 10 books in the list. What you are trying to do is display to the user the status of whether a book has arrived or not, whilst at the same time use the same control as a data input mechanism. This would be find if you were able to bind the checkbox directly to field data. Because you can't you really need to go about things differently.

What I would do, depending on how I wanted things to look:
You already have a Location field in your data so you should Hide it. Create a new text box and bind it to something like:
=Iif(Location=2, "Arrived", "Backorder")

Then make that field locked so the user can't edit it. They have no need to. Now you can have your check box unbound and in the click event, update the [Location] as you have in your click code. Make sure that the On_Current event in your subform reads the value of Location to set the state of the checkbox properly. Don't bind the checkbox at all.
Something like: Checkbox.value=([Location]<>2)

I might use a toggle button rather than a check box in this case for the simple fact that I always think of check boxes as optional choices. Whilst a toggle button looks like a button it's really just the same as a check box but it gives the impression to the user that "if you click here, something will change". You can also change the label of the toggle button depending on what action pushing it will have.

 

[ChrisCarroll]

PCLewis, Thanks for getting me started, sorry for slow reaction. So:

(1) You have correctly visualised my form and explained what I'm trying to do.
(2) I've unbound the checkbox and now the click event fires instead of beeping and saying that it's bound to an expression.
(3) I've put a txtLocation on the form, bound simply to Location and for debugging purposes I currently leave this textbox visible and enabled.
(5) I put this on the form Current event:
Me!chkArrived.Value = (Me!txtLocation.Value = 3)

Now I tick the chkArrived tickbox. All 10 tickboxes are simulaneously ticked. The txtLocation is updated when I leave the record. As I scroll up and down, all tickboxes tick and clear in unison to show the value of Location for the record I'm on. Cute but not what we aimed for.
------
This makes me feel that I really do want the checkbox bound to the Location field; and want instead to deal with this problem of the Click method not running?
Or, try again with the invisible control over the checkbox?
Thanks,
Chris

 

[PCLewis]

Good point. I forgot about that behaviour on a continuous form.

Hmm, the click event won't work for your checkbox when bound to an expression but the mousedown/mouseup does. Perhaps you can use the mousedown event to do what you want. User clicks on the checkbox, and in code you change the value of location like in your original post:

If Location=2 Then
Location=3
Elsif Location=3
Location=2
End If

Take care about when location is not a 2 or 3 of course.

This will cause the checkbox to alter state because it is bound:
= ([Location] = 3)

If the Location field has only 2 states however (Arrived or not Arrived), you might consider replacing or adding the field definition. You lose very little in having a Location field as well as an "Arrived" field. Adding this yes/no field to your table would solve all of your problems. This would be my preference if it were my task. It would make reporting on items not arrived easier too.