Reassigning records

I want to be able to reassign a record from one master to another.

For example.  I have a piece of equipment with an ID of 250.  And I have 4 other pieces of equipment that are associated to the equipment with ID 250.  (One to many relationship)  I want to move one of the four pieces from ID 250 to ID 251  How do I go about doing that?

What i have envisioned is a drill down with a list of the equipment and button that says move.  

John SheehySystem Security ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
How is your data organized?  Is the table hierarchical, (ID, ParentID) or do you have a second table that defines relationships, which would allow the same part to be associated with multiple assemblies (it sounds like the former).

Either way, you simply need to change the parentID associated with the part, but you have to have a way of identifying the new parent, maybe in a combo box or a listbox?

You might do this with a treeview to select the part you want to reassign, but then need some method to identify the new parent, and then generally a button which would execute an update query, something like:

UPDATE yourTable
SET [ParentID] = Form!yourForm.NewParent
WHERE yourTable.ID = Form!yourForm.SelectedID
John SheehySystem Security ManagerAuthor Commented:
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)


Dale FyeOwner, Developing Solutions LLCCommented:
can you provide a screenshot of your form as it currently exists?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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?
John SheehySystem Security ManagerAuthor Commented:
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.
You have two levels where you probably need history.  The top level is moved from one person to another by changing the Assigned Employee.  There is no need to requery since this is the main form.  The subform is where you need the ability to change the HardwareID so on that form i would add a combo where you could select a target device and here you would use a requery to see the original component disappear.  If you have a view that shows the components, it might be less disconcerting to make this kind of change from that form.  

In both cases you will want to log either the before or after images of the record.  Since this is essntially an add and a delete operation, you will need to include enough information in your change log to identify the before and after foreign keys.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySystem Security ManagerAuthor Commented:
The solutions provided worked out great.  Both work and I was able to use them both on seperate areas within the app.  Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.