By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,958 Members | 1,165 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,958 IT Pros & Developers. It's quick & easy.

How to Re-parent a Record

P: n/a
Hello,

I have a form that contains several sub-forms corresponding to tables which
are conceptually children of the main table for the form. On this form, I
need to have a button that allows one of the child records to be
re-parented, that is to have one of its key fields changed to the value of a
different primary key in the parent table. I want the button to pop up some
kind of dialogue or additional form containing a combo box or something
similar that displays information from rows in the parent table and allows
the new key value to be selected. Upon return to the main form, the new key
value should be used to update the record in the child table, and that
record should disappear from the child table subform for the presently
displayed parent table record on the main form.

I am an experienced database developer, but a complete neophyte where MS
Access and VBA are concerned. Can anyone out there help get me started on
how something along these lines could be implemented?

Thanks in advance,
s/KAM

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Kevin.

If you use a Continuous Form (not Datasheet) for the subform, you can show a
Form Footer section. In this section, place an unbound combo (or text box)
where the user can select the new parent. It will look like this:
Reassign selected record to new parent: [ v]

In the AfterUpdate event procedure of this combo, reassign the foreign key
field, save the change, and requery the subform:

Private Sub cboNewParent_AfterUpdate
If Not IsNull(Me.cboNewParent) Then
Me.[NameOfYourForeignKeyHere] = Me.cboNewParent
Me.Dirty = False
Me.Requery
End If
End Sub

If you prefer to use a datasheet subform, it would be possible to code that
on the main form, by replacing "Me" with:
Me.[NameOfYourSubformControlHere].Form

BTW, you can help prevent crashes in Access if you represent your foreign
key field with a text box on the subform. Set its Visible property to No if
you wish. Theoretically this should not be necessary, the last couple of
versions of Access can crash sporadically if you don't.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin Myers" <Ke********@austin.rr.com> wrote in message
news:10*************@corp.supernews.com...

I have a form that contains several sub-forms corresponding to tables which are conceptually children of the main table for the form. On this form, I
need to have a button that allows one of the child records to be
re-parented, that is to have one of its key fields changed to the value of a different primary key in the parent table. I want the button to pop up some kind of dialogue or additional form containing a combo box or something
similar that displays information from rows in the parent table and allows
the new key value to be selected. Upon return to the main form, the new key value should be used to update the record in the child table, and that
record should disappear from the child table subform for the presently
displayed parent table record on the main form.

I am an experienced database developer, but a complete neophyte where MS
Access and VBA are concerned. Can anyone out there help get me started on
how something along these lines could be implemented?

Thanks in advance,
s/KAM

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.