Unable to delete subform records

[Syphonics via AccessMonster.com]

Hi all,

I have a main form which inside contains a sub-form A and inside this subform
A there is a subform B.
Subform A is a datasheet view while subformB is a form.
After I delete the records in subform B, the records are gone. But once I
close and open the main form again, the records still appears in the subform
B.
I couldn't delete the records in subform B using the main form. But I can do
it when I open only the subform A and delete the records of subform B.
Please do help me in this. I am stuck with this for days.

Thank you.

 

[Syphonics via AccessMonster.com]

Anyone able to help me?

Hi all,

I have a main form which inside contains a sub-form A and inside this subform
A there is a subform B.
Subform A is a datasheet view while subformB is a form.
After I delete the records in subform B, the records are gone. But once I
close and open the main form again, the records still appears in the subform
B.
I couldn't delete the records in subform B using the main form. But I can do
it when I open only the subform A and delete the records of subform B.
Please do help me in this. I am stuck with this for days.

Thank you.

 

[John W. Vinson]

Hi all,

I have a main form which inside contains a sub-form A and inside this subform
A there is a subform B.
Subform A is a datasheet view while subformB is a form.
After I delete the records in subform B, the records are gone. But once I
close and open the main form again, the records still appears in the subform
B.
I couldn't delete the records in subform B using the main form. But I can do
it when I open only the subform A and delete the records of subform B.
Please do help me in this. I am stuck with this for days.

Thank you.

I'm confused. In my experience you can't HAVE a subform within a Datasheet
subform! Are you using Subdatasheets, with the + icon at the left of the
datasheet, or what?

What are the Recordsource properties of A and B? Could you post the SQL? How
are you deleting the records?

 

[Syphonics via AccessMonster.com]

Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the
+icon, it will open the sub form B, which is a form view.

In my main form, there is a combo box which allows me to choose the customer.
Upon choosing the customer, it will filtered and shows the list of orders of
the customer in subform A.
Subform A record source is from my "Order Query".
On clicking on the +icon of subfrom A, it will open subform B and show the
Account Receivables details. Subform B record source is from my table
"Account Receivables".

I left click on the record of the subform B and did a record cut. After
closing the main form and reopening it, the record which i have just deleted
appears once again. I couldn't delete the record unless I do a manual delete
on my table "Account Receivables".

SQL
Subfrom A:
SELECT Order.*
FROM [Order];

Subform B:
SELECT
FROM [Account Receivables];

Hi all,

[quoted text clipped - 9 lines]

Thank you.

I'm confused. In my experience you can't HAVE a subform within a Datasheet
subform! Are you using Subdatasheets, with the + icon at the left of the
datasheet, or what?

What are the Recordsource properties of A and B? Could you post the SQL? How
are you deleting the records?

 

[John W. Vinson]

Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the
+icon, it will open the sub form B, which is a form view.

In my main form, there is a combo box which allows me to choose the customer.
Upon choosing the customer, it will filtered and shows the list of orders of
the customer in subform A.
Subform A record source is from my "Order Query".
On clicking on the +icon of subfrom A, it will open subform B and show the
Account Receivables details. Subform B record source is from my table
"Account Receivables".

I left click on the record of the subform B and did a record cut. After
closing the main form and reopening it, the record which i have just deleted
appears once again. I couldn't delete the record unless I do a manual delete
on my table "Account Receivables".

SQL
Subfrom A:
SELECT Order.*
FROM [Order];

Subform B:
SELECT
FROM [Account Receivables];

How are your tables related? Do you have Referential Integrity enforced on the
relationship? What about Cascade Deletes? What's the Master and Child Link
Field of B?

 

[Syphonics via AccessMonster.com]

Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the

[quoted text clipped - 21 lines]

SELECT
FROM [Account Receivables];

How are your tables related? Do you have Referential Integrity enforced on the
relationship? What about Cascade Deletes? What's the Master and Child Link
Field of B?

There is a field name AcountID in the "Account Recievables" table which is
link to the Order ID in the "Order Table" both are primary key. I have tried
with and without "Referential Integrity and Cascade Deletes" but it still
never work.
Subform A is link with master and child link using Customer ID, for filtering
of the customer.
Subform B is link with master link: OrderID of the "Order Query" and the
child link is the AccountID of the "Account Recieveable Table"

 

[John W. Vinson]

Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the

[quoted text clipped - 21 lines]

SELECT
FROM [Account Receivables];

How are your tables related? Do you have Referential Integrity enforced on the
relationship? What about Cascade Deletes? What's the Master and Child Link
Field of B?

There is a field name AcountID in the "Account Recievables" table which is
link to the Order ID in the "Order Table" both are primary key. I have tried
with and without "Referential Integrity and Cascade Deletes" but it still
never work.
Subform A is link with master and child link using Customer ID, for filtering
of the customer.
Subform B is link with master link: OrderID of the "Order Query" and the
child link is the AccountID of the "Account Recieveable Table"

I'm wondering about your data structure. Does each order have one and only one
Account Recievable record? That would imply a one to one relationship: if
that's the case, why have two tables? What's the direction of the relationship
(even one to one relationships have a parent and a child table)?

 

[Syphonics via AccessMonster.com]

Each order may have more than 1 account recievable records.
I have just realised that I am able to delete the records if I open the
Subform A with Subform B in it.
But if I open the main form with SubformA and inside of SubformA is SubformB,
I am not able to delete the record.

[quoted text clipped - 14 lines]

Subform B is link with master link: OrderID of the "Order Query" and the
child link is the AccountID of the "Account Recieveable Table"

I'm wondering about your data structure. Does each order have one and only one
Account Recievable record? That would imply a one to one relationship: if
that's the case, why have two tables? What's the direction of the relationship
(even one to one relationships have a parent and a child table)?

 

[John W. Vinson]

Each order may have more than 1 account recievable records.
I have just realised that I am able to delete the records if I open the
Subform A with Subform B in it.
But if I open the main form with SubformA and inside of SubformA is SubformB,
I am not able to delete the record.

That would appear to be a problem with A2007 and subdatasheets - neither of
which I use routinely! Consider using a true Subform rather than a datasheet.

 

[David W. Fenton]

In my experience you can't HAVE a subform within a Datasheet
subform!

Open a form with a child form and then change the view to Datasheet
view. You'll see that you get a datasheet with the child form
embedded like a subdatasheet. In this way, you can actually have a
child form with child forms, including continuous forms.

I've been harping on how useful this is for a really long time, but
nobody seems to notice that it's possible!

 

[Syphonics via AccessMonster.com]

This method of child form and change to datasheet view couldn't work as well.
It seems like the main form embedded with SubformA embedded with SubformB
couldn't work even when SubformA is not in datasheet view.

 

[David W. Fenton]

This method of child form and change to datasheet view couldn't
work as well. It seems like the main form embedded with SubformA
embedded with SubformB couldn't work even when SubformA is not in
datasheet view.

I didn't realize you had 2 levels of child form.