Need help with primary key problem

[mitchem1]

I am attempting to convert an inventory database to Access. Most inventory items have a unique ID. However, there are various misc. items that do not. For example, there may be 60 carpenter levels each assigned the ID m00012. My problem is that the user wants an error message when he attempts to assign an inventory item that is already assigned elsewhere. He doesn't want to be bothered, however, when assigning misc. items. Thanks, and hope this wasn't too confusing.

 

[cpod]

Do the misc. items all have IDs starting with "m"? If so all you have to do is something like:

if left(me!IDtextbox,1)<>"m" then
if dcount("*","yourtablename","ID = '" & me!IDtextbox & "'")>0 then
msgbox "Duplicate ID"
end if
end if

 

[mitchem1]

Yes cpod, all of the misc. items have a similar ID, so I will give that a shot. Thanks a lot.