CODEWITHSUNDEEP
ms-access
Humpton
Humpton

Reputation: 1519

Disable Check Box Based on Other Values

I have a relatively simple Continuous Form. There is an invisible Check Box, let's call it Frank.

There is second Check Box (Ralph) which may be True or False. There's also a text box with a date (RalphDate).

If Frank is True, I don't want the user to be able to change Ralph or RalphDate, regardless of what is already there.

Now, RalphDate is easy in that I just use a Conditional Format to check the value of Frank which either enables or disables RalphDate. This option is not available for a Check Box.

I'm now stuck, because using some Visual Basic sets the state of all the Ralph boxes the same based on the value of the first Frank value it comes across. The question therefore, is how do I disable only the associated Ralph Check Box based on the Frank Check Box being True?

Upvotes: 4

Views: 5645

Answers (4)

Kat
Kat

Reputation: 239

Set the control source of Ralph to a function, such as =GetCheckState(). Then create a function like this:

Public Function GetCheckState() as boolean
    If Frank then
        GetCheckState = True
    Else
        GetCheckState = False
    End If
End Function

This will control whether Ralph is checked or not based on Frank. Since the checkbox is bound to the function, it will not let you change the value by clicking it.

To react to the user clicking the box, add code to the On Mouse Up event:

Private Sub Ralph_MouseUp(Button As Integer, Shift As Integer, _
    X As Single, Y As Single)
    If Button = acLeftButton And Not Frank Then
        Frank = True
    End If
    Ralph.Requery
End Sub

The Click event seems to be entirely ignored when the control source is a function, so you must use On Mouse Up instead (On Mouse Down works as well if you prefer that). You can also add similar code to a KeyDown event if you want the box to check when users press the space bar or enter key.

The only downsides are:

  • The box is not greyed out when it's not able to be clicked.
  • Access will display a message in the status bar saying the control can't be edited because of the expression it's bound to when it's clicked on.

For the first point, you could pop up a message explaining it if you think people will be confused. For the second, there is probably a way around it, but I couldn't find it. The message still appears even when I tried setting my own status message in both the Mouse Down and Mouse Up events.

If having the box greyed out when it is disabled is very important to you then this won't work, but I was unhappy with the look and feel of the "simulated" check box that was suggested in the accepted answer. This method allowed me to control the functionality of the check box based on other records and still use an actual check box.

Upvotes: 0

David-W-Fenton
David-W-Fenton

Reputation: 23067

This is one of the drawbacks of continuous forms, and there's no way around it.

In general, I avoid editable continuous forms -- I use them only to display a list, and use an editable subform linked to the continuous form's PK to display the current data for editing.

This avoids all the problems with conditional display of particular controls, since you just display the data in the continuous form and can control exactly what is visible/enabled in the single detail subform.

Upvotes: 1

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

Solution 1

  • Update the Locked property instead of Enabled for ckRalph.
  • do that in the OnCurrent() event handler for the form.

The Locked property will prevent changes without visual cue. This should take care of the functional part, even though visually the checkbox will still look enabled, it can't be changed.

Solution 2

Another way is to simulate the checkbox by doing this:

  • use a textbox, call it fakeckRalph; make it a small square.
  • make its record source property something like '=getstate()'

  • create a getstate function that does something like:

    Public Function GetState() as string
    GetState = iif(ckRalph, "V", " ")
End Function

I suggest using a symbol font and a character to make the tick appear better in its box.
So now, if your Ralph is true, the checkbox will display the tick and if not, it will be empty.

To complete the trick, use conditional formatting on the textbox to disabled it when Franck is true.
To make the clicking more realistic, also change the cursor to the hand pointer.

The last things that's needed is to wire the the OnClick event from our fakeckRalph textbox so that we can toggle the state of Ralph and display it properly:

Private Sub OnClick()
    Ralph = Not Ralph
    fakeckRalph.Requery
End Sub

I've user similar techniques to show whether a record on a continuous form was locked (used a blue colour padlock symbol from one of the Windings fonts) or deletable, with a big red cross that could be clicked:

image 2 http://img9.imageshack.us/img9/6296/sshot2do5.png image 1 http://img8.imageshack.us/img8/4997/sshot1ya5.png

Upvotes: 4

DJ.
DJ.

Reputation: 16247

Short answer is you can't do that if you are using continuous forms. Changing the setting of a control changes it for all rows.

Upvotes: 0

Related Questions