Custom Checkbox

Question

Since it is not possible to increase the size of Access checkbox, I am trying to create a larger one using Toggle button bound to Yes/No field OnCall. This works fine on existing records using this code:

Private Sub Form_Current()   

    ChkOncall_AfterUpdate

End Sub

Private Sub ChkOnCall_AfterUpdate()

    If OnCall Then

        ChkOnCall.Caption = "✓"

    Else

        ChkOnCall.Caption = ""

    End If

End Sub

But am getting "Invalid use of Null" on a new record. I understand this is because the value of Yes/No field on a new record is Null which changes when something is entered in any field. Is there a way of changing the initial value to No? I have tried this code in Sub Form_Current before calling ChkOncall_AfterUpdate:

If OnCall = Null then

OnCall.value=0

end if

but does not work - on hovering over If OnCall it does show Null but for some reason ignores the next line.

Would appreciate your help in correcting this.

Answer 1

> If OnCall = Null then

This is SQL syntax, but you are in VBA. Change to:

If IsNull(OnCall) then

Answer 2

I set the default to False and then 0 but I still get the same error.

Answer 3

You might like to take a look at BigCheck.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file stores the TRUE and FALSE values in a conventional Boolean (Yes/No) column in the table.  In the form a text box using the WingDings font simulates a check box.  When the user clicks the control they are actually clicking a transparent button which toggles the value of a hidden check box bound to the Boolean column with:

    Me.chkMyBooleanField = Not Me.chkMyBooleanField

The dummy check box simulated by a text box has the following as its ControlSource property:

    =IIf([chkMyBooleanField],Chr(252),"")

252 is the ASCII value for the tick character in the WingDings font.

Answer 4

Why not set the Default value on the Table level to False. That should prevent the field from ever being Null.

Answer 5

How about change 'If OnCall Then' to

'If nz(OnCall, False) Then'