BeforeUpdate Subroutine on Checkbox

I posted a question last week asking “How to remove/Delete data when unchecking a Checkbox”. This question was answered, but in working the problem I came up with a different issue. I have explained what is happening below and ask for assistance figuring this out.
The objective:
If the Return checkbox is checked, and the date and time are entered this is a complete record.
If the “Returned” checkbox is checked, and for some reason it is decided to uncheck it, which would (hide) the “Return Date” or Return Time” controls this data needs to be removed. This keep the database from having data in the table that can’t be seen on the form.
Currently the code in the Before Update Subroutine is working backwards.
On Record one (1) the checkbox is checked and both “Return Date” and “Return Time” are empty. When unchecked the message box should not fire, but it does.
On Record two (2) the checkbox is checked and both “Return Date” and “Return Time” are entered. When unchecked the message box does not fire, and both controls are made invisible leaving the data in the fields. It should give a warning from the message box.
These two conditions are working backwards of what should happen. I have studied the chkReturned_BeforeUpdate code, but just don’t see where the problem is.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think you need to change the code in the following routine at the If statement

If Me!chkReturned.Value = 0 And Nz(Me.txtReturnDate.Value) = "" Then

If Me!chkReturned.Value = 0 And Nz(Me.txtReturnDate.Value, "") <> "" Then

Private Sub chkReturned_BeforeUpdate(Cancel As Integer)

    If Me!chkReturned.Value = 0 And Nz(Me.txtReturnDate.Value, "") <> "" Then
        If MsgBox("Unchecking this box will delete the Date & Time information." & vbCrLf & _
        "Do you wish to Continue?", vbYesNo + vbInformation, "Check Info") = vbYes Then
            Me.txtReturnDate.Value = Null
            Me.txtReturnTime.Value = Null
            Cancel = True
            'Me.txtReturnDate.SetFocus ' Unable to setfocus if not visible?
        End If
    End If

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinCaptainAuthor Commented:
I hope this is not asking to much. Can you explain the difference.
Me.txtReturnDate.Value, "") <> "" Then

"" = Null
<> = Not equal
txtReturnDate.Value, is null, and not equal to ?. I am not sure how to read the section after )
KevinCaptainAuthor Commented:
That worked great, Thank you for your help!
Trying to learn something new with each problem.
The Nz function has 2 arguments, the 2nd of which indicates what to return if the the value in the 1st argument is null. Since you didn't have this argument set, you were not getting a "" result returned if the value was null. I used <> "" afterwards because if the value of the field was not equal to "" then it has some value entered which you wanted to be cleared (set to "").
KevinCaptainAuthor Commented:
I understand.
Thank you for the solution and the explanation.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.