BeforeUpdate firing repeatedly 1

[VicM]

Hi,
I'm using MSAccess 2010 on a Win7 Pro box

My DB keeps track of books that I've read, whether from library, ebook, ones I've purchased or others have given me.

I have one main form with no subforms on it.

There are checkboxes for friends & relatives to whom I loan books or receive books from. In the BeforeUpdate of the 'To' checkbox I check whether the book was a library book or ebook, both of which I cannot lend out; so therefore I need to cancel the checkbox event. When I do accidentally check a 'To' box for an illegal loan, the BeforeUpdate appears to work properly.
However, back at the main form, even tho the 'To' checkbox is empty, when I select any box or button there, the previous BeforeUpdate keeps firing. Nothing else works; it's in an apparent loop. I have to put the form into design mode; but when I try doing that I still get a runtime error '2467', "The expression you entered refers to an object that is closed or doesn't exist."
I click on the 'End' in the error box and receive another message saying to the effect that I can't save the record at this time; do I want to close the object anyway.
Here is the code for the BeforeUpdate event:

Private Sub To_BarbK_BeforeUpdate(Cancel As Integer)

If Me.[Borrowed From Public Library] = True Or Me.Ebook = True Then
    
    Cancel = True
    
End If

    Beep
    MsgBox "You can't give a library book or Ebook to Barb", vbOKOnly, "Not Allowed"


End Sub

I have tried moving the last 2 statements both before and after the Cancel statement in the If block with no change in results.

Any suggestions are welcome.
Thanks,
Vic

 

[dhookom]

I would probably disable the To check boxes once Me.[Borrowed From Public Library] = True Or Me.Ebook = True. Block your errors before they occur.

BTW: I believe your table structure is un-normalized. If you add more friends or relatives, do you have to add more fields?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[VicM]

dhookom,
Thanks for your suggestions. I think I'll try that approach of disabling the check boxes for library and ebooks.
As for not being normalized, I hear you. While the number of relatives/friends will probably not increase (and more likely decrease) in the future, I'd rather not redesign the form and tables at this time.
Thanks again for your suggestions. Very helpful.
Vic