I'm curious, is PartID an autonumber? Or something the user is entering?
If you're entering it, why not just set the table field property to not allow duplicates?

Also, in your DLookup, what kind of field is LegoNumber? You are treating it like it's a string.
I don't see any code to test the PartID if you didn't handle it in the table.

Hi Thomas

To answer your questions the PartID is a autonumber. LegoNumber is a short text field. The code works fine I just want to clear the LegoNumber field after the Message box is closed.

@ MD This code appears to be in a form. Your PartsT table seems to have a PartID field. Then you dim a variable to have the same name as the field (PartID)? Even if it compiles, I'm guessing Access is going to be very confused when you try to set a value in its autonumber field.

I was curious, so I created a simple database to see what would happen in this scenario. Access seems to evaluate the line of code that tries to overwrite its AutoNumber by setting the variable but not updating the source field. The code continues.
Adding this line after the Cancel statement seems to work:

LegoNumber = ""

When designing the table, I sent the LegoNumber field as Required, so it wouldn't allow for a Null (gave a RTE).

Then for fun, and to refresh my memory, I tried to do the same thing (duplicating a field that has Indexed: Yes (No Duplicates)) and it worked as expected for the Entity Form in the ADS.
Notice the lack of a message box and that the status area error message (029) appears after entry of the field, not when the form tries to save. The cursor remains in the entry field and the backcolor changes to one indicating "Here is your error!" The status message tells you exactly what field is duplicating to exactly what record.

Want to see it in Spanish?

And for those that are saying "Oh no, not that ADS thing again!" It's for me as much as you. Every time I give a demo or answer a question like this, I find some little bug that needs attention. In the Spanish version, see how "Related" on the right didn't get translated to Spanish. It should say Relacionado.  Fixed thanks to MD making me go the extra mile.

Thank you Thomas for your help. I found a fix that works for me. In Richard's Tech Help video Warn but Allow he uses the Undo command.

@MD In my experience, undo puts ALL the fields back to where they were before the form went dirty.
In your case, depending on the state of the table before your form runs, you may be allowing a duplicate back into the table with an undo. For that reason, I do an error check Before_Update on an entry field (#028 in the English form's status area above), and I check ALL the fields at Before_Udate for the form. The only catch I've found, if the form isn't dirty, then the Before_Update for the form doesn't fire off. So, I add an option to check all the fields when an "Entry" button is pressed (to start making changes to a record after previewing it) and I have that funny little button with three lines at the top of the form, and that too will validate all the fields. Lots of stuff going on to try and make a form bullet-proof.