Adding record to subform

[Someone]

Hello

I have a parent form which has a three-page tab control. The form's
recordsource is connected to a table, and the information is on the first
tab. On the third tab is a subform in datasheet view. There is a
one-to-many relationship between the parent and subform (using an ID value
in each). The Link Master Fields and Link Child Fields are correctly set.

When changing the information in one of the fields on the first tab, I have
code that should update the subform with the change that occurred. However,
this isn't occurring.

Strangely, I had sorted this out in a post on 26/11/05 at 18:14 with Tina,
but it's now all gone wrong. I had tried out the following audit trail at
http://support.microsoft.com/default.aspx?scid=kb;en-us;197592 and decided
to get rid of it - that's when the code I had went wrong.

This is the code I'm using:

With Me.frm_A_Subform.Form.RecordsetClone
.AddNew
!Changes = ("Status Changed to " & Me.Status)
.Update
End With

The ID in the subform table isn't the same as the ID in the parent table.

What am I doing wrong please?

Thanks
M

 

[Rick Brandt]

Hello

I have a parent form which has a three-page tab control. The form's
recordsource is connected to a table, and the information is on the
first tab. On the third tab is a subform in datasheet view. There
is a one-to-many relationship between the parent and subform (using
an ID value in each). The Link Master Fields and Link Child Fields
are correctly set.
When changing the information in one of the fields on the first tab,
I have code that should update the subform with the change that
occurred. However, this isn't occurring.

Strangely, I had sorted this out in a post on 26/11/05 at 18:14 with
Tina, but it's now all gone wrong. I had tried out the following
audit trail at
http://support.microsoft.com/default.aspx?scid=kb;en-us;197592 and
decided to get rid of it - that's when the code I had went wrong.
This is the code I'm using:

With Me.frm_A_Subform.Form.RecordsetClone
.AddNew
!Changes = ("Status Changed to " & Me.Status)
.Update
End With

I see nothing in this code that would cause the subform to update. You need to
issue either a Refresh or a Requery on the subform if you want the changes to
show up immediately.

The ID in the subform table isn't the same as the ID in the parent
table.

I don't understand this statement. A properly configured subform will only show
child records that have the same values in the linking fields the names of which
are entered into the MasterLink and ChildLink properties. How can you see any
records in the subform if the ID field is not the same? Is ID not the linking
field?

 

[Someone]

I see nothing in this code that would cause the subform to update. You
need to issue either a Refresh or a Requery on the subform if you want the
changes to show up immediately.


I don't understand this statement. A properly configured subform will
only show child records that have the same values in the linking fields
the names of which are entered into the MasterLink and ChildLink
properties. How can you see any records in the subform if the ID field is
not the same? Is ID not the linking field?

What I meant is that when I look at the table related to the subform, the ID
doesn't match the ID in the main table. I understand that this is why the
records don't show up in the subform, but I don't understand where this is
going wrong, because I have a relationship set up between the two tables and
the link master and child fields are set.

I'm stumped.

 

[Rick Brandt]

What I meant is that when I look at the table related to the subform,
the ID doesn't match the ID in the main table. I understand that
this is why the records don't show up in the subform, but I don't
understand where this is going wrong, because I have a relationship
set up between the two tables and the link master and child fields
are set.
I'm stumped.

I don't know if a RecordsetClone created from a subform inherits anything from
the MasterLink and ChildLink properties (I suspect that it does not). The fact
of the matter is that you are no longer using the "form" you are manipulating a
Recordset that was copied from the form. In that case you need to explicitly
set the ID value rather than expecting it to automatically be populated
correctly.

 

[Someone]

Also, when using the following:

Me.Subform_Name.SetFocus
DoCmd.RunCommand acCmdRecordsGoToNew

Creates the error "This command isn't available now".

Nothing seems to work for me

 

[John Vinson]

Also, when using the following:

Me.Subform_Name.SetFocus
DoCmd.RunCommand acCmdRecordsGoToNew

Creates the error "This command isn't available now".

Is the Name property of the Subform control on your mainform in fact

Subform_Name

or do you have some more reasonable name for the subform?

Note that the Subform control - the box on the mainform which contains
the subform - and the form within that control might have different
names. By default Access uses the same name, but it depends on how you
created the controls.

John W. Vinson[MVP]

 

[Someone]

Is the Name property of the Subform control on your mainform in fact

Subform_Name

or do you have some more reasonable name for the subform?

Note that the Subform control - the box on the mainform which contains
the subform - and the form within that control might have different
names. By default Access uses the same name, but it depends on how you
created the controls.

John W. Vinson[MVP]

I gave that name for example purposes.

I just don't know what to do - I've Googled till my brains have come out!
The different ways of adding new records to a subform in code that I have
seen just don't work for me.

 

[Someone]

I've sorted it by moving it from AfterUpdate to On Change.

 

[John Vinson]

I just don't know what to do - I've Googled till my brains have come out!
The different ways of adding new records to a subform in code that I have
seen just don't work for me.

You'll probably need to Requery the subform after adding records to
its recordsource table.

John W. Vinson[MVP]