none
Setting checkbox visibility property to false causes run time error RRS feed

  • Question

  • Hi there, 

    I have a medium complex database (25 tables) and form (6 subforms), with a lot of VBA in the background.

    In trying to reproduce a nagging bug, I narrowed it down to the visibility property of a bound checkbox located on the form.

    When the user adds a product to the database through the form, VBA code in the backgrounds runs many checks. If all checks pass, the value of a hidden bound checkbox is set to True.

    If the visibility of this checkbox is False, I get a run-time error 7878 (the data has changed).
    If the visibility of this checkbox is True, everything works perfectly.

    Of course the easy fix is keeping the checkbox visible, but I would prefer the correct fix. Any idea why this is happening and how to fix it?

    Thank you so much!

    Friday, November 8, 2019 8:42 PM

All replies

  • I don't believe ACCESS lets you set the value of a hidden true/false checkbox control. It has to be visible. Of course you could always set the height and width to something like .01 and then hide it behind another control with background set to transparent.
    Friday, November 8, 2019 10:00 PM
  • I just tried replicating this issue in a new database and couldn't get the error to trigger.

    I created a simple table with 2 boolean fields, then created a basic form with the checkboxes and 2 command buttons.

    1 checkbox was visible and the other was hidden.

    On each of the command buttons, I added code to set both check boxes to true or false (respectively).

    This worked as intended and changed the records accordingly (both checkboxes) without any error messages.

    Have you tried something similar?

    Is there any other code running before or after the update that may be causing the issue? What about stepping through the code via the debugger, what line of code does the error trigger on?

    Edit: test was done in Access 2016
    Saturday, November 9, 2019 1:37 AM
  • Ok interesting. No I had not tried to create a more simple database to test.

    On my end, if I perform the exact same sequence of actions where the only difference is whether the checkbox is visible, I get no error when it is visible, and I get a run-time error on the line of code where its value gets updated if it is no visible;

    Me.chkchild_titles_up_to_date.Value = 1

    I tried adding If Me.Dirty Then Me.Dirty = False just upstream, but it does not trigger since Dirty returns False, so problem persists.

    ..Still searching...

    Saturday, November 9, 2019 2:34 AM
  • Well, one interesting way to get a error in this "area" is if the control in question has the focus and THEN you set it to visible = false.

    So, you might want to "move" the focus away from those controls before any routine runs that hides/shows the controls. Often I just choose say the first control. But, you need/want to ensure that any control being hidden does not have the focus when you do this. 

    So, try adding some code to "move" the focus away from the check box(s).

    eg:  me.SomeTextBox.SetFocus

    --- now run your code

    The other possible issue would be if your database is SQL server. In these cases, then if you have ANY bit fields, make sure that a default is specified as 0 for such columns.  The reason is a "long time" issue with null bit fields and SQL server. If such columns are null, then you get errors about someone else having changed the data when that's not the case. So, a big detail here would be if SQL server is involved. (if yes, in addition to checking if bit columns have a default set, you also want to add a timestamp column. 

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Saturday, November 9, 2019 7:58 AM
  • -1 = True in ACCESS and 0 = False so try

    Me.chkchild_titles_up_to_date = -1 for True

    Saturday, November 9, 2019 2:07 PM