Having problem with button click event in Access 2016 form RRS feed

  • Question

  • I am running Access 2016 on Windows Server 2012.

    My Access database is basically a few forms (and associated code) and some linked tables with three queries (for the forms).

    The forms are not bound, they are basically data entry forms for printing labels.

    The user chooses a product type, then chooses a part number, then a date, a quantity, and then chooses which labels (using check boxes) to print.

    The check boxes are not "grouped" together, because I want the user to be able to print any combination of labels, however I do  want to require that at least one of the boxes is checked. The boxes are all enabled and checked by default, so the user MUST uncheck them to stop a particular label from printing.

    When the user clicks the command button to "Print", the Click event is triggered.

    The click event does this (in plain English):

    if chkbox_box_label is checked, set box_label to "YES" otherwise, set it to "NO"

    If chkbox_unit_label is checked, set unit_label to "YES" otherwise, set it to "NO"

    If chkbox_date_label is checked, set date_label to "YES" otherwise, set it to "NO"

    If box_label is NO, AND unit_label is NO, AND date_label is NO (no boxes are checked), then I want to present an error message and stop here, allow the user to select a box and click Print again.

    If at least one of them is "YES", then I want to continue by inserting the appropriate date into the database and clear the form.

    What I have run into is this (in plain English):

    If I choose the product type, part number, date, quantity and click print (no change to any check boxes), it behaves as expected.

    If I choose the product type, part number, date, quantity and un-check one or even two of the boxes, it works as expected.

    If I un-check all three boxes and click the command button, I get my error message (using MsgBox function) and I click OK, returning me to the data entry form. Upon returning to the form, I am able to check the boxes (they are enabled), but clicking the command button seems to result in the code just repeating the same error message regardless of which boxes I check.

    I have been working with different things to get the code to tell me what is going on, form adding breakpoints to the code, to adding additional MsgBox commands to monitor how things are going, but nothing has helped.

    I am currently using If statements which in my mind was the simplest way to get the code working, but it still is not working.

    Can someone give me some ideas where to look to figure out what I am doing wrong, and why the whole "Click" event is not running like it should?

    Wednesday, September 26, 2018 7:37 PM


  • Well, I managed to figure it out and get it to working.

    I had to use breakpoints extensively to check the value of variables, and had to change my If statements a bit.

    First, I discovered that with the check boxes having a default value of 1 (checked), when I uncheck them they go to 0, and then interestingly they go to -1 when I check them again.

    That is what was causing my problem... I was testing to see if each of the boxes was 1 (checked), but as noted above they were only 1 the first time I tested them.

    So, the solution was to test for them to be 0 (unchecked), and assign "NO" to the proper variable.

    Then I used a multi-condition If...Then... to test and see if there was at least one box checked:

    ' Get the values for the check boxes and set the values for the record
    If CHK_BOX.Value = 0 Then box_label = "NO" Else box_label = "YES"
    If CHK_Unit.Value = 0 Then unit_label = "NO" Else unit_label = "YES"
    ' Present error message if one of the boxes is not clicked
    If box_label = "NO" And unit_label = "NO" Then
        Err = MsgBox("Must select at least one form to print", vbOKOnly, "ERROR")
        printit = False
        printit = True
    End If
    • Marked as answer by Lee Willmann Thursday, September 27, 2018 2:33 AM
    Thursday, September 27, 2018 2:33 AM