Reassigning records

How it looks right now is I have a form and a sub-form.  On the form is the parent record and the sub-form lists all the child records.  Linked by the ID on the parent form.  The Parent from is set to Single form and the sub form is set to continuous form to display all records associated with the parent record.

I can add the drill down with a list of all the assets names from the parent table to be displayed on the sub form.  That part is work fine.
Adding a button and the VBA behind was/IS the tricky part.

I would imagine I could do something like this for the On-click event:

Dim tmp_HWID as string
tmp_HWID = me.cbo_Asset.column(1)
me.HWID = tmp_HWID
requery or refresh.

Or it could be
me.HWID = me.cbo_Asset.column(1)
docmd.refresh

thoughts?

John

can you provide a screenshot of your form as it currently exists?

All you have to do to "move" the record is to change its foreign key.  If the only view you have of the components is from the "parent" to a subform, then the interface will be a little awkward.  you would need to add a combo to the subform that allows you to choose a new parent.  This control should not be bound.  The afterUpdate event of this control would need two lines of code.

Me.theforeignkey = Me.cboNewParentID
Me.Requery

The requery will be a little disconcerting because it will cause the record you just modified to disappear.  However, if you change to the parent you moved it to, you will see it there.

This will be a smoother operation if you have a view of the data that simply looks at the components.  In that form, there will be abound combo that shows the current parent.  you would just pick another option from the list.

The component view would make sense if you were dealing with things like cars or computers or phones that were assigned to individuals but could be moved to a different person.

Do you have any need to keep a historical record of where the component has been?

Pat and Dale,

I never even considered the historical aspect of it, but I do have an audit procedure that tracks changes and places them in their own table.
What were your thoughts on the historical aspect of it?

The equipment I am dealing with is computers.

Our SAs move components around a lot.  Sometimes they move hard drives from one machine to another and we need to track that.  Having the ability to move that hard drive from one machine to another will save a ton in duplicated work.  Right now they recreate the record and mark the old one as archived.  Which become really confusing when you look at reports.

So changing the HW_ID and then re querying might be the way to go.

Attached is a screenshot of what I am working with.  The left hand side is where the HW_ID is displayed for this particular record it is 52.
Doc1.docx

The solutions provided worked out great.  Both work and I was able to use them both on seperate areas within the app.  Thanks