• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

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 Sheehy
John Sheehy
  • 3
  • 2
  • 2
2 Solutions
Dale FyeCommented:
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 SheehySecurity AnalystAuthor 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 FyeCommented:
can you provide a screenshot of your form as it currently exists?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 SheehySecurity AnalystAuthor 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.
John SheehySecurity AnalystAuthor Commented:
The solutions provided worked out great.  Both work and I was able to use them both on seperate areas within the app.  Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now