Using a Subform to Create and/or Link a Record Depending on Whether it Already Exists

[Nurgle]

I'm sorry this is so long. I've edited a couple of times now, and cut it by 1/3 but haven't come up with a way to shorten it more without losing important bits.

I don't even know what to search for, so I'm sorry if this has been answered. I don't think I'm trying to do something too "out there" but on the other hand I can't find any examples for what I am trying to do (either saying it can't be done or here is how it is done).

The setup:
The user is creating (or modifying) an Internal Incident for a product that was out of specification. Each product has a unique lot number. Lots are in their own table, so the form for creating an internal incident has a subform for entering the lot numbers. This is working normally.

The problem:
If the user tries to use a lot number that already exists, it states that the record cannot be added because a duplicate is being created in a unique index.

First attempt at a solution:
I changed the lot number from a text box to a combo box. The user could still enter new lot numbers (so long as they didn't exist yet), but if they wanted to link to an existing lot either the drop-down was blank or, if there were lots to select, it not only didn't update the other fields to match the chosen lots it still gave the unique index error and promptly messed up the information for the hapless lot chosen (which I've finally at last realized relates to the bound column choice, but isn't the true issue).

Second attempt at a solution:
I wandered into the world of .Seek and .NoMatch and promptly was in way over my head. I can certainly get it to trigger whether or not the lot number exists, but have no clue what to do past that point.

I changed the lot number box from a bound control to an unbound control. On LostFocus, it tests to see if the control is Null, and if it isn't null, focus goes to the Product combo box. The macro there for GotFocus is where the Seek is performed, and the .NoMatch is set. But now I'm stuck with two problems: what to do with the matching information AND how to link the lot number back to the internal incident. In other words, I think I just blew up the complexity by orders of magnitude and I'm peering over the edge of the abyss.

What I really want to do:
This doesn't fit into searches either here or for Google, because I can't think of a simpler way to word it. What I want is for the user to simply type in the lot number for their new (or updated) internal incident, and when they go to the next control, if the lot already exists, populate the subform with the existing information and link that existing lot to the internal incident. If the lot doesn't exist, I want it to behave as I'm used to a subform doing, and create the new record for the lot, linked to the internal incident.

I don't want or expect anyone to hand me the answer, unless that answer is "No, you need a different approach because you are seriously breaking subforms." I just want to get a suggestion as to which direction to look, or if the answer really is through Seek and (as with about everything else in this project) I'm running up a brick wall because I'm a VBA newbie.

No code is provided because I don't have any that has come even remotely close to doing what I want.

[Rabbit]

You don't need a subform. If I understand correctly, one lot can have many products. In a situation like this, you use a drop down that lists the existing lots. But you set the combo box to allow items not in the list so they can type other values. And you use the Not In List Event to add new values.

Subforms are meant for the reverse situation. Where you have one parent record and you want to add many child records.