Delete Problem with Multiple Subforms

[Marcus via AccessMonster.com]

I have a form used for entering data file definitions (fFile). A file may
contain multiple records. Each record contains multiple fields. The users
wanted the ability to view all records contained within the file and see all
fields for the selected record. In order to do this I would need a continuous
form within a continuous form.

Since Access does not allow this, I created two subforms. The first (sRecord)
contains the descriptive information about the records within the file. The
second (sField) contains descriptive information about the fields within the
record. In order to ensure that the fields displayed on the sField form show
only those associated with the record selected on sRecord, the Record Source
for sField has a where clause referencing the RecordId from sRecord. I also
have an OnCurrent event on sRecord to requery sField (Forms!fFile!sField.
requery) when a different record is selected.

Everything works fine for display and for adding records and fields. I am
having a problem though when the user deletes a record from the sRecord
subform and selects another record on sRecord, the requery no longer
refreshes the data in sField. The only way to refresh the data was to close
and re-open the form. This obviously isn't an acceptable solution.

Is there a solution to this issue?

 

[SusanV]

Hi Marcus,

Instead of basing your subform on a WHERE clause, you should relate the 2
tables with referential integrity - cascade deletes. Set the main from and
subform Parent/Child link field to the common field you are using in your
where clause.
This way when you delete a record on the main table (main form) it will also
be deleted from the child table/form.

 

[Marcus via AccessMonster.com]

Susan,

Thank you for your prompt response. I had already set up the relationship
with the cascading delete as you noted. The delete is working fine. The
record from sRecord and the associated records in sField successfully deleted.
The problem appears to be with the forms. Changing focus to a different
record on sRecord no longer refreshes the data in sField once the delete has
occurred.

Hi Marcus,

Instead of basing your subform on a WHERE clause, you should relate the 2
tables with referential integrity - cascade deletes. Set the main from and
subform Parent/Child link field to the common field you are using in your
where clause.
This way when you delete a record on the main table (main form) it will also
be deleted from the child table/form.

I have a form used for entering data file definitions (fFile). A file may
contain multiple records. Each record contains multiple fields. The users

[quoted text clipped - 27 lines]

Is there a solution to this issue?

 

[SusanV]

Are the form/subform links set correctly?

Susan,

Thank you for your prompt response. I had already set up the relationship
with the cascading delete as you noted. The delete is working fine. The
record from sRecord and the associated records in sField successfully
deleted.
The problem appears to be with the forms. Changing focus to a different
record on sRecord no longer refreshes the data in sField once the delete
has
occurred.

Hi Marcus,

Instead of basing your subform on a WHERE clause, you should relate the 2
tables with referential integrity - cascade deletes. Set the main from and
subform Parent/Child link field to the common field you are using in your
where clause.
This way when you delete a record on the main table (main form) it will
also
be deleted from the child table/form.

I have a form used for entering data file definitions (fFile). A file may
contain multiple records. Each record contains multiple fields. The
users

[quoted text clipped - 27 lines]

Is there a solution to this issue?

 

[Marcus via AccessMonster.com]

Susan,

Good thought. Unfortunately, since both subforms are continuous, Access does
not allow you to set up the master/child links. I'm using the where clause
noted earlier to get around this limitation.

Mark

Are the form/subform links set correctly?

Susan,

[quoted text clipped - 22 lines]

 

[SusanV]

Um... I have many subforms setup which are continuous forms and the linking
fields to the main forms work fine.

You state "both subforms..."
Are you trying to force Subform2 based on the data in Subform1 without a
common link between both subforms and the Main form perhaps? That would
certainly break it - you can't link subform to subform, you need to use the
main form as a connector. In fact you can use a common field in the form
header of the main form, make the header visible = false, and have NOTHING
on your main form other than that control and the 2 (now) related subforms
if you like - so it looks like just the 2 subforms, with no extraneous
fields or controls.

Or perhaps I misunderstood? If so, could you be more specific about your
forms and the parent/child relationships?

SusanV

Susan,

Good thought. Unfortunately, since both subforms are continuous, Access
does
not allow you to set up the master/child links. I'm using the where clause
noted earlier to get around this limitation.

Mark

Are the form/subform links set correctly?

Susan,

[quoted text clipped - 22 lines]

Is there a solution to this issue?

 

[Marcus via AccessMonster.com]

Susan,

I appreciate all of your efforts. I have a feeling that I didn't explain the
forms well enough. Here's the setup.

Main Form (fFile) - Describes a data file
Default View = Single Form
RecordSource = SELECT * FROM tFile

Subform1 (sRecord) - Describes records within the data file. There can be
multiple records/file
Default View = Continuous
Record Source = SELECT * FROM tRecord
Link Child Fields = FileId
Link Master Fields = FileId

Subform2 (sField) - Describes the fields contained within the records shown
in sRecord. There are multiple fields/record
Default View = Continuous
RecordSource = SELECT * FROM tField WHERE RecordId = Forms!fFile!sRecord!
RecordId
Link Child Fields =
Link Master Fields =

Since you can't set up a master/child relationship between two continuous
forms the Link Child Fields and Link Master Fields parameters are not set for
Subform2. To get around that I have specified the relationship between the
two subforms with the WHERE clause in the RecordSource of Subform2. I then
have an OnCurrent event that performs a requery of Subform2 (Forms!fFile!
sField.Requery).

When a file definition is displayed on the master form, the records contained
in that file are shown in Subform1. Selecting a record on Subform1 displays
the fields contained in that record on Subform2. This works well. As does
adding new records or adding new fields to existing records.

The problem occurs when deleting a record definition from Subform1. The
delete works (including the delete of the child rows in the tField table
thanks to the cascading delete) but the refresh no longer works. Selecting
different records in Subform1 no longer displays the fields associated to it
in Subform2. In fact, the recordset in Subform2 is now grayed out. Closing
the Main Form and re-opening it corrects the display.

I can't figure out why the link breaks.

Thanks,
Mark

Um... I have many subforms setup which are continuous forms and the linking
fields to the main forms work fine.

You state "both subforms..."
Are you trying to force Subform2 based on the data in Subform1 without a
common link between both subforms and the Main form perhaps? That would
certainly break it - you can't link subform to subform, you need to use the
main form as a connector. In fact you can use a common field in the form
header of the main form, make the header visible = false, and have NOTHING
on your main form other than that control and the 2 (now) related subforms
if you like - so it looks like just the 2 subforms, with no extraneous
fields or controls.

Or perhaps I misunderstood? If so, could you be more specific about your
forms and the parent/child relationships?

SusanV

Susan,

[quoted text clipped - 12 lines]

 

[Guest]

You can link a 2nd subform. On the main form create a text box call it
linkfield

In the on current event of subform2 use this code:
Me.Parent!linkfield = Me!RecordID

Link Master Field: Linkfield
Link Child Field: RecordID
you must type this in.

It works for me, it got this from the newsgroup quite some time ago and I am
using it over and over again.

Also, when it tells you that a continuous form cannot have a subform and it
changes the continuous form back to a single form, try changing it back to
continuous and I got that to work as well.

Susan,

I appreciate all of your efforts. I have a feeling that I didn't explain the
forms well enough. Here's the setup.

Main Form (fFile) - Describes a data file
Default View = Single Form
RecordSource = SELECT * FROM tFile

Subform1 (sRecord) - Describes records within the data file. There can be
multiple records/file
Default View = Continuous
Record Source = SELECT * FROM tRecord
Link Child Fields = FileId
Link Master Fields = FileId

Subform2 (sField) - Describes the fields contained within the records shown
in sRecord. There are multiple fields/record
Default View = Continuous
RecordSource = SELECT * FROM tField WHERE RecordId = Forms!fFile!sRecord!
RecordId
Link Child Fields =
Link Master Fields =

Since you can't set up a master/child relationship between two continuous
forms the Link Child Fields and Link Master Fields parameters are not set for
Subform2. To get around that I have specified the relationship between the
two subforms with the WHERE clause in the RecordSource of Subform2. I then
have an OnCurrent event that performs a requery of Subform2 (Forms!fFile!
sField.Requery).

When a file definition is displayed on the master form, the records contained
in that file are shown in Subform1. Selecting a record on Subform1 displays
the fields contained in that record on Subform2. This works well. As does
adding new records or adding new fields to existing records.

The problem occurs when deleting a record definition from Subform1. The
delete works (including the delete of the child rows in the tField table
thanks to the cascading delete) but the refresh no longer works. Selecting
different records in Subform1 no longer displays the fields associated to it
in Subform2. In fact, the recordset in Subform2 is now grayed out. Closing
the Main Form and re-opening it corrects the display.

I can't figure out why the link breaks.

Thanks,
Mark

Um... I have many subforms setup which are continuous forms and the linking
fields to the main forms work fine.

You state "both subforms..."
Are you trying to force Subform2 based on the data in Subform1 without a
common link between both subforms and the Main form perhaps? That would
certainly break it - you can't link subform to subform, you need to use the
main form as a connector. In fact you can use a common field in the form
header of the main form, make the header visible = false, and have NOTHING
on your main form other than that control and the 2 (now) related subforms
if you like - so it looks like just the 2 subforms, with no extraneous
fields or controls.

Or perhaps I misunderstood? If so, could you be more specific about your
forms and the parent/child relationships?

SusanV

Susan,

[quoted text clipped - 12 lines]

Is there a solution to this issue?

 

[Marcus via AccessMonster.com]

Anne,

That did the trick. Looking back at the thread, I think that is what Susan
was trying to tell me as well. Maybe the good night's sleep helped me
understand. Thank you for your time and the great suggestion.

Also, thanks to Susan for her help!

Mark

You can link a 2nd subform. On the main form create a text box call it
linkfield

In the on current event of subform2 use this code:
Me.Parent!linkfield = Me!RecordID

Link Master Field: Linkfield
Link Child Field: RecordID
you must type this in.

It works for me, it got this from the newsgroup quite some time ago and I am
using it over and over again.

Also, when it tells you that a continuous form cannot have a subform and it
changes the continuous form back to a single form, try changing it back to
continuous and I got that to work as well.

Susan,

[quoted text clipped - 67 lines]

 

[SusanV]

Glad you got it working!
Thanks Anne!

;-)

SusanV

Anne,

That did the trick. Looking back at the thread, I think that is what Susan
was trying to tell me as well. Maybe the good night's sleep helped me
understand. Thank you for your time and the great suggestion.

Also, thanks to Susan for her help!

Mark

You can link a 2nd subform. On the main form create a text box call it
linkfield

In the on current event of subform2 use this code:
Me.Parent!linkfield = Me!RecordID

Link Master Field: Linkfield
Link Child Field: RecordID
you must type this in.

It works for me, it got this from the newsgroup quite some time ago and I
am
using it over and over again.

Also, when it tells you that a continuous form cannot have a subform and
it
changes the continuous form back to a single form, try changing it back to
continuous and I got that to work as well.

Susan,

[quoted text clipped - 67 lines]

Is there a solution to this issue?