Attaching a subform record to a parent record.

[pvdalen]

Howdy,

I have two tables, Customer and Equipment, that are joined by a 1-to-many
relationship, as each customer can have many pieces of equipment.

I have a form with the customer data; attached via a subform are the pieces
of equipment for each customer. There are no issues within the subform
adding or updating an equipment item for a customer.

The issue I'm having is this: I want to be able to access a customer record
in the main form, then, through the equipment subform, locate an unattached
piece of equipment, then attach it to the customer.

I can search the database for the piece of equipment using a unique
identifier and copy that record to the subform just fine. However, when I
try to get the system to attach the equipment to the customer, I get messages
stating that I can't create a record because of a primary key violation.
Which is quite right; I don't want to add a new equipment record to the
equipment table, I want to attach an existing one to a customer on the parent
form. Using any of the navigation buttons or existing command buttons (Add
New, Save, etc) gives me the same messages.

I tried to code a command button that just attaches the customer ID FK to
the existing equipment record, then requerying the form, but when the system
attempts the requery, I get a message indicating it can't perform the
function because the record is locked.

So now I'm a little stuck. I know I need to not just retrieve the data of
the equipment, but point to that record while I'm processing. Is what I'm
trying to do impossible because of the filter in place when viewing the
customer and equipment already attached to that customer?

I hope this makes sense and thanks very much in advance.