DLookup For Duplicate Entries and MessageBox Response Question (1 Viewer)

(This code isn't mine, I found it online, and it was probably here)
I put this code in the Before Update event on the user form for the LotNumber text box. As you can see from my note above, the original value for Cancel was True. In the case of LotNumber data there are rare occasions when its acceptable to reuse a Lot Number, so I wanted to prompt the user, but allow a duplicate as well, so No Duplicates is turned off at the table level. What was happening was that the message box would just come back up each time I moved to the next data entry item. I don't understand Access VBA very well, but by experimenting I found that by changing it to Cancel = False it performs as intended. Can anyone help me understand why?

Setting Cancel to True, would cancel the update operation, which wasn't your intention.

Does that make sense ?

Yes it does. Thank you. I need to study VBA more. My method of changing things in stuff I found online until I break it or it works, while often illuminating, leaves me in situations like this more often than I'd like.

This is odd because the default value for the Cancel parameter should be False. That is, the default should be to allow the update. I have no clue as to why you are seeing Cancel as True initially. Is there some unseen code (before the sample code) that would have established the Cancel value to be True? I think we are not seeing everything that is there. Like, the BeforeUpdate entry point and End Sub or Exit Sub exit point.

However, you should note that since your message box is using vbOKOnly then your If Response = vbOK Then ... will ALWAYS execute because you can never get another response than OK to that box. Look up the MessageBox function to see what you can use for that second parameter.

For your convenience, here is one reference link to message box behavior.

https://support.office.com/en-us/article/msgbox-function-e23511c8-4157-4e09-93a6-ba9f1749f4c0

Another suggestion: Instead of DLookup, use DCount with the same three parameters, then your FoundNotFound with be either zero or not zero and you will never run afoul of any nulls that might exist. DCount is more tolerant of unusual records and is therefore less likely to fall flat on its face.

This is odd because the default value for the Cancel parameter should be False. That is, the default should be to allow the update. I have no clue as to why you are seeing Cancel as False.

Click to expand...

The code I found online was setup as Cancel = True. It was applied to a similar but different need, so that may be where that came from. Thanks for the link, and I will give the DCount option a try.