(Access 2007)

I am modifying the input method of JobCodes. It used to be that we were given a list of ItemNos for a JobCode, found the Item records in the database with a search function (another part of the .mdb) to manually enter in the same JobCode to the relevant record fields.

The purpose of this form/subform is to enter the JobCodes into a series of ItemNos with better consistency (we had trouble with some JobCodes being inputted improperly so now we store the JobCodes in a table). We would only have to set JobCodes and input the ItemNos in the subform, adding the JobCodes to the relevant Items record automatically.

This section of the database looks like:

Jobs (JobCode) -One-to-Many-> Items (ItemNo, JobCode)

(No Enforced Referential Integrity)

I have a form (frmJobList) that navigates by 'JobCode' with an attached datasheet view subform that displays all Item records with the same JobCode found in the parent Form. Master/Child fields are properly linked, and that part of the database works fine.

However, when I type in a new ItemNo into the subform to add it to the list of Items that have the JobCode, the database acts as if I'm adding a new Item and promptly errors out (duplicates of ItemNo, which is the Primary Key for Items).

Seems like I'm not invoking Autolookup properly. I know that this method can work as I've seen it done in the same database.

Thanks in advance for any help.