AfterInsert Event - want to update a field in subform table from main form table

[Guest]

I have two tables

Parent is "Members" and child is "Attenance
The main form contains one record for "Members" and the "CoreGroup" field is displayed (and updateable) in the "CoreGroup" listbox object on the "Members" form.

The "Attendance" subform is a list of all attendance records for each Member.
When I insert a new attendance record, I want to update that record's "CoreGroup" field with the current "Members.CoreGroup" field (or object). I am doing this to track the CoreGroups assigned to each attendance record. I want to store actual text name of the CoreGroup, not the ID

Thanks in advance
Kelly

 

[Joe Black]

I have two tables:

Parent is "Members" and child is "Attenance"
The main form contains one record for "Members" and the "CoreGroup" field

is displayed (and updateable) in the "CoreGroup" listbox object on the
"Members" form.

The "Attendance" subform is a list of all attendance records for each Member.
When I insert a new attendance record, I want to update that record's

"CoreGroup" field with the current "Members.CoreGroup" field (or object). I
am doing this to track the CoreGroups assigned to each attendance record. I
want to store actual text name of the CoreGroup, not the ID.

Thanks in advance!
Kelly

Hi Kelly

You could use the following code in the before insert event of the
Attendance subform (assuming the name of the listbox on the Members form is
called "CoreGroup", and that the text name of the core group is in the
second column of the listbox)

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CoreGroup = Nz(Me.Parent.Form.CoreGroup.Column(1), "No core group
selected")
End Sub

Regard - Joe

 

[Guest]

I'm getting a 2465 error - Application-defined or Object-defined error - which doesn't really tell me much.

In my main form - CareGroup has - "CareGroupTypeID" and "CareGroup" in the combolistbox
In my subform - CareGroup is just "CareGroup

I want to copy "CareGroup" from the main form into "CareGroup" on the subform when inserting a new record in the subform

Thanks - Kelly

 

[Joe Black]

I'm getting a 2465 error - Application-defined or Object-defined error -

which doesn't really tell me much.

In my main form - CareGroup has - "CareGroupTypeID" and "CareGroup" in the combolistbox.
In my subform - CareGroup is just "CareGroup"

I want to copy "CareGroup" from the main form into "CareGroup" on the

subform when inserting a new record in the subform.

Thanks - Kelly

Hi Kelly

Your description doesn't really tell me much either.

Did you put the code in the before insert event of the Attendance subform?
What line in the code I supplied causes the error (which line is highlighted
when the code stops)?
What is the name of your combolistbox (in your first post it was a "listbox
object") on your main form?

Regards - Joe

 

[Guest]

Here is the code in it's entirety that I put under the Attendance Subform's BeforeInsert Event:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CoreGroup = Nz(Me.Parent.Form.CoreGroupTypeID.Column(1), "No core group Selected ")
End Sub

In the Main form called "Members" is a combo box that contains the data from the CareGroup table - it contains two fields - the first is "CareGroupTypeID" which is hidden when the combo box is chosen from - and the second is "CareGroup" which is the text description of what "CareGroupTypeID" represents. I want to save the second field in the combo box that has already been chosen in the main form - to be saved to the "CareGroup" field in the "Attendance" subform when adding a new attendance record.

I've looked all over trying to find an example of this and can't. I am trying to track what CareGroup a member belonged to on a particular date. If the CareGroup changes on a Member record, and I didn't have it stored in that Member's Attendance record - I would lose track of what former CareGroup's that Member belonged to. I wish I could show you the form - but don't know how to paste it into this message. If you would like I can email it to you.

I've looked all over trying to find an example of this and can't - which is, how do you pull data from a main form field into a subform field and have it save to the subform field's underlying table using an event.

Thanks - Kelly

 

[Joe Black]

Here is the code in it's entirety that I put under the Attendance Subform's BeforeInsert Event:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CoreGroup = Nz(Me.Parent.Form.CoreGroupTypeID.Column(1), "No core group Selected ")
End Sub

In the Main form called "Members" is a combo box that contains the data

from the CareGroup table - it contains two fields - the first is
"CareGroupTypeID" which is hidden when the combo box is chosen from - and
the second is "CareGroup" which is the text description of what
"CareGroupTypeID" represents. I want to save the second field in the combo
box that has already been chosen in the main form - to be saved to the
"CareGroup" field in the "Attendance" subform when adding a new attendance
record.

I've looked all over trying to find an example of this and can't. I am

trying to track what CareGroup a member belonged to on a particular date.
If the CareGroup changes on a Member record, and I didn't have it stored in
that Member's Attendance record - I would lose track of what former
CareGroup's that Member belonged to. I wish I could show you the form - but
don't know how to paste it into this message. If you would like I can email
it to you.

I've looked all over trying to find an example of this and can't - which

is, how do you pull data from a main form field into a subform field and
have it save to the subform field's underlying table using an event.

Thanks - Kelly

Hi Kelly

It would be more helpful if your descriptions were consistent. In your first
post the field was CoreGroup and now it is CareGroup
If it is CareGroup then the code:
Me!CoreGroup = Nz(Me.Parent.Form.CoreGroupTypeID.Column(1), "No core group
Selected ")
will not work.

Also, you didn't answer my question:
What is the name of your combolistbox (in your first post it was a
"listboxobject") on your main form?
In the code you posted you have the name of the combo box as
"CoreGroupTypeID". Check that it is actually called that and not something
like "Combo27". It needs to be the actual name of the combo box so if it is
not named "CoreGroupTypeID" then change "CoreGroupTypeID" in the code to
whatever the actual combo box name is.

Also, if the name of the field is "CareGroup" then change "Me!CoreGroup" to
"Me!CareGroup"

Regards - Joe